SQL + SSRS Tip: Avoid Trouble with DATETIME, DATETIME2 in SSRS Reports

In the MEDITECH report writing world, an old, old “gotcha” for database programmers is to write a report query that selects by Discharge Date, and then when the end-users run the report, they eventually realize that they are only getting patients discharged on the last date of the report range at exactly midnight.

Why? Because data type of the discharge date field…

MEDITECH 6.x: 
RegAcct_RegistrationTypes.RegistrationTypeDischargeDateTime

MAGIC:  AdmDischarge.DischargeDateTime

Client Server:  AdmDischargeInfo.DischargeDateTime

…are all DATETIME, which includes hours/minutes/seconds in the date definition. In the Data Repository (DR), this field combines what is two fields in NPR. If you’re transitioning from NPR to the DR, this is something that will trip you up if you’re not checking the data.

If you just use a date, then the HH/MM/SS component of the DATETIME defaults to 00:00:00 and that means your comparison gets only discharges at exactly midnight (at the start of the day). You want the end of the date range to default to 11:59:59pm, but the computer has no way of knowing you want that behavior, unless we take an extra step.

This crops up again with SSRS because of a mismatch between these discharge date fields and the Date parameter type, which also defaults to 00:00:00 (particularly if you are using the Date Picker). Note that the Calendar Picker only lets you pick dates, not dates and times. (You can pick the date, then go into the Parameter field and manually enter a valid time, but that’s not terribly intuitive, and it’s prone to error).

If we think about our end users, and the data they are trying to get at, we realize that most of the time, they do not need to enter a time when running the report. Most of the time, they want just a range of days, and they want to get the entire last day (and the entire first day, for that matter).

So, we let them just use the default date picker and have it get us a plain old date (MM/DD/YYYY) without the time component.

Then, inside our query (which you’ve got in a stored procedure, right? Of course, you do!), we handle the missing time component one of two ways:

Approach One – Bump the last day to the end of the last day

Define the @dThruDate parameter as DATETIME in your stored procedure parameter block, and then, before you use the parameter:

 -- Reset ending date to end-of-day
SET @dThruDate = DATEADD(ms,-3,DATEADD(dd,1,DATEADD(dd,0,DATEDIFF(dd,0,@dThruDate)))) ;

This is useful in the case where you’ve got a query or stored procedure that is already set up to accept DATETIME parameters, and you don’t want to mess with that. The combinations of DATEADD/DATEDIFF do the following:

1. Reset @dThruDate to 00:00:00 of the date, just to make sure.
2. Add a day to the revised date.
3. Then, subtracts 3 ms (milliseconds) from that new date to back up just a smidge into the end of the previous day.

Which all works just fine.

Approach Two – Dump the Hours/Minutes/Seconds Entirely

But if you’re starting the query and report fresh, then you can just use the DATE datatype for your from/thru parameters and only worry about the MM/DD/YYYY component when you are comparing:

WHERE DischargeDateTime >= @dFromDate

      AND

      DischargeDateTime < DATEADD(day,1,@dThruDate)

This particular structure also avoids some potential issues with the BETWEEN/AND predicate, which can also be tripped up by DATE vs DATETIME.

Our Report Writing team can help you fix reports, create new ones, make old ones faster, and much more. Simply reach out to your Iatric Systems Account Executive or our NPR report writing team at reportwriting@iatric.com to discuss how we can help support your team!