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
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
17 People are following this question.
