another converting date/time string into datetime 1

[baboudi]

Hi all,

I have been trying without success to convert a varchar containing date and time(e.g 200702220255320000) into the datetime format 21 with the 'Syntax error converting datetime from character string.'

declare @dt char(23)
set @dt = '20070222025532000'
Select Convert(DateTime, @dt)

the only way could get it to work is when I manually used the format below:

declare @dt char(23)
set @dt = '20070222 02:55:32.000'
Select Convert(DateTime, @dt)

Do we actually have a way to do the conversion using the original format?

Many thanks
baboudi

 

[bborissov]

declare @dt char(23)
declare @realDT varchar(200)
set @dt = '200702220255320000'
SET @realDT = LEFT(@dt,8)+' '+
              SUBSTRING(@dt,9,2)+':'+
              SUBSTRING(@dt,11,2)+':'+
              SUBSTRING(@dt,13,2)+'.'+
              RIGHT(RTRIM(@dt),3)
Select Convert(DateTime, @realDT)

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
Microsoft MVP VFP

 

[baboudi]

thanks a lot, does the job.
Baboudi