CODEWITHSUNDEEP
sql
Vaibhav Sinha
Vaibhav Sinha

Reputation: 69

Convert time in hh:mm:ss AM(PM) format (sql)

How do I convert time : ' 8:02:24 AM ' into ' 08:02:24 AM ' so that all the time in a particular column is of fixed length.

I tried using different style values in convert function but couldn't get my desired result.

Upvotes: 0

Views: 11368

Answers (2)

Trung Duong
Trung Duong

Reputation: 3475

You could use CONVERT function to convert all time value to 24 hours format 

SELECT CONVERT(VARCHAR, <your column name>, 108) AS HourMinuteSecond

You could check the result by executing the following query 

SELECT CONVERT(VARCHAR, CONVERT(DATETIME, '8:08:8 PM'), 108) AS HourMinuteSecond

If you don't want 24 hours format, you could use

SELECT REPLACE(RIGHT('0' + LTRIM(RIGHT(CONVERT(VARCHAR, <your column name>, 131), 14)), 11), ':000', ' ') AS HourMinuteSecond

Example:

   SELECT REPLACE(RIGHT('0' + LTRIM(RIGHT(CONVERT(VARCHAR, CONVERT(DATETIME, '8:8:8 PM'), 131), 14)), 11), ':000', ' ') AS HourMinuteSecond

Upvotes: 2

Rajesh Bhat
Rajesh Bhat

Reputation: 811

Below sample code will add 0 based on the hour part length (1 or 2).

declare @hour char(2)
    declare @time varchar(20)='8:02:24 PM'
    select @hour=substring(@time,1,charindex(':',@time)-1)
    if len(@hour)=1
    begin
       set @hour='0'+@hour
       set @time=@hour+substring(@time,charindex(':',@time),10)
    end   
    select @time

Upvotes: 0

Related Questions