SQL Server - How do I convert a string time 2:30 PM to 24 hour 14:30 in sql 2008?

Asked By Tino on 25-Jun-13 03:57 PM

Hi All,
I have a few fields that are all varchar(8) and have string appointment times such as 2:30 PM. I want to format these fields to the 24 hour clock so I can get the elapsed time between fields. Is there an easy way to do this, or do I have to create a case statement for each field to do it?

Thanks for your help in advance.
Leonie Kruger replied to Tino on 25-Jun-13 04:29 PM
Hi,

select convert(varchar,cast('02:30:00pm ' as datetime), 8)

Kind Regards
Tino replied to Leonie Kruger on 25-Jun-13 05:11 PM
Thanks Leonie,
Unfortunately I got this error when I tried your code...

Msg 242, Level 16, State 3, Line 2
The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.
Msg 241, Level 16, State 1, Line 2
Conversion failed when converting date and/or time from character string.


Robbe Morris replied to Tino on 25-Jun-13 06:44 PM
Opting to store date/time values in a column as varchar was pretty stupid.  All queries for time ranges will return improper results because the query has to compare strings instead of date/time values.  Wrapping the where clause comparisons in data conversion functions for these strings will likely cause the optimizer not to use any indexes you have set up.

If it were me, I'd fix the core problem and not just attempt to put a bandaid on it that may or may not function in different request scenarios.
Tino replied to Robbe Morris on 25-Jun-13 07:18 PM
I agree with you 100% Robbe, but unfortunately that's the way the database is set up so I have to work with... or around what I have.
Robbe Morris replied to Tino on 25-Jun-13 08:27 PM
CREATE FUNCTION dbo.ConvertTimeToDateTime
(
  @mytime as varchar(50)
)
RETURNS datetime
AS
BEGIN
 
return cast('01/01/2013 ' + @mytime as DateTime)
 
END
GO


declare @mytime as varchar(50)

Set @mytime = '02:03 PM'

select dbo.ConvertTimeToDateTime(@mytime)

As long as the hardcoded date in the UDF is valid, this works for getting you a converted datetime object to work with.  Feel free to enhance to meet your needs.


Tino replied to Robbe Morris on 25-Jun-13 08:33 PM
Awesome, thanks Robbe. I will give that a try tomorrow.
Robbe Morris replied to Tino on 25-Jun-13 08:38 PM
just a heads up.  UDFs can cause a query to perform slower.  If you find that to be the case here, you'll want to manually type the code in the UDF in the actual SQL query instead.  Sucks I know...

Headin' to bed.  Got a big day of deep sea fishing for Mahi in Islamorada, FL tomorrow.  cya
Tino replied to Robbe Morris on 25-Jun-13 08:40 PM
That sounds AWESOME... the fishing part! Have fun man!