More Date Time Questions

[gkrenton]

I have 2 fields in a table that are
datetime year to second formats
I am trying to do a comparison to the current day to flag a record as 'new' or 'old'
My statement of
CASE when create_date >= current - 2 units day then 1 else 0 end

Gives me all records where the day is different than 'today's day which doesn't get me to where I need to be. So if today is 08/05/2004 it gives me all records where the day field is greater or equal to 03. Which I understand why it's doing that since I'm asking only for a day comparison but how the heck do I do a comparison within a case statement based upon the mm/dd/yyyy excluding the time portion of the datetime field.

I've looked through the FAQ's here, and my growing library of informix sql books & no one seems to really address this subject in a way that seems to fit what I'm doing or maybe I'm too dense to see that they are solving my same problem.

Any hints, help and suggestions would be greatly appreciated.

- Gina

 

[PHV]

Something like this ?
CASE when DATE(create_date) >= TODAY - 2 then 1 else 0 end

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[gkrenton]

That seems to be the ticket. One of these days I'll figure out the date & datetime issues in such a way that I don't have to ask.

Thanks a ton.

- Gina