CODEWITHSUNDEEP
sqlsql-server-ce
mrjimoy_05
mrjimoy_05

Reputation: 3578

Select the first two character in SQL Server CE

[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?

Upvotes: 5

Views: 18744

Answers (3)

Jonathan Leffler
Jonathan Leffler

Reputation: 754760

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.

Upvotes: 2

Chris Gessler
Chris Gessler

Reputation: 23123

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

SUBSTRING ( expression , start , length )

Upvotes: 12

Daniel A. White
Daniel A. White

Reputation: 191037

Do you mean like this?

SELECT LEFT(Time, 2) From Table

Upvotes: 1

Related Questions