alanneese58
asked on
Converting Military Time to regular time.
I have a field called "Start_Time" which is an Int. It holds military time values (1800 for 6:00 PM). How do I get SQL to display "06:00 PM" instead of "1800" on my reports?
declare @time int
set @time = 1800
select convert( varchar(20), convert(datetime, '1/1/2000 ' + convert(varchar(10), @time/100) + ':00' ),100)
i hardcoded the 1st january here, but you could add your own date in there
set @time = 1800
select convert( varchar(20), convert(datetime, '1/1/2000 ' + convert(varchar(10), @time/100) + ':00' ),100)
i hardcoded the 1st january here, but you could add your own date in there
ASKER
KarinLoos,
Can you remove the date in your example? I just need the time displayed.
Can you remove the date in your example? I just need the time displayed.
declare @time int
set @time = 2300
select ltrim(right( convert( varchar(20), convert(datetime, '1/1/2000 ' + convert(varchar(10), @time/100) + ':00' ), 100) ,7))
bearing in mind that you cannot have a time which is 2400 -- in this case the value in your field must be 0
set @time = 2300
select ltrim(right( convert( varchar(20), convert(datetime, '1/1/2000 ' + convert(varchar(10), @time/100) + ':00' ), 100) ,7))
bearing in mind that you cannot have a time which is 2400 -- in this case the value in your field must be 0
ASKER
Your solution is not taking into account the minutes. 2330 should return "11:30 PM".
yes, ok then use
declare @time int
declare @mytime varchar(10)
set @time = 2330
SEt @mytime =
(CASE
WHEN @time >= 1200
then
REPLACE( convert(varchar(10), convert( decimal(6,2), ( convert( decimal(6,2), @time) / 100)) ), '.', ':') + 'PM'
else
REPLACE( convert(varchar(10), convert( decimal(6,2), ( convert( decimal(6,2), @time) / 100)) ), '.', ':') + 'AM'
end)
select @mytime
i used a variables here (ie @mytime and @mytime, but in your query it could look
select (CASE
WHEN timefieldname >= 1200
then
REPLACE( convert(varchar(10), convert( decimal(6,2), ( convert( decimal(6,2), timefieldname ) / 100)) ), '.', ':') + 'PM'
else
REPLACE( convert(varchar(10), convert( decimal(6,2), ( convert( decimal(6,2), timefieldname ) / 100)) ), '.', ':') + 'AM'
end) as sometime
from table1
declare @time int
declare @mytime varchar(10)
set @time = 2330
SEt @mytime =
(CASE
WHEN @time >= 1200
then
REPLACE( convert(varchar(10), convert( decimal(6,2), ( convert( decimal(6,2), @time) / 100)) ), '.', ':') + 'PM'
else
REPLACE( convert(varchar(10), convert( decimal(6,2), ( convert( decimal(6,2), @time) / 100)) ), '.', ':') + 'AM'
end)
select @mytime
i used a variables here (ie @mytime and @mytime, but in your query it could look
select (CASE
WHEN timefieldname >= 1200
then
REPLACE( convert(varchar(10), convert( decimal(6,2), ( convert( decimal(6,2), timefieldname ) / 100)) ), '.', ':') + 'PM'
else
REPLACE( convert(varchar(10), convert( decimal(6,2), ( convert( decimal(6,2), timefieldname ) / 100)) ), '.', ':') + 'AM'
end) as sometime
from table1
ASKER
One last thing and I think we have it. You need to subtract the twelve hours from the time if it is PM. 2330 is returning "23:30 PM".
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
did my solution return an error?
ASKER
Lowfatspread,
I didn't understand what merid was. Plus it would have helped me understand your solution better if you had used my variable names in it also.
Thanks.
I didn't understand what merid was. Plus it would have helped me understand your solution better if you had used my variable names in it also.
Thanks.
From (
select Right('0000'+convert(varch
,' ' + Case When Start_time > 1159 then 'PM' else 'AM' end as Merid
From YourTable
) as X