Can't search through Date
-
im using this query select date from table where date='5/7/2007' it returns '0' rows, but when I run this follwoing query it returns 2 rows select date from table where date='5/7/2007 1:30:00 PM' i don't want to search with time how can i do that? i even try this select date from table where date='5/7/2007%' but it gives error as it cant convert smalldatetime to string any solution.
-
im using this query select date from table where date='5/7/2007' it returns '0' rows, but when I run this follwoing query it returns 2 rows select date from table where date='5/7/2007 1:30:00 PM' i don't want to search with time how can i do that? i even try this select date from table where date='5/7/2007%' but it gives error as it cant convert smalldatetime to string any solution.
-
im using this query select date from table where date='5/7/2007' it returns '0' rows, but when I run this follwoing query it returns 2 rows select date from table where date='5/7/2007 1:30:00 PM' i don't want to search with time how can i do that? i even try this select date from table where date='5/7/2007%' but it gives error as it cant convert smalldatetime to string any solution.
Everyone seems to be doing this today! Firstly - use ISO 8601 format. This format -
yyyyMMdd- is not subject to misinterpretation by different locale settings. Secondly, dates have a time part. If you want any time on 7 May 2007, use:WHERE date >= '20070507' AND date < '20070508'
If you don't specify a time part for a date literal, the time part is set to 00:00:00 (i.e. 12am).
Stability. What an interesting concept. -- Chris Maunder
-
Everyone seems to be doing this today! Firstly - use ISO 8601 format. This format -
yyyyMMdd- is not subject to misinterpretation by different locale settings. Secondly, dates have a time part. If you want any time on 7 May 2007, use:WHERE date >= '20070507' AND date < '20070508'
If you don't specify a time part for a date literal, the time part is set to 00:00:00 (i.e. 12am).
Stability. What an interesting concept. -- Chris Maunder
-
Thanks Mike, I'll be much oblige if you can tell me how to set that format? n where should set this ISO format either in OS or MSSQL Management Studio? Thanks Again
gamzun wrote:
n where should set this ISO format either in OS or MSSQL Management Studio?
YOU must employ the format in YOUR application / queries. This is not an OS or Server task. It is up to the application to format user entered dates properly before submitting them to the server in queries....
-
im using this query select date from table where date='5/7/2007' it returns '0' rows, but when I run this follwoing query it returns 2 rows select date from table where date='5/7/2007 1:30:00 PM' i don't want to search with time how can i do that? i even try this select date from table where date='5/7/2007%' but it gives error as it cant convert smalldatetime to string any solution.