CODEWITHSUNDEEP
sqlsql-serversql-server-2008t-sqlsql-server-2008-r2
007
007

Reputation: 2186

Convert Time - T-SQL

I have 05:00 AM or 5:00 AM or 00:05:00 AM or 00:5:00 AM or 5 AM (hours....but can also have minutes or seco

I want to convert this to HH:MM:SS The above example is in Hours but it could be in minutes or seconds...

Now, I don't know if this is possible as let's say 05:00 AM could be 5 hours or 5 minutes. Has anyone came across such situation before? If so, how did you go about dealing with this?

I suppose we can use LEN function to check if it's HOURS or minutes so if 05:00 AM or 5:00 AM, it's minutes if it's 05:00:00 AM or 5:00:00 AM, it's hours.

Or we can check for the # of ":" occurrences and decide if it's hours or minutes or seconds and convert based on that...

Anyone has a better solution?

Thank you

Upvotes: 0

Views: 787

Answers (1)

Osa E
Osa E

Reputation: 1781

  1. Cast as date
  2. Convert to string using format

Below is example of all combinations entered above. Every one returns 05:00:00

select convert(varchar(15), cast('05:00 AM' as datetime), 108)
  , convert(varchar(15), cast('5:00 AM' as datetime), 108)
  , convert(varchar(15), cast('00:05:00 AM' as datetime), 108)
  , convert(varchar(15), cast('00:5:00 AM' as datetime), 108)
  , convert(varchar(15), cast('5 AM' as datetime), 108)

Upvotes: 1

Related Questions