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)

Possible duplicate of [SQL add Datetime add hour add Minute](https://stackoverflow.com/questions/8020744/sql-add-datetime-add-hour-add-minute) — Tanner, Sep 05 '17 at 10:44

score 2 · Answer 1 · answered Sep 05 '17 at 10:38

2

You can add two datetime values together, so try:

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

answered Sep 05 '17 at 10:38

Gordon Linoff

1,242,037
58
646
786

You can leave out the `TIME` cast -- `11:00` will convert directly to a `DATETIME` (to wit, `1900-01-01 11:00`). Even so, this trick does not work for `DATETIME2`, which is unfortunate if you need the precision. – Jeroen Mostert Sep 05 '17 at 10:41

score 1 · Accepted Answer · answered Sep 05 '17 at 10:38

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

answered Sep 05 '17 at 10:38

Zohar Peled

79,642
10
69
121

score 0 · Answer 3 · answered Sep 05 '17 at 10:40

0

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

answered Sep 05 '17 at 10:40

Rajan

303
1
12

GuidoG · Answer 4 · 2017-09-05T10:52:59.093

0

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

edited Sep 05 '17 at 10:52

answered Sep 05 '17 at 10:41

GuidoG

11,359
6
44
79

what about seconds and fractional seconds? – Jodrell Sep 05 '17 at 11:53
@Jodrell I did not cover that because they are also not in the question. The OP stated the time is in hh:mm format. I even asked this in a comment – GuidoG Sep 05 '17 at 13:05
@Jodrell And I also mention in bold text that this will only work when the time is in hh:mm format – GuidoG Sep 05 '17 at 13:10

score 0 · Answer 5 · answered Sep 05 '17 at 11:47

0

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

answered Sep 05 '17 at 11:47

Jodrell

34,946
5
87
124

Concatenate date and string to create datetime in SQL

5 Answers5