August 7, 2006 at 11:55 am
Hi,
I'd like to query the records by a date string, ie: '8/1/2006'. In my database i have a column created_date which is a datetime column.
SELECT * FROM my_table
WHERE created_date BETWEEN DATEADD(day, 0, '8/1/2006') AND DATEADD(day, 1, '8/1/2006')
The above query works fine but it seems to me a little 'overkill' for a simple query. Is there a better query to return the same results?
Thanks,
Tuan
August 7, 2006 at 12:13 pm
SELECT * FROM my_table
WHERE created_date BETWEEN cast('8/1/2006 12:00AM' as datetime) AND cast('8/1/2006 11:59:59PM' as datetime)
August 7, 2006 at 3:48 pm
Thanks Nagabhushanam!
August 8, 2006 at 9:46 am
Depending on the size of the table yyou are querying the cast may begin to perform poorly... Another Alternative that you'll actually be able to pass a datetime to as well would be...
DATEADD(mm, DATEDIFF(mm,0,getdate()), 0)
You'd need to replace the Getdate() command with whatever date you're passing in. It looks complicated, but it's very fast and scales well. You can read more about the method here... http://www.databasejournal.com/features/mssql/article.php/3076421
-Luke.
August 8, 2006 at 12:15 pm
Hi Luke -
THannks for your response. I tried to apply your suggestion in my query statement but I got syntax error
Original Query - Works fine
SELECT * FROM my_table
WHERE created_date BETWEEN BETWEEN DATEADD(day, 0, '08/01/2006') AND DATEADD(day, 1, '08/01/2006')
New Query - Syntax error
SELECT * FROM my_table
WHERE created_date BETWEEN DATEADD(day, DATEDIFF(day, 0, '08/01/2006'), 1))
THanks,
Tuan
August 8, 2006 at 1:10 pm
You need to use it once for each date...
WHERE created_date BETWEEN DATEADD(mm, DATEDIFF(mm,0,'08/01/2006'), 0) -- Eq to {d '2006-08-01 00:00:00'} or 08/01/2006 00:00:00
AND DATEADD(mm, DATEDIFF(mm,0,'08/02/2006'), 0) --Eq to {d '2006-08-02 00:00:00'} or 08/02/2006 00:00:00
It drops any time portion from a date time variable down to 00:00:00. That way you can use it to IMHO write your queries in a more logical syntax. It also allows you to use a datetime variable for a stored procedure and not be concerned about passing a time portion, or if one does get passed it will drop it back to midnight.
I find it faster and easier to apply than hard coding the time portion as Nagabhushanam did. I like the fact that it not only solves the current problem but can be used anytime you need to drop the time portion of a datetime for evaluation, display etc...
August 8, 2006 at 1:33 pm
Hi Luke -
That was very helpful. Thanks again for your help!
Tuan
August 8, 2006 at 10:55 pm
Tuan,
Looking just at what your original post requests, the DATEADD method being discussed for most of this thread will work for you...but...
Keep in mind that the BETWEEN function is INCLUSIVE. This means that using the DATEADD function to add a day to your variable, will cause you to select all records between and including 8/1/06 00:00:00 - 8/2/06 00:00:00.
Nagabhushanam's solution is the only one that is guaranteed to return data from ONLY 8/1/06.
This may be important to you, or it may not. If it is, use the DATEADD function in the later posts WITHOUT using the BETWEEN. Use '>=' and '<' instead.
Good luck!
John
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply