| Author |
 Topic  |
|
|
mase2hot
Starting Member
36 Posts |
 Posted - 11/11/2012 : 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)
Singapore
16746 Posts |
Posted - 11/11/2012 : 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/yyyy
example 12/11/2012. Is it Dec 11 or Nov 12 ?
KH
Time is always against us
|
 |
|
|
mase2hot
Starting Member
36 Posts |
Posted - 11/12/2012 : 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
Flowing Fount of Yak Knowledge
USA
5469 Posts |
Posted - 11/12/2012 : 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 Optimizer
TG |
|
|
| |
Topic |
|
Data cleansing dates MSSQL 2008 R2
