0

I am working with SQL Server. I have two columns in a table. The date column stores date as varchar. A sample data stored in the date column is 2006-05-29 00:00:00.000. The time column is also stored as varchar. A sample data stored in the time column is 02:00 PM. I have to concatenate data and time and compare to another date and time in a similar format.

Essentially I have to concatenate 2006-05-29 00:00:00.000 and 02:00 PM and then concatenate another piece of data 2006-05-29 00:00:00.000 and 08:00 PM and compare them.

Dale K
  • 25,246
  • 15
  • 42
  • 71
Massey
  • 1,099
  • 3
  • 24
  • 50
  • 1
    Wait, what? Why not compare the dates with the dates and the times with the times? – Laughing Vergil Sep 23 '19 at 20:40
  • Possible duplicate of [How to combine date from one field with time from another field - MS SQL Server](https://stackoverflow.com/questions/700619/how-to-combine-date-from-one-field-with-time-from-another-field-ms-sql-server) – Sebastian Brosch Sep 23 '19 at 20:44
  • *The date column stores date as varchar* - well, that's your main problem right there. After all: you should **always** use the **most appropriate** datatype for everything - and using `varchar` for storing dates and times ***ISN'T*** the most appropriate datatype. Fix that first - before wasting any more time on trying to compare those columns..... – marc_s Sep 23 '19 at 20:47
  • Sebastian, I already saw that question which you have mentioned as duplicate. However, the format in which time is stored in my case is totally different. Thanks. – Massey Sep 23 '19 at 20:47
  • marc_s, it's an existing table and they don't want to change the format now. – Massey Sep 23 '19 at 20:48
  • @Massey - But you didn't tried, do you? the solutions working with your values too. – Sebastian Brosch Sep 23 '19 at 20:49

1 Answers1

0

Storing date/time values as strings is not the way to go. SQL Server (and other databases) have built-in types for date/time values. Use them! They are there for a reason.

That said, your strings are readily convertible to date/time values. And you can add two datetimes. So you can do:

select try_convert(datetime, datecol) + convert(datetime, try_convert(time, timecol))
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786