I have a table which has column of datatype varchar.it 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.
e.g.
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.
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
select
cast(
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
end
as datetime)
from table
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?
Dates in multiple format in one column
Posted - 02/13/2013 : 00:29:22
