Sort integer as hour

[lafalot]

Hi,

I have time stored as an integer in sql server. The time is separated into fields of hour, minute and am/pm. I need to output the data and sort the time, but of course, 12 is coming after 1. Is there a way to sort my hour integers in a time fashion?

Thank you!

[hinde]

I personally would not go for this particular way of storing times. If I was faced with this paritcular problem from a site that I was asked to maintain, I would write a one-shot script that would:
1. Alter the table to have an odbc datetime.
2. Read in all records from this table.
3. Loop over this table, updating each record's new odbc datetime with a translated version of your integer.

You would still have this integer field and instead of doing something programmatically with coldfusion you would foist the work off on SQL server, which would make this sorting perform better.

[lafalot]

Thanks for your advice, hinde. I think your solution is probably more sound than the one I came up with. I'm just not that comfortable w/sql server, so what I did was create a table with 3 columns: hour, sort, and am_pm. I put all 1-12 & 1-11 in the hour column, sort order in the sort column, and am or pm in the am_pm column. So, the columns with one and pm has 13 as a sort. Then, I did an innerjoin on my hour and am/pm field in the tabe, and used the sort field to order by. It seems to be working.

Thanks again!