Concatenate date and string to create datetime in SQL

Question

I need to concatenate a datetime and a time field in SQL to a single date time.

e.g I have a datetime of 2017-09-05 00:00:00.000 and a string time of 11:00. What I want is a single field in a view of 2017-09-05 11:00:00.000

I have tried casting the datetime to a date and then concatenate the new date and string date field together but this doesn't work.

To cast the datetime I am using: CAST(dtDate AS DATE) AS dtNewDate which works fine. When I then use: CAST(dtNewDate + szTime AS datetime) AS dtNewDateTime the creation of the view works fine but selecting the top 1000 returns a "conversion failed when converting date and/or time from character string."

Is there an easier way to do this or can anyone offer some advise (other than storing the date and time in a single datetime field in the first place as it is populated by a third party application which I do not have access to change)

Answer 1

Assuming 11:00 stands for 11:00:00, you can do something like this:

SELECT dtDate + CONVERT(DateTime, szTime, 108)
FROM...

See a live demo on rextester

Answer 2

If you care about precision with DATETIME2,

DECLARE @D DATETIME = '2017-01-01';

DECLARE @T varchar(7) = '11:00';

SELECT DATEADD(day, DATEDIFF(day, '19000101', @D), CAST(CAST(@T AS TIME) AS DATETIME2(7)));

Running example here

Answer 3

You can add two datetime values together, so try:

CAST(dtDate AS DATETIME) + CAST(CAST(szTime AS TIME) as DATETIME)

Answer 4

You can try following.

DECLARE @YourDate AS DATETIME
SET @YourDate = CONVERT(VARCHAR(10), '2017-09-05 00:00:00.000', 111) + ' 11:00'
PRINT @YourDate

Answer 5

another way is to get the hour from your stringfield, convert it to int, and add that as hours to the datetime

declare @date datetime = '20170905'
declare @stringtime varchar(5) = '11:00'

select left(@stringtime, 2),
       dateadd(hour, convert(int, left(@stringtime, 2)), @date)

If you also need the minutes you can do it like this :

declare @date datetime = '20170905'
declare @stringtime varchar(5) = '11:05'

select left(@stringtime, 2),
       right(@stringtime, 2),
       dateadd(minute, convert(int, right(@stringtime, 2)), dateadd(hour, convert(int, left(@stringtime, 2)), @date))

This will only work if the stringfield is always in format hh:mm