Please start any new threads on our new site at We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Save Password
Forgot your Password?

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

Starting Member

36 Posts

Posted - 11/11/2012 :  18:10:12  Show Profile  Reply with Quote

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?


In (Som, Ni, Yak)

17689 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 ?

Time is always against us

Go to Top of Page

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

Flowing Fount of Yak Knowledge

6065 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
Go to Top of Page
  Previous Topic Topic Next 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.03 seconds. Powered By: Snitz Forums 2000