mguptill
asked on
Convert Numeric field to Time
I have a numeric field starttime_t numeric(5,2) which stores decimal time in quarter hours. example 8.15, 8.30,8.45, 9.00 what I need to do is report this as time. example 08:15:00
8.15pm should be 20:15:00 any help will be appriciated
8.15pm should be 20:15:00 any help will be appriciated
ASKER
Yes it is I don't care if it says AMor PM here is some sample data
20.00 I still want the same format so in this case I would like 20.00 to reflect 20:00:00
20.00 I still want the same format so in this case I would like 20.00 to reflect 20:00:00
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you for your responses I'm sorry I didn't get back right away I had to go somewhere in a hurry. i will look at these and get back to you soon. again sorry for taking so long
ASKER
Thank you both for your solutions and they both worked but Wizilling's solution is what I needed although I will distribute the points accordingly because I believe in fairness and think Lowfatsread should get some points for his effort. Again I thank you both. Sorry lowfat i didn't need the date added.
I don't know why you don't do something simple like this:
DECLARE @starttime_t numeric(5,2)
SET @starttime_t = '8.20'
SELECT CONVERT(varchar(8), CONVERT(datetime, REPLACE(@starttime_t, '.', ':'), 8), 8)
DECLARE @starttime_t numeric(5,2)
SET @starttime_t = '8.20'
SELECT CONVERT(varchar(8), CONVERT(datetime, REPLACE(@starttime_t, '.', ':'), 8), 8)
ASKER
That works great ac thanks
can you confirm that 8:15 pm is stored as 20.15 in the database?