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

mguptillAsked:
Who is Participating?
 
WizillingConnect With a Mentor Commented:
declare @t numeric(5,2) = '8.30'

select substring(convert(varchar(100), cast(replace(CAST(@t as varchar(10)), '.', ':') as datetime), 120), 12,8)
0
 
WizillingCommented:
hi - if its a numeric field then you wont be able to store pm.
can you confirm that 8:15 pm is stored as 20.15 in the database?
0
 
mguptillAuthor Commented:
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
0
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

 
LowfatspreadConnect With a Mentor Commented:
CONVERT(DATETIME,RIGHT('0'+replaCE(CONVERT(VARCHAR(5),convert(SMALLMONEY,starttime_t),0),'.',':'),5)+':00.000',8)
0
 
mguptillAuthor Commented:
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
0
 
mguptillAuthor Commented:
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.
0
 
Anthony PerkinsCommented:
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)
0
 
mguptillAuthor Commented:
That works great ac thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.