Convert String Type British Datetime Data (DD/MM/YYYY hh:mm:ss ) in Varchar/NVarchar to Standard Datetime with T-SQL




create table test (strdt varchar(255))
--British datetime in string
insert into test values 
('13/12/2013 13:33:00 +00:00')
,('01/01/2013 13:33:00 +00:00')
,('28/02/2013 13:33:00 +00:00')
,('31/10/2013 13:33:00 +00:00')
Select 
--string manipulation and cast to datetime
Cast(substring(strdt,7,4)+substring(strdt,4,2)+substring(strdt,1,2) +' '+ substring(strdt,12,8) as datetime),

--Convert string to British datetime first and then convert the British datetime to standard
  convert(datetime, convert(datetime, Left(strdt,20), 103),112)
   
FROM test


--clean up
drop table test

 


 
Advertisements


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s