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.

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Data cleansing dates MSSQL 2008 R2

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/yyyy

example 12/11/2012. Is it Dec 11 or Nov 12 ?


KH
[spoiler]Time is always against us[/spoiler]

Go to Top of Page

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?
Go to Top of Page

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 Optimizer
TG
Go to Top of Page
   

- Advertisement -