Building DATETIME from two strings

Question

Building DATETIME from two strings

Johnny B

SSCrazy

Points: 2965
More actions
February 8, 2016 at 9:59 am

#308298

Hi, I've worked on this for an hour or so... I'm not that good I think.
Date Time_on
20160205 00:00:0005:17:00
both are strings. I need to combine them and make a DATETIME.
CAST(LEFT([Date],8)+' '+Time_on+'.000' AS DATETIME)
SQL 2012 Standard VPS Windows 2012 Server Standard

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Luis Cazares SSC Guru Points: 183706 More actions · Answer 1

Johnny B (2/8/2016)
Hi, I've worked on this for an hour or so... I'm not that good I think.
Date Time_on
20160205 00:00:0005:17:00
both are strings. I need to combine them and make a DATETIME.
CAST(LEFT([Date],8)+' '+Time_on+'.000' AS DATETIME)

You just need to convert both to datetime and add them. Is an advantage of datetime and smalldatetime.

CREATE TABLE #TestDates(

Date varchar( 25),

Time_on char( 8)

);

INSERT INTO #TestDates

VALUES( '20160205 00:00:00', '05:17:00');

SELECT CONVERT( datetime, Date) + CONVERT( datetime, Time_on)

FROM #TestDates;

GO

DROP TABLE #TestDates;

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

ScottPletcher SSC Guru Points: 101098 More actions · Answer 2

Interesting. The code below should work fine. What error are you getting? Are you sure that time is always formatted correctly?

SELECT CAST(LEFT([Date],8)+' '+Time_on AS datetime)

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Johnny B SSCrazy Points: 2965 More actions · Answer 3

Thanks everyone, these solutions are similar to some of what I tried. It ends up the source system has a 36 hour day in some cases.... I'm not sure why but I've found existing logic which handles it. Thanks everyone! 36 hour day... really?

SQL 2012 Standard VPS Windows 2012 Server Standard

Luis Cazares SSC Guru Points: 183706 More actions · Answer 4

Johnny B (2/8/2016)
36 hour day... really?

Sound like a normal day in my previous job. 😀

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2