3

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)

Improve this question
3
  • how does 7 hours looks like, 07:00 or 7:00 ? Commented Sep 5, 2017 at 10:36
  • 7 hours is displayed as 07:00 in the string field. Commented Sep 5, 2017 at 10:37
  • 1
    Possible duplicate of SQL add Datetime add hour add Minute Commented Sep 5, 2017 at 10:44

5 Answers 5

Reset to default
2

You can add two datetime values together, so try:

CAST(dtDate AS DATETIME) + CAST(CAST(szTime AS TIME) as DATETIME)
Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

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.
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

Improve this answer

Comments

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
Improve this answer

Comments

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

Improve this answer

3 Comments

what about seconds and fractional seconds?
@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
@Jodrell And I also mention in bold text that this will only work when the time is in hh:mm format
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

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.