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.
| Author |
Topic |
|
adya
Starting Member
31 Posts |
 Posted - 2008-12-31 : 11:57:06
|
| I have a query which gets the values from multiple table within a given interval of dates.The query uses: Convert(varchar, CreationDate, 101) between Convert(datetime,@FromDate, 101) And Convert(datetime, @ToDate, 101) in Where Condition to check for the date values. This works fine if the year is same, but fails when the year changes from 2008 to 2009.How else can i check if the date is between 2 specified values.Thanx in advance! |
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2008-12-31 : 12:20:30
|
When you do that conversion, you are converting them to strings in format MM/DD/YYYY (12/31/2008), so the compare is a string compare. Since the year is last, this makes the string compare invalid.You can just compare datetime columns directly, so there is ordinarily no reason to do a conversion to a string. Also, converting to a string prevents your query from using any index in the CreationDate column. You can just do it like this:where -- Start date is midnight on first day you want to include (YYYY-MM-DD 00:00:00.000) CreationDate >= StartDate and -- End date is midnight on the first day you do NOT want to include (YYYY-MM-DD 00:00:00.000) CreationDate < EndDate You can look at the link below for much more information on dealing with dates in SQL Server.Date/Time Info and Script Linkshttp://www.sqlteam.com/forums/topic.asp?TOPIC_ID=64762CODO ERGO SUM |
 |
|
|
adya
Starting Member
31 Posts |
Posted - 2008-12-31 : 12:53:37
|
| Thanx Michael!!The change seems to make it work! |
|
|
|
|
|
|
How to compare dates in SQL