Reputation: 919

Parse date in SQLServer

I am trying converted from VARCHAR(50) to DATE; only date; but it did not give me any results however this is the statement i used:

SELECT COUNT(F0)
FROM [OriginalData]
WHERE F1 between Convert(VARCHAR(11),'25/12/1988',103) and 
          convert(VARCHAR(11),'16/01/2013',103) AND F0 = 120002

The only way it shown results is when the day string is 01; e.g:

01/01/1988 and 01/01/2013

so please any advice how to converted from from string to date and compare it?

Upvotes: 0

Answers (5)

NG.

Reputation: 6073

You can use the following query :

declare @t varchar(100) = '26/12/1988'

select case
        when convert(date,@t,103) between Convert(date,'25/12/1988',103) and convert(date,'16/01/2013',103) 
                then @t
        else
            'date is not in the specified range'    
        end

Upvotes: 0

gbn

Reputation: 432521

Assuming F1 is varchar(50), then you are doing a string comparison.

Use proper ISO dates...

...
WHERE CONVERT(date, F1, 112) -- change this to match F1 format
         BETWEEN CONVERT(date,'19881225',112) and 
                 CONVERT(date,'20130116',112)
       AND F0 = 120002

Note that this isn't efficient from an indexing perspective because of the CONVERT required on F1.

Upvotes: 2

Pranay Rana

Reputation: 176946

You need to convert it like this

SELECT convert(datetime, '23/07/2009', 103)

that means query will be

SELECT COUNT(F0)
FROM [OriginalData]
WHERE F1 between Convert(datetime,'25/12/1988',103) and 
          convert(datetime,'16/01/2013',103) AND F0 = 120002

instead of VARCHAR(11) use DateTime data type while converting

Note : Assuming F1 is type of datetime

if F1 datatype of varchar than

SELECT COUNT(F0)
FROM [OriginalData]
WHERE Convert(datetime,F1,103) between Convert(datetime,'25/12/1988',103) and 
          convert(datetime,'16/01/2013',103) AND F0 = 120002

convert F1 also like in above query

Upvotes: 1