Subtract TIME from DATETIME

[Big Pat]

I'm sure this is simple but my brain is mushy this morning!

I have two datetime fields ADMISSION and DISCHARGE with values such as 03/02/2008 15:00. How do I convert these to dates only? I don't want to just *format* them as dates, which I can do. In this example I would want to convert that date to 03/02/2008 (or 03/02/2008 00:00)

It's an idiosyncrasy of the way the NHS calculates hospital length of stay. Our previous IT system had dates and times in separate fields so I could just ignore the time field and work out length of stay based on dates only. However, our new system returns a combined datetime.

Patient A 03/02/2008 00:15 to 03/02/2008 23:45 = 0 days
but
Patient B 03/02/2008 23:45 to 04/02/2008 00:15 = 1 day

Basically, you count a day if there's a date change, even if that was only half an hour (or even a few minutes) either side of midnight, but you DON'T count a day if there was no date change, even if that was almost 24 hours.

So formatting them as dd/mm/yyyy wont work ,as I will still have partial days when I subtract one from the other and INT([DISCHARGE]-[ADMISSION]) wont always work either as it would calculate 0 for patient B.

Help!

 

[stopher]

So formatting them as dd/mm/yyyy wont work ,as I will still have partial days when I subtract one from the other and INT([DISCHARGE]-[ADMISSION]) wont always work either as it would calculate 0 for patient B.

There are many ways but this is as good as any I guess..
INT([DISCHARGE])-INT([ADMISSION])

Stopher

 

[Big Pat]

Thanks Stopher,

Just make each one an integer first, THEN subtract. I should've seen that!

Elegant and simple. Well done!