asked on

Date Conversion Error

Hi

When i Execute the Below Query its id Displaying the 2012 year dates also

SELECT CONVERT(VARCHAR(8), s.CREATED_TIME,3) as day from Table1 s
WHERE CONVERT(VARCHAR(8), s.created_time, 3)
between CONVERT(VARCHAR(8), '13/09/11', 3) and CONVERT(VARCHAR(8), '20/09/11', 3) order by created_time

Created_time datatype id datetime
in the Database its is storing as the these format:2011-09-13 12:11:00.000
2011-09-14 14:22:50.950
2011-09-14 14:36:14.267

Out Put
13/09/11
13/09/11
14/09/11
14/09/11
14/09/11
14/09/11
14/09/11
14/09/11
16/09/11
16/09/11
16/09/11
16/09/11
16/09/11
16/09/11
16/09/11
16/09/11
16/09/11
20/09/11
20/09/11
20/09/11
20/09/11
20/09/11
20/09/11
20/09/11
17/01/12
17/01/12
17/01/12
17/01/12
17/01/12
17/01/12
17/01/12
17/01/12
17/01/12
17/01/12
17/01/12
17/01/12
17/01/12
17/01/12
17/01/12
17/01/12
17/01/12
17/01/12
17/01/12
17/01/12
17/01/12
17/01/12
17/01/12
17/01/12
17/01/12
17/01/12
17/01/12
17/01/12
18/01/12
18/01/12
18/01/12
18/01/12
18/01/12
18/01/12
18/01/12
18/01/12
18/01/12
18/01/12
18/01/12
18/01/12
19/01/12
19/01/12
19/01/12
19/01/12
19/01/12
19/01/12
19/01/12
19/01/12
17/02/12
17/02/12
17/02/12
17/02/12
17/02/12
17/02/12
17/02/12
17/02/12
17/02/12
17/02/12
17/02/12
17/02/12
17/02/12
17/02/12
17/02/12
17/02/12
17/02/12
17/02/12
17/02/12
17/02/12
17/02/12
17/02/12
17/02/12
17/02/12
17/02/12
17/02/12
17/02/12
17/02/12
17/02/12
17/02/12
17/02/12
17/02/12
17/02/12
17/02/12
17/02/12
17/02/12
17/02/12
17/02/12
17/02/12
17/02/12

Please suggest !!

Thanks
Raj

Lee

You're converting to a string and comparing.

Try this:

SELECT CONVERT(VARCHAR(8), s.CREATED_TIME,3) as day from Table1 s
WHERE created_time
between cast(CONVERT(VARCHAR(8), '13/09/11', 3) as datetime) and cast(CONVERT(VARCHAR(8), '20/09/11', 3) as datetime) order by created_time

I'm assuming created_time is a datetime field.

ASKER CERTIFIED SOLUTION

Lee

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Cluskitt

To avoid incompatibilities, I always use 'yyyyMMdd' format. You can try this:
WHERE CONVERT(VARCHAR(8), s.created_time, 112)
between '20110913' and '20110920'

You don't even need to convert your field to 112. SQL does an implicit conversion.

BuggyCoder

SELECT  CONVERT(VARCHAR(8), s.CREATED_TIME,3) as day from  Table1 s
WHERE  created_time between CONVERT(datetime, '13/09/11', 103) and  CONVERT(datetime, '20/09/11', 103) order by created_time

Open in new window

Lee

SQL does do implicit conversions, but explicit conversions are better in my opinion as at least you control the conversion and you don't rely on something to do it for you. We all know how much a nightmare date handling is because of the differing formats regardless of how good SQL's date handling system is.

Cluskitt

That's why I use 112. DateField='20120301' is the same thing as DateField=CONVERT(datetime,'20120301'). And you have the exact same control over it without the explicit conversion and with it.
As a general rule, you should always use explicit conversions. But in this particular case, there is no difference, unless your date field isn't a date either.
And the yyyyMMdd format is the only one I found that is, so far, fully universal.

nrajasekhar7

ASKER

Thanks You very Much ,It works