Concatenate Date and Time Variable using SQL Query

Question

I have two Columns in my SQL Table. One is DateTime and The other is Time They are in the Format, DateTime - 2012-04-05 16:58:56.000 and Time - 21:30:00.00000

I need to write a Query which will return a Column as - 2012-04-05 21:30:00.000

Can you Help me?

score 9 · Accepted Answer · edited Jan 18 '21 at 12:30

9

~~In SQL-Server 2008, you can add the values this way:~~

select cast(dt as date) + cast(tm as datetime)
from yourtable

See SQL Fiddle with Demo

The above no longer works, here is an updated version that will work in SQL Server. This can also be edited to include additional precision:

SELECT dateadd(day, datediff(day,'19000101',dt), CAST(tm AS DATETIME))
from yourtable

See Demo.

edited Jan 18 '21 at 12:30

Community

1
1

answered Sep 10 '12 at 10:54

Taryn

242,637
56
362
405

This did not work from in 2012 and this actually changed the date to some new date – Avi Jul 25 '16 at 15:32
@hsanivag Considering this was tagged with SQL Server 2008 the answer was specific to that version. The SQL Fiddle link confirmed that it did work for that version. – Taryn Jul 25 '16 at 15:36
Please check the explanation here, this the code is working fine but for specific scenario and the links below will give you better explanation http://stackoverflow.com/a/700647/2634848 – Avi Jul 25 '16 at 15:46
@hsanivag I've updated the answer with a version that should work in 2012. – Taryn Jul 25 '16 at 15:54

score 0 · Answer 2 · answered Sep 10 '12 at 11:01

0

This Worked For Me.

Select DateAdd(d, DateDiff(d, 0, Cast(OrderDate As datetime)), Cast(Delivery_Time as datetime))   From myTable

answered Sep 10 '12 at 11:01

Krishna Thota

6,646
14
54
79

score 0 · Answer 3 · answered Jul 19 '16 at 20:07

0

SELECT CAST(date_col AS DATETIME)+CAST(time_col AS DATETIME) FROM your_table

Tested on SQL SERVER 2012

answered Jul 19 '16 at 20:07

Park Keeper

520
4
6

Works fine but for specific scenario. Please check the below link for additional explanation http://stackoverflow.com/a/700647/2634848 – Avi Jul 25 '16 at 15:48

score -1 · Answer 4 · answered Sep 10 '12 at 10:40

-1

Try this::

Select CONCAT(DATE(column_dateTIME),column_Time) from my Table

answered Sep 10 '12 at 10:40

Sashi Kant

13,277
9
44
71

It Returns `'DATE' is not a recognized built-in function name.` – Krishna Thota Sep 10 '12 at 10:47
@SashiKant The question is about sql-server, I have retagged it as such already. How is your sql-server kung fu Sashi? – Fluffeh Sep 10 '12 at 10:50

manish chavda · Answer 5 · 2012-09-10T11:03:36.157

-1

For MySQL

Select CONCAT_WS(" ", date(dattime_columnname), time(time_column)) from table_name;

For SQL Server 2008:

select convert(varchar, datetime_column, 103) + ' ' + convert(varchar, time_column, 114)

edited Sep 10 '12 at 11:03

answered Sep 10 '12 at 10:55

manish chavda

19
1
3

Joe G Joseph · Answer 6 · 2012-09-10T10:59:23.297

-2

try this:

This should work in SQL SERVER

select CONVERT(varchar(10),dt_col,121)+' '+cast(t_col as varchar(20)) 
from your_table

edited Sep 10 '12 at 10:59

answered Sep 10 '12 at 10:54

Joe G Joseph

23,518
5
56
58

Concatenate Date and Time Variable using SQL Query

6 Answers6