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 |
|
Sun Foster
Aged Yak Warrior
515 Posts |
 Posted - 2006-06-08 : 10:42:06
|
| I have a table in which there is column "OrderDate" and its type is varchar. The data stored it like "07/25/2005", "12/01/2005"...I try to use "between" statement to control OrderDate but got wrong data. How to do it? Do I need to convert varchar to date type? |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2006-06-08 : 10:47:54
|
| Dont use varchar datatype to store dates. Use Proper Datetime datatype to avoid many problemsYou need to convert it to datetime before checking...where cast(datecol as datetime) between date1 and date2MadhivananFailing to plan is Planning to fail |
 |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2006-06-08 : 10:48:31
|
that's the problem of not using proper data type. You will not have this problem if you use datetype data type..Yes you have to convert to datetime data type.
KH |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-06-08 : 14:28:09
|
| "You need to convert it to datetime before checking"and you'll need to take into account the locale of the server, or specify a specific date format for the conversion.Kristen |
|
|
|
PSamsig
Constraint Violating Yak Guru
384 Posts |
Posted - 2006-06-09 : 12:59:54
|
| ... and the possibility that the convertion fails because of an invalide date. Never never NEVER store dates in varchars. (Unless of course you need lazy evaluation) And if you really do (and you really don't) then do it in a universal format like 'YYYYMMDD', them you can actually compare and index proberly on.-- This one's tricky. You have to use calculus and imaginary numbers for this. You know, eleventeen, thirty-twelve and all those. |
|
|
|
|
|
|
Use between in date column