Converting a Time to 24 hour time in SQL Server

Question

I'm using Microsoft SQL Server 2000 and need to convert my time column to 24 hour time instead of just 9:30 AM, 12:30 PM, etc. I'm trying to sort this column and I believe its not working because its just looking at things numerically instead of as the time. I think what I need can be done with the function FORMAT(Time, HH:mm:ss), but that doesn't seem to be a function in SQL Server, so I'm stuck now.

Answer 1

CONVERT(char(5), GETDATE(), 108)

Answer 2

First time I post something! I had to convert from M/D/YYYY h:mm:ss AM/PM to Datetime. Please note my localtime is DD-MM-YYYY so I had to convert in that format before converting to Datetime. Field name to convert was "Valeur" in table N_IsaacForm. Here is how I did it in SQL 2008:

select NIF.Valeur, CASE WHEN CHARINDEX('PM',NIF.Valeur) = 0 
THEN CONVERT(datetime,substring(NIF.Valeur, P2.Pos + 1, P3.Pos - P2.Pos - 1) + '-'
+ RIGHT('0' + substring(NIF.Valeur, P3.Pos + 1, P4.Pos - P3.Pos - 3),8) + '-'
+ RIGHT('0' + substring(NIF.Valeur, P1.Pos + 1, P2.Pos - P1.Pos - 1),2) + ' ' 
+ RIGHT('0' + substring(NIF.Valeur, 1, P1.Pos-1),2))
--add 12 hours if PM
ELSE DATEADD(hour,12,CONVERT(datetime,substring(NIF.Valeur, P2.Pos + 1, P3.Pos - P2.Pos - 1) + '-'
+ RIGHT('0' + substring(NIF.Valeur, P3.Pos + 1, P4.Pos - P3.Pos - 3),8) + '-'
+ RIGHT('0' + substring(NIF.Valeur, P1.Pos + 1, P2.Pos - P1.Pos - 1),2) + ' ' 
+ RIGHT('0' + substring(NIF.Valeur, 1, P1.Pos-1),2)))
END

FROM N_IsaacForm AS NIF (nolock)    
cross apply (select (charindex('/', NIF.Valeur))) as P1(Pos)
cross apply (select (charindex('/', NIF.Valeur, P1.Pos+1))) as P2(Pos)
cross apply (select (charindex(' ', NIF.Valeur, P2.Pos+1))) as P3(Pos)
cross apply (select (charindex('M', NIF.Valeur, P3.Pos+1))) as P4(Pos)
WHERE CHARINDEX('M',NIF.Valeur) > 0 --(I had other values in the table with   proper format)

You just have to change the order of the elements to set your particular local time.

Results obtained

1. 8/20/2015 1:11:31 AM ---> 2015-08-20 01:11:31.000

2. 8/19/2015 10:37:32 PM ---> 2015-08-19 22:37:32.000

3. 10/7/2015 8:51:37 PM ---> 2015-10-07 20:51:37.000

4. 9/8/2015 3:27:17 PM ---> 2015-09-08 15:27:17.000

Hope it may help someone ! I've found so much help on this site !