CODEWITHSUNDEEP
sql-serverdate
Dodgeball
Dodgeball

Reputation: 125

String of date time convert to date time in SQL Server

I have this string which is a combination of date and time but in string format meaning that it has no space. I parsed it from a very long string but I now have or need to convert it to a standard date and time.

This is my string for date and time:

141007024755

This is how I parsed it from a very long string of data

[date&time] = SUBSTRING(@ProductCode, 27, 12)

This is the format I'm expecting but can't do it.

2014-10-07 02:47:55.000

Can anyone give me a hint on how to do this? An advice perhaps.

Thanks.

Upvotes: 0

Views: 57

Answers (2)

VBlades
VBlades

Reputation: 2251

Assuming the dates are formatted exactly as your sample string, you can just keep chopping up the string and appending it back together and cast the result to date. Simple function like this may help:

CREATE FUNCTION dbo.udf_ReturnDateFromString(@DateString AS VARCHAR(14))
RETURNS DATETIME
AS
BEGIN
    SET         @DateString = '20' + @DateString

    RETURN      CAST(LEFT(@DateString, 4) + '-' +
                     SUBSTRING(@DateString, 5, 2) + '-' +
                     SUBSTRING(@DateString, 7, 2) + ' ' +
                     SUBSTRING(@DateString, 9, 2) + ':' +
                     SUBSTRING(@DateString, 11, 2) + ':' +
                     SUBSTRING(@DateString, 13, 2) AS DATETIME)
END;
GO

SELECT  dbo.udf_ReturnDateFromString('141007024755');

The result is:

2014-10-07 02:47:55.000

You get the idea. This was done on SS 2008 R2.

Upvotes: 1

jpw
jpw

Reputation: 44911

If the string is always 12 characters long you could try this:

select cast(stuff(stuff(stuff('141007024755', 7,0,' '), 10,0,':'), 13,0,':') as datetime)

Basically it uses the stuff function to insert a space between the date and time parts and colons between the different time parts producing a string like141007 02:47:55that can be converted todatetimeusingcast.

Upvotes: 1

Related Questions