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 |
|
rbohaty
Yak Posting Veteran
71 Posts |
 Posted - 2006-12-05 : 15:47:57
|
| I have a table with a date column and want to verify that all data in the column is the correct format mm/dd/yyyy. Is there a script I can run on the table /columns to verify all data conforms to the format? |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2006-12-05 : 15:52:39
|
| You cannot do this!It does not matter which format the date is presented in QA, EM or SSMS! The date is just a representation of a fraction number where the integer part is the number of days passed since 1 January 1900, and the fraction is the number of 1/300 seconds passed since midnight that day.Leave it to the front end to do the date formatting.Peter LarssonHelsingborg, Sweden |
 |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2006-12-05 : 15:54:00
|
| But if the system you are handling is badly planned by storing the dates as VARCHAR (datetime is always to prefer), use thisset dateformat mdyselect * from yourtablenamehere where isdate(yourcolumnnamehere) = 0Peter LarssonHelsingborg, Sweden |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2006-12-06 : 10:38:20
|
| 1 Use always DATETIME datatype2 It is front end where you should show that formatMadhivananFailing to plan is Planning to fail |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2006-12-06 : 10:42:29
|
quote:
Originally posted by Peso
But if the system you are handling is badly planned by storing the dates as VARCHAR (datetime is always to prefer), use thisset dateformat mdyselect * from yourtablenamehere where isdate(yourcolumnnamehere) = 0Peter LarssonHelsingborg, Sweden
ISDATE is like ISNUMERIC not reliableSelect ISDATE('2000'),ISDATE(2000)MadhivananFailing to plan is Planning to fail |
|
|
|
|
|
|
check data format validity