Please help me to build fast/optimized t-sql for converting 12 hour clock to 24 hour clock

I have a table contain only 2 columns(Time and AMPM). both columns are NVARCHAR.

Time......AMPM

07:30.....AM

08:45.....PM

12:00.....PM

01:00.....PM

12:00.....AM

I need to build t-sql to convert above data to

Time

07:30

20:45

12:00

13:00

00:00

as u may know, i want to convert 12 hour clock to 24 hour clock in custom string format. now, i have to convert time column to datetime variable and convert to 12 hour clock system again because i don't want to split the string and then perform addition/subtraction.

please suggest/guide me for the optimized t-sql to do this. i cannot perform this task on code due to some restriction. i was forced to do this on SQL SERVER 2005/2008 only.

thanks in adv and sorry for broken english.

vcha

You cannot use Time data type if you want to support MS SQL 2005. My solution uses some conversions:

-- test data:
SELECT '07:30' AS Time, 'AM' AS AMPM
INTO testTime
UNION ALL 
SELECT '08:45' AS Time, 'PM'
UNION ALL 
SELECT '12:00' AS Time, 'PM'
UNION ALL 
SELECT '01:00' AS Time, 'PM'
UNION ALL 
SELECT '12:00' AS Time, 'AM'
UNION ALL 
SELECT '07:15' AS Time, 'AM'

-- Final query
SELECT TIME, AMPM , 
       CASE WHEN AMPM = 'PM' THEN 
         CAST( CAST(LEFT(Time, 2) AS int) + 12 AS varchar)+substring(Time, 3, 3) 
       ELSE 
         Time 
       END
FROM testTime

Sorry, but it's not very clear what you're looking for here. What is the datatype containing the time? What is the datatype (or string format) of what you are trying to produce? Without these, the question is very open to (mis)interpretation.

With that said, some guidelines that may help:

• Convert your data (if necessary) to an appropriate date/time datatype
• SQL 2008 has the Time data type. If you can, use this to keep things simple
• Use the DATEADD function if/when you add 12 hours to a starting time. Use this to add 12 hours to your (converted) time for PM data.

But... properly configuring 12:00 AM or PM will be tricky. On the one hand, 12:00AM (aka midnight) is represented in 24 hour time as 00:00, which may or may not be what you want. But if you take 12:00 PM and (as per above) add 12 hours for "PM", you get to Midnight, which is not what you want. Odds are you'll have to do something tricky with a CASE statement. Here's a sample based on what I had before I realized I couldn't finish the query:

  ,dateadd(hh
           ,case
              when SomeTime = '12:00' and AM_PM = 'PM' then 0
              when SomeTime = '12:00' and AM_PM = 'AM' then 12
              when AM_PM = 'AM' then 0
              else 12  --  PM
            end
           ,cast(SomeTime as time))

I created a table called 12HourClock that contains the columns you specified in your example.

SELECT LEFT(CAST(CONVERT(time, [Time] + ' ' + AMPM, 8) AS nvarchar(5)), 5) AS 'Time'
FROM [12HourClock]