Select the first two character in SQL Server CE

Question

[ANSWER]

Thanks everyone! The code to solved the problem below is:

SELECT ID
FROM Schedule
WHERE (SUBSTRING(Time, 1, 2) = DATEPART(hh, GETDATE()))

---

How to select the first two character in SQL Server CE?

For example:

+-----+-----------+------------+
+ Day +  Time     +  ID        +
+-----+-----------+------------+
+ 1   +  08:00:00 +  A1        +
+ 1   +  09:00:00 +  A2        +
+ 1   +  09:30:00 +  A3        +
+ 1   +  10:15:00 +  A4        + 
+-----+-----------+------------+

Time column is a nvarchar type.

Then, I want to select the ID only with the time based on the real-hour, like only select the 08 part from 08:00:00. How can I do that?

Answer 1

SUBSTRING is available in CE - http://msdn.microsoft.com/en-us/library/aa275646(SQL.80).aspx

SUBSTRING ( expression , start , length )

Answer 2

The SQL Standard method is given by the syntax diagram:

<character substring function> ::=
         SUBSTRING <left paren> <character value expression> FROM <start position>
         [ FOR <string length> ] [ USING <char length units> ] <right paren> 

Hence, in this example:

SELECT SUBSTRING(Time FROM 1 FOR 2) FROM AnonymousTable;

Some DBMS (Oracle, and those emulating Oracle) provide a SUBSTR() function instead:

SELECT SUBSTR(Time, 1, 2) FROM AnonymousTable;

The 1 is the starting offset, counting from 1, and the 2 is the length (not the finishing offset, though they are the same for this example, and any other where the start is 1).

There may be other methods for your particular DBMS. For example, with Informix you could write:

SELECT Time[1,2] FROM AnonymousTable;

This time though, the subscripts are start and stop positions.

Informix supports all three of these. I don't know of any other DBMS that uses the subscript notation.