Capture last 24 hours of data

Transact-SQL
1

Running a SELECT statement against multiple tables and I need to filter the data down to the last 24 hours. The column i am running the date filter against is called service_date and the date format it uses is YYYYMMDD. I have tried many options and either get a syntax error, invalid date parameter error, or it runs and doesn't really limit it to the last 24hours. Here is my statement with no filter on service date:

SELECT provider_mstr.description, location_mstr.location_name, person.first_name, person.last_name,
person.date_of_birth, patient_procedure.service_item_id, patient_procedure.service_item_desc,
patient_procedure.service_date, patient_procedure.diagnosis_code_id_1, payer_mstr.payer_name,
patient_encounter.enc_timestamp
FROM patient_procedure INNER JOIN person
ON person.person_id = patient_procedure.person_id
INNER JOIN payer_mstr
ON patient_procedure.payer_id = payer_mstr.payer_id
INNER JOIN provider_mstr
ON patient_procedure.provider_id = provider_mstr.provider_id
INNER JOIN location_mstr
ON patient_procedure.location_id = location_mstr.location_id
INNER JOIN patient_encounter
ON patient_procedure.enc_id = patient_encounter.enc_id
WHERE payer_name LIKE '%Medicare%' OR payer_name LIKE '%Medicaid%' AND service_date ____________
ORDER BY provider_mstr.description

I have tried the following and failed:

service_date >= DATEADD(day, DATEDIFF(day, 1, CURRENT_TIMESTAMP), 0)

service_date <= CONVERT (date, getdate())

2

If the column you are comparing against - service_date - has data stored in YYYYMMDD format, then it is likely using a character format. There is not enough information in that column to determine whether it was entered more or less than 24 hours ago. For example, as I am writing this it is 10:59 AM on 8/22/2018. If you see 20180821 in that column, all you can conclude is that it was data for yesterday. You cannot tell whether it is for 8:00 AM or 3:00 PM. If it is the former, that should not be in your results, but if it is the latter, it should be.

If you are able to, you should make the data type of the column DATETIME. That would allow you to store the date and time (going forward). Then you could query using:
service_date > dateadd(day, -1, getdate())
For existing data, the time portion of service_date is lost unless you can go to the original data source and reimport it to include time portion as well.

1 Like
3

Thank you! I am too new to SQL writing to have known that but I assumed because I read the standard date formats for SQl and this wasn't meeting that format.
I will probably have to rope in others to convert the column, if possible, to make sure we don't lose any historical data.

thanks again