-3

I have a table t1:(Have added Datetime1 column with no values initially, later want to update the values in Datetime1 field. Type of Date1 is date and type of Time1 is time)

Date1         Time1         Datetime1
2021-07-19  02:00:00.000     NULL
2021-07-20  01:00:00.000     NULL
2021-07-21  12:00:00.000     NULL

I want to add another column Datetime1 which will be the concatenation of both date and time

Expected output:

Date1         Time1         Datetime1
2021-07-19  02:00:00.000   2021-07-19 02:00:00.000
2021-07-20  01:00:00.000   2021-07-20 01:00:00.000
2021-07-21  12:00:00.000   2021-07-21 12:00:00.000

Code:

update t1
set Datetime1 = cast(concat(Date1,' ',Time1) as datetime)

I am getting wrong output after using this above code:

Date1         Time1         Datetime1
2021-07-19  02:00:00.000   1900-01-01  02:00:00.000
2021-07-20  01:00:00.000   1900-01-01  01:00:00.000
2021-07-21  12:00:00.000   1900-01-01  12:00:00.000

Instead of dates I am getting 1900-01-01, can anyone please tell what im doing wrong

noob
  • 3,601
  • 6
  • 27
  • 73

1 Answers1

4

Convert both columns to datetime and then simply add them together

Example

Declare @YourTable Table ([Date1] date,[Time1] time,DateTime1 datetime)
Insert Into @YourTable Values 
 ('2021-07-19','02:00:00.000',null)
,('2021-07-20','01:00:00.000',null)
,('2021-07-21','12:00:00.000',null)
 
Update @YourTable
   set DateTime1 = convert(datetime,[date1])+convert(datetime,[time1])

Results

Date1       Time1               DateTime1
2021-07-19  02:00:00.0000000    2021-07-19 02:00:00.000
2021-07-20  01:00:00.0000000    2021-07-20 01:00:00.000
2021-07-21  12:00:00.0000000    2021-07-21 12:00:00.000
John Cappelletti
  • 79,615
  • 7
  • 44
  • 66