Date - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Date

Author

Topic

dr223
Constraint Violating Yak Guru

444 Posts

Posted - 2011-04-14 : 08:20:31

Hi,

I am working with SQL 2005, and have the following query;

SELECT * FROM dbo.TherapyData
where eventdate='08/12/2009'

Works fine and brings all the date on 08/12/2009.

However, then I change the query

SELECT * FROM dbo.TherapyData
where eventdate>='08/12/2009'

The result is invalid as it brings eventdata in 2005/2006/2007/2008.

What am I doing wrong?

Thanks in advance .. Note: the date field has been saved as nvarchar, mainly because some of the eventdates are empty..

Many thanks

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-04-14 : 08:25:55

It's because of the varchar - 18/12/2009 is less than 19/01/1900
try
SELECT * FROM dbo.TherapyData
where convert(datetime,eventdate,103) >= '20091208'
(or you might want 101 if you are using m/dd/yyyy)
you really need to change the datatype though as this will not use an index usefully and will be slow.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

dr223
Constraint Violating Yak Guru

444 Posts

Posted - 2011-04-14 : 09:15:25

ok one last thing.. how can I order by eventdate when I try ;

SELECT * FROM dbo.TherapyData
where convert(datetime,eventdate,103) >= '20091208'
Order By eventdate

I get

01/01/2010 as the fisrt record then
01/02/2010

However, in reality th efirst record should be
08/12/2009
...

thanks

jeffw8713
Aged Yak Warrior

819 Posts

Posted - 2011-04-14 : 15:25:55

Use convert(datetime, eventdate, 103) in the order by clause.

A better option would be to use the above to create a computed column, persist it and add an index. Then, you could use the computed column directly instead of using the function in the query. This would allow SQL Server to use that index (potentially). Depends upon the actual query, number of rows, etc...

Jeff

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources