Unanswered: Padding with 0's to obtain correct length?
I have a column I wish to select called event_hour. Its of the format HHMM and I wish to group my results by the hour. i.e. substr(event_hour,1,2).
However, the values vary from either 3 characters of 4 characters. The time is supposed to be coded by the 24hr clock but but its not quite like that.
0900hrs will be '900'
1000hrs will be '1000'
2130hrs will be '2130
0830hrs will be '830'
So, by grouping using the substr I get messy results. Is it possible to pad out the data with a 0 on those short values so I can perform a correct substr?
I thought of concatinating but that would lead me to have not only the correct 0830 for example, but also incorrect 02130.
Im sure there must be a simple solution. But I'm no expert.