Clarity LOS Calculation with DATEDIFF: Admit-to-Discharge Logic for Epic Analysts

A Clarity LOS calculation looks like one line of SQL: DATEDIFF between admit and discharge. It is also the most common reason two Epic reports show different length-of-stay numbers for the same patients. The function counts calendar boundaries, not elapsed days, and the definition you pick changes the answer. This article shows which definition fits which question and how to prove your number.

I have reconciled LOS metrics between quality, finance, and operations teams, and the argument is almost never about the data. It is about the definition. Column names below follow standard Clarity. Your version may differ, so check the Clarity Data Dictionary on Epic UserWeb before you ship a report. If encounter tables are new to you, read the PAT_ENC table guide first, then return here.

What Length of Stay Means in a Clarity LOS Calculation

Length of stay measures the time between admission and discharge. Hospitals use at least five versions of it. Each answers a different question, and each produces a different number from the same encounter.

Notice that calendar days and midnight census agree for this stay but not for every stay. A patient admitted 4/1 at 23:50 and discharged 4/2 at 00:10 crosses one midnight and stays 20 minutes. DATEDIFF reports 1 day. Elapsed hours report 0.33.

No definition is wrong. A wrong report uses one definition under another definition’s name. Put the definition in the report title or footer, as in “LOS (midnight census days).”

Why the definition matters for money and quality

CMS publishes geometric mean length of stay for each MS-DRG, which hospitals use as an expected-LOS benchmark. A hospital that computes observed LOS with a different counting rule than the benchmark compares unlike measures. The CMS MS-DRG classifications and software page lists the impact files that carry those values.

Source Columns for LOS in Epic Clarity

Inpatient LOS starts in the hospital encounter tables. PAT_ENC holds the contact. PAT_ENC_HSP extends it with hospital-specific times and categories, joined on PAT_ENC_CSN_ID.

CSN, account, and unit grain

LOS can be measured per hospital encounter, per billing account, or per nursing unit. A patient who moves from the emergency department to a medical floor to intensive care has one hospital stay and three unit stays. Whether the ED and inpatient portions share a CSN depends on your ADT workflow, so test it with real examples before you assume.

Pick the grain first, then the formula. Mixing them is the second most common reconciliation failure after the counting rule.

How DATEDIFF Behaves in a Clarity LOS Calculation

DATEDIFF in SQL Server counts the number of boundaries crossed for the unit you name. It does not measure elapsed time. The Microsoft DATEDIFF documentation states this directly, and it surprises analysts who expect a stopwatch.

The first row shows the problem. Twenty minutes of care becomes one hospital day. The second row shows the opposite: a nearly 24-hour stay becomes zero days. Averages hide these errors, but counts of “same-day discharges” and “one-day stays” do not.

-- Calendar-day LOS (midnights crossed)
SELECT  h.PAT_ENC_CSN_ID,
        h.HOSP_ADMSN_TIME,
        h.HOSP_DISCH_TIME,
        DATEDIFF(day, h.HOSP_ADMSN_TIME, h.HOSP_DISCH_TIME)  AS los_calendar_days,
        DATEDIFF(minute, h.HOSP_ADMSN_TIME, h.HOSP_DISCH_TIME) / 60.0 AS los_hours
FROM    PAT_ENC_HSP h
WHERE   h.HOSP_DISCH_TIME >= '2026-09-01'
  AND   h.HOSP_DISCH_TIME <  '2026-10-01'
  AND   h.HOSP_ADMSN_TIME IS NOT NULL;

Use minutes as the base unit for elapsed time, then divide. DATEDIFF(hour, ...) also counts boundaries, so a 20-minute stay across the top of an hour returns 1 full hour. Minutes give you the resolution you need, and you convert once at the end.

Dialect differences that bite during migrations

Clarity runs on SQL Server at many sites and on Oracle at others. Caboodle and cloud warehouses add Snowflake and similar engines. The same idea needs different syntax, and the semantics are not identical.

When you port a LOS query between engines, rerun your boundary examples (the four rows above) on both. A matching total can hide opposite errors in individual rows. Teams that move reporting into Caboodle should test the same cases against the warehouse’s date dimension logic.

Scenario: Heart Failure LOS of 4.1 Days in Quality and 3.6 Days in Finance

A 400-bed community hospital tracked heart failure length of stay on two dashboards. The quality team reported 4.1 days. Finance reported 3.6. Both pulled from Clarity, both covered the same discharge month, and the CMO wanted one number before a board meeting on Thursday.

I was the analyst asked to reconcile them. The two queries differed in three ways. Quality used elapsed hours divided by 24. Finance used DATEDIFF on dates. And quality included observation patients who later converted to inpatient, while finance counted inpatient class only.

I rebuilt both numbers one factor at a time. That is the only way to attribute a gap. Changing three things at once leaves you with a total and no explanation.

The counting rule moved the average by 0.27 days. The class rule moved it by 0.12. The expired-patient rule moved it by 0.11. None of the three was an error. The hospital simply never wrote the definition down. The CMO picked midnight census days, inpatient class only, expired patients included, and both dashboards changed that week.

The before query (Quality)

-- BEFORE: elapsed hours / 24, includes observation conversions
SELECT  AVG(DATEDIFF(minute, h.HOSP_ADMSN_TIME, h.HOSP_DISCH_TIME) / 60.0 / 24.0) AS avg_los_days,
        COUNT(*) AS encounters
FROM    PAT_ENC_HSP h
JOIN    HSP_ACCOUNT a ON a.HSP_ACCOUNT_ID = h.HSP_ACCOUNT_ID
WHERE   h.HOSP_DISCH_TIME >= '2026-09-01'
  AND   h.HOSP_DISCH_TIME <  '2026-10-01'
  AND   a.FINAL_DRG_ID IN (291, 292, 293);   -- placeholder: DRG key varies by site

The DRG filter above is a placeholder. Clarity stores DRG through a separate dictionary table at many sites, so map the key your build uses. Do not paste the literal numbers into production code.

The after query (agreed definition)

-- AFTER: midnight census days, inpatient class only
SELECT  AVG(CAST(DATEDIFF(day, CAST(h.HOSP_ADMSN_TIME AS date),
                                CAST(h.HOSP_DISCH_TIME AS date)) AS decimal(9,2))) AS avg_los_days,
        COUNT(DISTINCT h.PAT_ENC_CSN_ID) AS encounters
FROM    PAT_ENC_HSP h
JOIN    ZC_PAT_CLASS pc ON pc.ADT_PAT_CLASS_C = h.ADT_PAT_CLASS_C
WHERE   h.HOSP_DISCH_TIME >= '2026-09-01'
  AND   h.HOSP_DISCH_TIME <  '2026-10-01'
  AND   h.HOSP_ADMSN_TIME IS NOT NULL
  AND   pc.NAME = 'Inpatient';

Casting both values to date before DATEDIFF removes the time-of-day effect and gives you midnights crossed. That equals the midnight census count for a stay with continuous presence. Document the count rule next to the number.

Midnight Census LOS and the Same-Day Discharge Rule

Patient days under CMS-style counting include the day of admission and exclude the day of discharge. That matches calendar-day DATEDIFF for most stays. The exception is the same-day stay, which returns 0.

A zero breaks averages less than it breaks ratios. A hospital that divides cost by LOS gets a divide-by-zero or an infinite cost per day. Some programs fix this by counting a same-day inpatient stay as one day. Decide the rule and apply it in the query, not in the dashboard.

-- Same-day rule: minimum 1 day
SELECT  h.PAT_ENC_CSN_ID,
        CASE WHEN DATEDIFF(day, h.HOSP_ADMSN_TIME, h.HOSP_DISCH_TIME) = 0
             THEN 1
             ELSE DATEDIFF(day, h.HOSP_ADMSN_TIME, h.HOSP_DISCH_TIME)
        END AS los_days_min1
FROM    PAT_ENC_HSP h
WHERE   h.HOSP_DISCH_TIME IS NOT NULL;

Label the output column los_days_min1, not los_days. The name tells the next analyst the rule exists.

Elapsed-Hour LOS and the Daylight Saving Problem

Throughput projects, such as emergency department or surgical recovery times, need hours. Clarity stores times in local time at most sites, without a time zone offset. Two nights a year, that breaks elapsed arithmetic.

In the fall, 1:30 a.m. occurs twice. A patient admitted at 12:30 a.m. and discharged at 2:30 a.m. spent three hours in the building but shows two on the clock. In the spring, the hour from 2:00 to 3:00 a.m. does not exist, so a two-hour overnight stay appears as one.

One hour on two nights matters little for a monthly average. It matters for a single audit sample or a short-stay metric, such as door-to-discharge under four hours. Flag stays that span the transition dates, and document the limitation rather than build time-zone logic that your source does not support.

Scenario: The Long-Stay Report That Hid the Longest Stays

A case management director asked for a weekly list of inpatients with a length of stay over ten days. The analyst built it from discharged encounters and wondered why the list looked short. It was short because a patient still in the hospital has no discharge time, and DATEDIFF against null returns null.

Every current long-stay patient dropped out of the query. The report showed only patients who had already left. The director could not act on any of them.

-- Running LOS for current inpatients (no discharge time yet)
SELECT  h.PAT_ENC_CSN_ID,
        h.HOSP_ADMSN_TIME,
        DATEDIFF(day, h.HOSP_ADMSN_TIME, GETDATE()) AS los_to_date
FROM    PAT_ENC_HSP h
WHERE   h.HOSP_ADMSN_TIME IS NOT NULL
  AND   h.HOSP_DISCH_TIME IS NULL
  AND   DATEDIFF(day, h.HOSP_ADMSN_TIME, GETDATE()) > 10;

Two cautions apply. First, Clarity is a nightly copy, so GETDATE() measures against today while the data reflects last night. Use the extract completion time as the end point when you compare to a sealed report. Second, a null discharge time can also mean bad data, such as a missing ADT discharge event for a patient who left.

For a live census, use a Chronicles-based report instead. Clarity cannot tell you who is in a bed right now. The Epic Clarity SQL guide covers that timing boundary.

Decision Tree: Choosing the Right Clarity LOS Calculation

Use this tree before you write any query. It routes the request to the right definition and grain.

Who asked, and what decision
will the number drive?

Benchmark vs peers
Quality, CMS comparison

Cost or revenue per day
Finance, payer contracts

Speed of care
Throughput, ED, ICU

Midnight census days
Grain: encounter (CSN)
Class: inpatient only
Compare to GMLOS by DRG
Match the benchmark
counting rule exactly

Billing days, per account
Grain: account (HAR)
Same-day rule: min 1
Match payer day rules
Never divide by zero
days

Elapsed hours
Grain: encounter or unit
Use DATEDIFF(minute)
Report median, not mean
Flag daylight saving
transition stays

Median matters for hours-based metrics. A few very long stays pull the mean far from what a typical patient experiences, and quality teams often care about the typical case.

Worked Example: Verifying One Encounter by Hand

Before any aggregate query goes to a stakeholder, I verify one encounter end to end. The exercise takes fifteen minutes and exposes problems no total will show. Pick a test patient with a known stay, open the encounter in Hyperspace, and write down the admit and discharge times shown on the screen.

Then query the same CSN and compare. Here is a composite example from a validation round on a new inpatient build.

All five rows matched. The next test patient did not. That patient was admitted through the emergency department, and Clarity showed an admit time four hours earlier than the unit arrival. The build team had mapped HOSP_ADMSN_TIME to ED arrival for that workflow, which changed every LOS for ED-admitted patients.

Neither answer was wrong. One counted from the emergency department door and the other from the inpatient bed. We documented the choice and added an ED-to-floor interval as a separate metric. A tester who only compared totals would have signed off on a definition the quality team never approved.

Where LOS Lives: Clarity, Reporting Workbench, and Caboodle

The same metric appears in three places. Each has strengths, and each hides assumptions. Knowing which tool built a number tells you where to look when it disagrees with another.

When two sources disagree, rebuild the number in Clarity and trace each difference. Clarity sits closest to the source, and its logic is visible in the query text. That is why validation work starts there even when the production report lives elsewhere.

Template: LOS Metric Definition Sheet for Business Analysts

This is the sheet I attach to every LOS requirement. It forces the decisions that cause rework later. Copy it into your requirements tool and fill every line before development starts.

The cohort date row prevents a quiet error. A stay that starts in August and ends in September belongs to September by discharge date and August by admission date. Averages by admission month and discharge month differ, and the gap widens in months with long-stay patients.

Making LOS Queries Fast Enough to Rerun

A validation query you cannot rerun is a validation query nobody will run. LOS queries scan PAT_ENC_HSP, so small habits keep them fast on large tables.

Filter by discharge time range first. Select only the columns you need. If you reuse the cohort across several checks, write it to a temporary table once. Avoid wrapping the time columns in functions inside the WHERE clause, because that blocks index use.

-- Stage the cohort once, reuse for every check
SELECT  h.PAT_ENC_CSN_ID, h.HSP_ACCOUNT_ID, h.HOSP_ADMSN_TIME, h.HOSP_DISCH_TIME, h.ADT_PAT_CLASS_C
INTO    #los_cohort
FROM    PAT_ENC_HSP h
WHERE   h.HOSP_DISCH_TIME >= '2026-09-01'
  AND   h.HOSP_DISCH_TIME <  '2026-10-01'
  AND   h.HOSP_ADMSN_TIME IS NOT NULL;

-- Every later check reads the small table, not the large one
SELECT COUNT(*) AS cohort_rows FROM #los_cohort;

Talk to your DBA about reporting windows before you run large date ranges during clinic hours. Most sites provide an off-peak slot or a replica for analysts.

Observed vs Expected LOS: Building the LOS Index

A raw average LOS tells you little, because case mix drives it. A hospital with more complex patients stays longer without being slower. The standard fix compares observed LOS to an expected value for the same MS-DRG.

CMS publishes a geometric mean LOS and an arithmetic mean LOS for each MS-DRG in its IPPS final rule tables. Load the geometric mean into a reference table in your reporting schema. The index is observed days divided by expected days. A value above 1.0 means longer than expected.

-- LOS index by MS-DRG (reference table loaded from CMS IPPS tables)
SELECT  ref.MS_DRG,
        COUNT(*)                                   AS discharges,
        AVG(CAST(los.los_days AS decimal(9,2)))    AS observed_los,
        MAX(ref.GMLOS)                             AS expected_los,
        AVG(CAST(los.los_days AS decimal(9,2))) / NULLIF(MAX(ref.GMLOS), 0) AS los_index
FROM   (SELECT h.PAT_ENC_CSN_ID, h.HSP_ACCOUNT_ID, a.MS_DRG_CODE,
               DATEDIFF(day, CAST(h.HOSP_ADMSN_TIME AS date), CAST(h.HOSP_DISCH_TIME AS date)) AS los_days
        FROM PAT_ENC_HSP h
        JOIN HSP_ACCOUNT_DRG_VIEW a ON a.HSP_ACCOUNT_ID = h.HSP_ACCOUNT_ID   -- site-specific DRG source
        WHERE h.HOSP_DISCH_TIME >= '2026-01-01' AND h.HOSP_DISCH_TIME < '2026-10-01') los
JOIN    RPT_CMS_GMLOS ref ON ref.MS_DRG = los.MS_DRG_CODE
GROUP BY ref.MS_DRG
HAVING  COUNT(*) >= 30
ORDER BY los_index DESC;

HSP_ACCOUNT_DRG_VIEW and RPT_CMS_GMLOS are placeholder names. Your site stores final DRG assignment in its own structure, and your reference table is something you create. The HAVING COUNT(*) >= 30 clause avoids reporting an index for DRGs with too few discharges to mean anything.

Two limits apply. CMS geometric means come from Medicare fee-for-service claims, so they fit commercial and Medicaid populations imperfectly. And final DRG assignment happens after coding, which can take days. A fresh discharge has no final DRG, so recent months look incomplete.

Unit-Level LOS from ADT Events

ICU length of stay needs the ADT event table. Each admit, transfer, and discharge writes a row to CLARITY_ADT with a time. You pair each unit arrival with the next event to get the unit stay.

WITH ev AS (
    SELECT  a.PAT_ENC_CSN_ID, a.DEPARTMENT_ID, a.EFFECTIVE_TIME,
            LEAD(a.EFFECTIVE_TIME) OVER (PARTITION BY a.PAT_ENC_CSN_ID
                                         ORDER BY a.EFFECTIVE_TIME, a.EVENT_ID) AS next_time
    FROM    CLARITY_ADT a
    WHERE   a.EVENT_SUBTYPE_C <> 2            -- exclude canceled events; confirm code in ZC_EVENT_SUBTYPE
      AND   a.EVENT_TYPE_C IN (1, 3, 4)       -- admit, transfer in, discharge; confirm in ZC_EVENT_TYPE
)
SELECT  e.DEPARTMENT_ID,
        COUNT(*) AS unit_stays,
        AVG(DATEDIFF(minute, e.EFFECTIVE_TIME, e.next_time) / 60.0) AS avg_unit_hours
FROM    ev e
WHERE   e.next_time IS NOT NULL
GROUP BY e.DEPARTMENT_ID;

Check the event type and subtype codes in your own dictionary before you run this. I hard-coded numbers here for readability. In production, join to the ZC tables and filter on names. Corrected events add another trap: a transfer that staff fixed afterward may appear twice, one canceled and one active.

Sum of unit hours should equal encounter hours. Run that check on a sample. If it fails, an ADT event is missing or duplicated, and the cause is usually a canceled-event filter or a unit with no recorded arrival.

Role Checklists for LOS Reporting

Common Clarity LOS Mistakes

When I Would Use Each LOS Approach

For quality benchmarking, I use midnight census days on inpatient encounters and compare to GMLOS, because that mirrors how CMS counts patient days. For finance, I use account grain and the payer’s rule, and I let revenue cycle sign off on the definition.

For throughput and staffing, I use elapsed minutes and report the median. Unit-level questions go to the ADT table. I avoid DATEDIFF on hours for anything under a day, because boundary counting distorts short stays.

I would not build an LOS dashboard in Clarity when a governed model already exists in Caboodle or the enterprise warehouse. Validate the model against a Clarity query, then trust the model.

Edge Cases That Break Clean LOS Logic

A Validation Pack for LOS Reports

-- Check 5: discharge-hour distribution
SELECT  DATEPART(hour, h.HOSP_DISCH_TIME) AS discharge_hour, COUNT(*) AS discharges
FROM    PAT_ENC_HSP h
WHERE   h.HOSP_DISCH_TIME >= '2026-09-01' AND h.HOSP_DISCH_TIME < '2026-10-01'
GROUP BY DATEPART(hour, h.HOSP_DISCH_TIME)
ORDER BY discharge_hour;

This query takes seconds and has found more LOS defects for me than any formula review. A spike at hour 0 usually means a default time was entered instead of a real one.

Practice these patterns on safe data in the SQL Analyst Challenge Room before you run them against production extracts.

PHI and LOS Queries

LOS alone is not identifying, but an encounter ID, dates, and a department can identify a patient under HIPAA. Aggregate in SQL and avoid exporting encounter-level rows for a spot check. The HIPAA-safe Clarity SQL article covers access requests, audit logging, and extract handling.

Questions Analysts Ask About Clarity LOS

Which table holds admit and discharge times?

PAT_ENC_HSP holds hospital admission and discharge times at most sites. CLARITY_ADT holds the event history behind them.

Should LOS include the discharge day?

Patient-day conventions exclude the discharge day. Calendar-day DATEDIFF follows that rule. If your sponsor wants the discharge day counted, add one and name the column.

Why does my LOS differ from the Epic Reporting Workbench?

The Workbench report may use a different counting rule, class filter, or cohort date. Compare definitions first, then compare rows.

Can I use DATEDIFF in Oracle?

No. Oracle subtracts dates directly and returns fractional days. Use TRUNC(d) - TRUNC(a) for calendar days.

Run This Before Your Next LOS Report Ships

Take one published LOS number and write down its definition in one line: counting rule, grain, class, same-day rule, and end point for open stays. If you cannot fill every part, the number is not ready. Then run the four boundary cases through the query. That hour of work prevents the Thursday-morning argument.

Sources and Further Reading

• Microsoft Learn: DATEDIFF (Transact-SQL). Documents boundary-counting behavior.
• CMS: MS-DRG classifications and software. Source for MS-DRG geometric mean length of stay in the IPPS tables.
• Epic Clarity Data Dictionary on Epic UserWeb (requires an Epic customer login). Authority for column names in your version.

Table and column names follow standard Clarity. Placeholder objects are marked in the code. Confirm every name and category code in your own environment before you deploy.