Cast a NCHAR14 to a DateTime

[eo]

Hi,

An application's SQL table captures data time in the format (example) '20070110100907' which means 10 January 2007 10:09:07
Is there a Cast or Convert syntax I can use to convert this into a yyyy-mm-dd hh:mm:ss date time format in a view over the table?

EO
Hertfordshire, England

 

[GeertVerhoeven]

Hi,

You can use the following function:

create function dbo.ConvertToDatetime (@inputString nchar(14))
returns datetime
as
begin
declare @hourString varchar(8)
set @hourString = substring(@inputString, 9, 2) + ':' + substring(@inputString, 11, 2) + ':' + right(@inputString, 2)

return convert(datetime, left(@inputString, 8), 112) + convert(datetime, @hourString, 108)
end
go

select dbo.ConvertToDatetime (@datetimestring)

Greetz,

Geert

Geert Verhoeven
Consultant @ Ausy Belgium

My Personal Blog