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')
--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

Select   CONVERT(datetime2(3),strdt, 103) AS DateValue
,try_convert(datetime2(3),strdt,103 )
,Try_PARSE(strdt as datetime2(3) using 'en-GB')

FROM test

--clean up
drop table test



Leave a Reply

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

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

Google+ photo

You are commenting using your Google+ 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 )


Connecting to %s