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 |
|
esthera
Master Smack Fu Yak Hacker
1410 Posts |
 Posted - 2004-10-20 : 14:40:09
|
| I have a sql statementSelect * from users WHERE mydate='10/20/2004'The mydate field is a datetime field. There is a record put in on 10/20/04. Is there a specific format I need to use? |
|
|
jmangione
Starting Member
7 Posts |
Posted - 2004-10-20 : 14:55:22
|
quote:
Originally posted by esthera
I have a sql statementSelect * from users WHERE mydate='10/20/2004'The mydate field is a datetime field. There is a record put in on 10/20/04. Is there a specific format I need to use?
SQL has had some known problems in the past with implicit datetime conversion from strings so I would reccomend you use 'YYYY-MM-DD' as your format (e.g. '2004-10-20'). If you need the time, the proper way to denote datetime is YYYY-MM-DD hh:mm:ss.mil (e.g. '2004-10-20 00:00:00.000'). Hope this helps! |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-10-20 : 15:28:03
|
| You just need to use CONVERT with a style.SELECT * FROM users WHERE CONVERT(varchar(10), mydate, 101) = '10/20/2004'Tara |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2004-10-20 : 15:44:30
|
quote:
Originally posted by tduggan
You just need to use CONVERT with a style.SELECT * FROM users WHERE CONVERT(varchar(10), mydate, 101) = '10/20/2004'Tara
 Be careful on this! You want it the other way around :SELECT * FROM users WHERE mydate = CONVERT(datetime, '10/20/2004', 101) You forfeit the use of index seeks with the WHERE clause you posted in your example, since every value must be retrieved and converted to varchar() before the comparison can be done. Perhaps more importantly, operators such as <, > and BETWEEN will not work at all if date values are converted to varchar() in that format.I personally like YYYY-MM-DD, but also like the explicit conversion as well.- Jeff |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-10-20 : 15:49:27
|
| Just illustrating the use of convert with a style...but,SELECT * FROM users WHERE mydate = CONVERT(datetime, '10/20/2004', 101) How is that query going to work when mydate has time values in it?Tara |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2004-10-20 : 16:00:00
|
| you're right, but handling time values in this case is a different question.Typically you use a range:SELECT * FROM users WHERE mydate >= CONVERT(datetime, '10/20/2004', 101) AND myDate < CONVERT(datetime, '10/21/2004', 101)That way, again, you ensure you can use indexes.- Jeff |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-10-20 : 16:01:21
|
| Right. Just wanted to make sure that I understood the question.Tara |
|
|
|
|
|
|
|
|
select where date=