question

deshazer72 avatar image
deshazer72 asked

I am trying to convert a var char time period to numeric

I have a column called time_period and data in it looks like this 0:00 - 0:59 it tracks through those time periods next will be 1:00 - 1:59 and so on. I want to write a query where i sort these in asc order but since they are var char they are not working correctly. I need to convert them to numeric so I been trying to cast them like this select * from sco_levels_hourly where report_date ='2017-02-06' order by store_nbr ASC, register_nbr, cast(time_period as numeric) asc I keep getting an error cant convert varchar to numeric i think it is because of the - I have tried to do replace and left but I am stuck or is there anyway to sort these strings because it goes from 1 to 10 instead of 1 to 2
sortingleftcast-convertcast
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

· Write an Answer
deshazer72 avatar image
deshazer72 answered
I figured it out this is my solution select * from sco_levels_hourly where report_date = '2017-02-06' order by store_nbr asc, register_nbr, cast(Replace(left(time_period, 4), ':', '.') as decimal(9,2)) ASC
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.