0

Hope you can help. I have a Date column and a time column and i want to be able to combine these together within a select statement so there is just one column for [Date and Time]. Everything ive tried seem to add them together instead of combining/appending.

Cheers, :)

PDB
  • 103
  • 2
  • 3
  • 13
  • What you mean by add them together? – sll Sep 06 '11 at 11:04
  • No just taking what is in DateColumn and TimeColumn & then showing them together in another column so it would be Date&TimeColumn – PDB Sep 06 '11 at 11:05
  • @sllev, adding them together mathematically – PDB Sep 06 '11 at 11:09
  • possible duplicate of [How to combine date from one field with time from another field - MS SQL Server](http://stackoverflow.com/questions/700619/how-to-combine-date-from-one-field-with-time-from-another-field-ms-sql-server) – Davide Piras Sep 06 '11 at 11:10
  • http://stackoverflow.com/questions/700619/how-to-combine-date-from-one-field-with-time-from-another-field-ms-sql-server – Davide Piras Sep 06 '11 at 11:10
  • Hi, sorry for the late response. Its come to light now that once the new [Date&Time] column has been created it will be included in a where clause to see if it is greater than another [Date&Time] column. also it is SQL server 2005 – PDB Sep 06 '11 at 13:09

1 Answers1

5

In SQL Server 2008 R2 you can use this(not in 2005):

DECLARE @TESTTBL TABLE ( dt DATE, tm TIME)
INSERT INTO @TESTTBL VALUES('2011-02-03', '01:02:03')
INSERT INTO @TESTTBL VALUES('2011-02-04', '02:03:04')

SELECT CAST(dt AS DATETIME) + CAST(tm AS DATETIME) FROM @TESTTBL

Result will be:

2011-02-03 01:02:03.000

2011-02-04 02:03:04.000

If you want the text's together, use varchar instead of datetime in the cast().

oddbear
  • 193
  • 1
  • 8
  • 1
    Yes, date & time is only in 2008. In 2005, it would most likely use two datetimes instead, and the right solution for the varchar version would be something like: SELECT LEFT(CONVERT ( varchar , dt ,21 ), 11) + RIGHT(CONVERT ( varchar , tm ,21 ), 9) FROM @TESTTBL – oddbear Sep 06 '11 at 11:31
  • This will work as well 'SELECT dt + CAST(tm AS DATETIME) FROM @TESTTBL' – t-clausen.dk Sep 06 '11 at 12:27
  • Hi, sorry for the late response. Its come to light now that once the new [Date&Time] column has been created it will be included in a where clause to see if it is greater than another [Date&Time] column. also it is SQL server 2005 – PDB Sep 06 '11 at 13:03
  • @ODDBEAR just tried your solution and it seems to work, thanks alot!! – PDB Sep 06 '11 at 13:14