CODEWITHSUNDEEP
sql-server-2008
Mally
Mally

Reputation: 95

Convert or cast Datetime2 and varchar into datetime

I have a date stored as datetime2(7) and a time stored as varchar(5).

e.g.

Date = 2016-11-30 00:00:00.000000 (datetime2)

Time = 09:00 (varchar)

Output should be 2016-11-30 09:00:00.000000 (datetime).

How do I convert or cast these as a datetime. I have tried several ways but have been unsuccessful.

Thank you in advance for your help.

Upvotes: 0

Views: 1856

Answers (2)

Gottfried Lesigang
Gottfried Lesigang

Reputation: 67291

Maybe simple as this?

DECLARE @d DATETIME2(7)='2016-11-30 00:00:00.000000'
DECLARE @t VARCHAR(5)='09:00';

SELECT CAST(CAST(@d AS DATETIME) + CAST(@t+':00' AS DATETIME) AS datetime2(7))

Your time needs just :00 to it, than you can cast this to DATETIME. Two values of type DATETIMEcan be added.

The whole expression can be re-converted to DATETIME2.

Upvotes: 1

Hackerman
Hackerman

Reputation: 12305

How about something like this:

DECLARE @MYDATE DATETIME2;
DECLARE @MYTIME VARCHAR(5);

SET @MYDATE = '2016-11-30';  -- this is equal to 2016-11-30 00:00:00.000000 
SET @MYTIME = '09:00';
-- for datetime2
SELECT CAST(CAST(LEFT(@MYDATE,10) AS VARCHAR) + ' ' + @MYTIME + ':00.000000' AS DATETIME2) 
-- for datetime
SELECT CONVERT(DATETIME,CAST(LEFT(@MYDATE,10) AS VARCHAR) + ' ' + @MYTIME + ':00') -- for datetime

Upvotes: 0

Related Questions