Member Avatar for 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!

  • 2 Contributors
  • 2 Replies
  • 128 Views
  • 1 Day Discussion Span
  • Latest Post Latest Post by lafalot

Dani AI

Generated

Both suggestions in the thread are solid: is right that using a native date/time is the clean long‑term fix, and ’s lookup table is a pragmatic quick solution. Below are two compact SQL approaches that avoid schema overhaul and will let you sort correctly and efficiently.

A one‑query fix: convert 12h values to a 24h value in the ORDER BY. This handles the 12 AM/PM edge cases and sorts by minute as well.

ORDER BY
  CASE
    WHEN UPPER(LTRIM(RTRIM([am_pm]))) = 'AM' AND [hour] = 12 THEN 0
    WHEN UPPER(LTRIM(RTRIM([am_pm]))) = 'AM' THEN [hour]
    WHEN UPPER(LTRIM(RTRIM([am_pm]))) = 'PM' AND [hour] = 12 THEN 12
    WHEN UPPER(LTRIM(RTRIM([am_pm]))) = 'PM' THEN [hour] + 12
  END,
  [minute]

A performant schema tweak: add a computed, persisted column that stores minutes-since-midnight (or a numeric sort key) and index it. This avoids recalculating the CASE for every ordered row and scales better for large tables.

ALTER TABLE dbo.YourTable
ADD TimeSort AS (
  (CASE
     WHEN UPPER(LTRIM(RTRIM([am_pm]))) = 'AM' AND [hour] = 12 THEN 0
     WHEN UPPER(LTRIM(RTRIM([am_pm]))) = 'AM' THEN [hour]
     WHEN UPPER(LTRIM(RTRIM([am_pm]))) = 'PM' AND [hour] = 12 THEN 12
     ELSE [hour] + 12
   END) * 60 + [minute]
) PERSISTED;

CREATE INDEX IX_YourTable_TimeSort ON dbo.YourTable(TimeSort);

Notes and tips: ensure am_pm, hour, and minute contain valid, consistent values (normalize whitespace/case or use fixed codes) before persisting. If storage and arithmetic on times will be common, migrate to native SQL Server time/datetime types for best long‑term maintainability (see and computed column guidance in ).

Recommended Answers

All 2 Replies

Member Avatar for 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.

Member Avatar for 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!

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.