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 |
mase2hot
Starting Member
36 Posts |
Posted - 2012-11-11 : 18:10:12
|
Hi,I have about 4 million records in a table. I have noticed that dates are a mess, I have 2 columns that contain datetimes. Unfortunately the datatype is varchar! which doesn't help.I have dates like (yyyy-mm-dd hh:mm:ss), (dd/mm/yyyy hh:mm:ss) and (mm/dd/yyyy/ hh:mm:ss). I need to somehow clean this up, whats the best way to do it?Thanks |
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2012-11-11 : 20:08:55
|
for yyyy-mm-dd, it is fine. For the other 2 format, you will need to find a way to determine the date is dd/mm/yyyy or mm/dd/yyyyexample 12/11/2012. Is it Dec 11 or Nov 12 ? KH[spoiler]Time is always against us[/spoiler] |
|
|
mase2hot
Starting Member
36 Posts |
Posted - 2012-11-12 : 16:13:35
|
That makes sense, luckily the only concerning dates are: from September, October, November this year...What command do I need to use? |
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2012-11-12 : 16:33:13
|
when there is a mixture of (dd/mm/yyyy and mm/dd/yyyy) formats in a varchar column there is no sql command that will tell you if 12/11/2012 Is Dec 11 or Nov 12.The only thing I can think of is if something like if application1 added dd/mm/yyyy dates and application2 added mm/dd/yyyy dates and you could tell by some other attributes which application added the date then you would know which date the value represents.or perhaps all dates added prior so some point in time were mm/dd/yyyy then after that the format switched to dd/mm/yyyy AND there was a some type of timestamp column then you could tell.Otherwise, you're hosed.Be One with the OptimizerTG |
|
|
|
|
|