0

I need to query a table with records that have a start_time and end_time field. Their data type is nvarchar(100) and formatted like this example: 09:30AM

I need to do a before or after comparison on these fields, and to do so I need them in the time data format. (Correct me if I am mistaken). In the query, how do I convert the start_time and end_time so I can do my comparison?

NOTE: I have tried convert(time, start_time), but get a Conversion failed when converting date and/or time from character string error message.

office_sql_guy
  • 1
  • 1
    Converting to time with your example works just fine. select convert(time, '09:30AM'). This most likely means you have some bad data in there somewhere....a solid example of why it is so critical to use the proper datatypes. – Sean Lange Nov 21 '17 at 20:38
  • Aside: Using a [persisted computed column](https://learn.microsoft.com/en-us/sql/t-sql/statements/alter-table-computed-column-definition-transact-sql) may be helpful. Of course, using the correct datatype is a better choice. – HABO Nov 21 '17 at 21:17

1 Answers1

1

It's simply:

SELECT CONVERT(TIME, '09:30AM')

OR

CAST('09:30AM' as time)

If the converision failed that mean you have some bad data somewhere, I would recommand the TRY_CONVERT if you have 2012+ which return NULL in case the coverison failed

SELECT TRY_CONVERT(TIME, '09:30AMBadDataHere')
Ilyes
  • 14,640
  • 4
  • 29
  • 55