June 7, 2007 at 6:16 am
I want to format the following the schedule desc. 'Every day at 94500' as 'Every day at 09:45:00 AM'.
Here I am adding the method which i tried to format the description. But i think it is not a good idea
set @schedule_description = 'Every day at 94500'
select (substring(@schedule_description, 1, charindex('at ', @schedule_description) + 1 )) + ' '+
left(REPLICATE('0', 6 - len(substring(@schedule_description, charindex('at ', @schedule_description)+ 3 , len(@schedule_description))))+
substring(@schedule_description, charindex('at ', @schedule_description)+ 3 , len(@schedule_description)), 2) + ':' +
substring(REPLICATE('0', 6 - len(substring(@schedule_description, charindex('at ', @schedule_description)+ 3 , len(@schedule_description))))+
substring(@schedule_description, charindex('at ', @schedule_description)+ 3 , len(@schedule_description)), 3, 2) + ':'+
right(REPLICATE('0', 6 - len(substring(@schedule_description, charindex('at ', @schedule_description)+ 3 , len(@schedule_description))))+
substring(@schedule_description, charindex('at ', @schedule_description)+ 3 , len(@schedule_description)), 2) +
case
when str(left(REPLICATE('0', 6 - len(substring(@schedule_description, charindex('at ', @schedule_description)+ 3 , len(@schedule_description))))+
substring(@schedule_description, charindex('at ', @schedule_description)+ 3 , len(@schedule_description)), 2))< 12
then ' AM'
else ' PM'
end as 'Schedule Description'
Please advice
Regards
Jismon thomas
June 7, 2007 at 7:55 am
As you have seen, manipulating strings is best done on the client or middle tier. If you really must do this in SQL then the following may be slightly more readable. It should also show the PM hours correctly. You will also need to check the AM/PM convention you require for midnight and noon.
SELECT ScheduleDesc + ' ' +
CASE
WHEN LEFT(ScheduleTime, 2) < '12'
THEN LEFT(ScheduleTime, 2)
+ ':' + SUBSTRING(ScheduleTime, 3, 2) + ':' + RIGHT(ScheduleTime,2) + ' AM'
ELSE REPLACE(STR(CAST(LEFT(ScheduleTime, 2) AS int) - 12, 2), ' ', '0')
+ ':' + SUBSTRING(ScheduleTime, 3, 2) + ':' + RIGHT(ScheduleTime,2) + ' PM'
END
FROM (
SELECT RTRIM(LEFT(@schedule_description, LEN(@schedule_description) - 6)) AS ScheduleDesc
,REPLACE(RIGHT(@schedule_description, 6), ' ', '0') AS ScheduleTime
) D
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply