Concatenate date and string to create datetime in SQL

sql sql-server date datetime2
SayCodeWell picture SayCodeWell · Sep 5, 2017 · Viewed 7.2k times · Source

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

Gordon Linoff picture Gordon Linoff · Sep 5, 2017

You can add two datetime values together, so try:

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

Related questions

How to return only the Date from a SQL Server DateTime datatype

SELECT GETDATE() Returns: 2008-09-22 15:24:13.790 I want that date part without the time part: 2008-09-22 00:00:00.000 How can I get that?

Read More
Best approach to remove time part of datetime in SQL Server

Which method provides the best performance when removing the time portion from a datetime field in SQL Server? a) select DATEADD(dd, DATEDIFF(dd, 0, getdate()), 0) or b) select cast(convert(char(11), getdate(), 113) as datetime) The second method does send a …

Read More
Query comparing dates in SQL

I have a table with dates that all happened in the month November. I wrote this query select id,numbers_from,created_date,amount_numbers,SMS_text from Test_Table where created_date <= '2013-04-12' This query …

Read More