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: 183686 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: 100262 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) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!

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: 183686 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