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
 General SQL Server Forums
 New to SQL Server Programming
 Dates in multiple format in one column
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Posting Yak Master

102 Posts

Posted - 02/13/2013 :  00:29:22  Show Profile  Reply with Quote
Hi All,

I have a table which has column of datatype contains dates in different format as below.

03/08/02012 PM 04:42:11
11/23/02012 PM 04:42:12
1/13/2012 11:25:57 AM
1/1/2011 11:25:57 AM
21/1/2011 11:25:57 AM
31/11/2012 11:25:57 AM

How can it be converted in one format using T-SQL ?

Any suggestions would be highly appreciated.

I tried it with Left function but it is for just only one format.
column has different date formats in varchar.


declare @table1 table
dtdate varchar(100)

insert @table1
select '2/4/2012' union all
select '24/11/2012'

select dtdate,case when dtdate like '_/_/%'
then '0'+left(dtdate,1)+'/'+'0'+right(left(dtdate,3),1)+right(dtdate,5)
else dtdate
end as newdate
from @table1

can it be converted in one date format ?

03/08/02012 PM 04:42:11
11/23/02012 PM 04:42:12 here year 02012 are not mistakes but are values in column.

Edited by - Vishal_sql on 02/13/2013 00:31:56

In (Som, Ni, Yak)

17689 Posts

Posted - 02/13/2013 :  02:03:50  Show Profile  Reply with Quote
How can it be converted in one format using T-SQL ?

You can use CAST() or CONVERT() to do it. And you should convert it to date or datetime data type.
However you will need to decide 01/02/2013 is Jan 2 or Feb 1 and handle it accordingly in your conversion logic

Time is always against us

Go to Top of Page

Premature Yak Congratulator

22864 Posts

Posted - 02/13/2013 :  04:18:07  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote

case when date_col like '%PM%' then replace(date_col,'PM','')+'PM' 
     when date_col like '%AM%' then replace(date_col,'AM','')+'AM' 
else date_col
as datetime)
from table


Failing to plan is Planning to fail
Go to Top of Page

Very Important crosS Applying yaK Herder

52326 Posts

Posted - 02/13/2013 :  04:35:03  Show Profile  Reply with Quote
this is the reason why we always say Use proper datatype for your columns


SQL Server MVP

Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3873 Posts

Posted - 02/13/2013 :  09:30:32  Show Profile  Reply with Quote
Following up on what khtan pointed out about Jan 2 vs Feb 2nd, I would think that this is an impossible situation. Assume that the dates have a uniform distribution over the day of the year. That means about 40 percent of the dates are ambiguous. Everything up to and including the 12th day of each month (with the exception of those days that happen to be have the same date as the month number such as 5/5/2012, 6/6/2012 etc.) are ambigous.

So unless you have some other column that will help you decipher whether a given date is Jan 2 or Feb 1, not only can you resolve this programmatically, even manual intervention wouldn't help.

Is there any possibility that you can get the data from the source in a deterministic format?
Go to Top of Page

Posting Yak Master

102 Posts

Posted - 02/13/2013 :  09:44:28  Show Profile  Reply with Quote
Hi All,
Thanks for the reply.

I beleive by replacing '/' in the column the data could be converted in int and then compared with Left(intdate,4) > 1231 or < 3112.

and then again converted back to date format
but its giving arithmetic overflow error.

Seem the date is in many formats in column is cumbersome to solve problem.
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.02 seconds. Powered By: Snitz Forums 2000