SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Data cleansing dates MSSQL 2008 R2
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

mase2hot
Starting Member

36 Posts

Posted - 11/11/2012 :  18:10:12  Show Profile  Reply with Quote
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
17586 Posts

Posted - 11/11/2012 :  20:08:55  Show Profile  Reply with Quote
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

Go to Top of Page

mase2hot
Starting Member

36 Posts

Posted - 11/12/2012 :  16:13:35  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
6059 Posts

Posted - 11/12/2012 :  16:33:13  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000