Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 330
  • Last Modified:

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

0
mguptill
Asked:
mguptill
2 Solutions
 
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
 
WizillingCommented:
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LowfatspreadCommented:
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

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now