-1

I have a date column with datatype as Date and a time column with datatype as Time.

Column A    Column B
4/6/2015    13:25:18
4/10/2015   12:52:14
4/10/2015   10:39:48

I need to join these two columns and insert into a column with datatype Datetime.

Column C
2015-06-04 13:25:18
2015-10-4 12:52:14
2015-10-4 10:39:48

I have only date in one column and only time in another column and i'm using the below now which is throwing an error

CONVERT(datetime, LEFT([ColumnA], 12) + LEFT([Column B], 7), 101) as ColumnC
sqluser
  • 5,502
  • 7
  • 36
  • 50
Chaitanya Komatla
  • 11
  • 1

2 Answers2

1
CREATE TABLE #test([Column A] DATE, [Column B] TIME, [Column C] DATETIME2)

INSERT INTO #test([Column A], [Column B]) VALUES  ('4/6/2015', '13:25:18')
                                                 ,('4/10/2015', '12:52:14')
                                                 ,('4/10/2015', '10:39:48')

UPDATE #test
SET [Column C] = CAST([Column A] AS VARCHAR(10)) + ' ' + CAST([Column B] AS VARCHAR(8))

SELECT * FROM #test
sqluser
  • 5,502
  • 7
  • 36
  • 50
0

This might help.

1. If you are just doing doing a select query, this will help:

SELECT FORMAT([Column A],'yyyy-MM-dd') + ' ' + FORMAT([Column B],'')AS [Column C] FROM TableName;

2. But, if you want to add new column to existing table and add existing date and time values of each row in this new column, this will help:

ALTER TABLE TableName
ADD [Column C] smalldatetime;
UPDATE TableName
SET [Column C] = FORMAT([Column A],'yyyy-MM-dd') + ' ' + CAST([Column B] AS varchar);
Topman
  • 306
  • 1
  • 4
  • 14