Detecting a date within a range? 1

[jisoo23]

Hello all!

I'm trying to figure out a funny problem here and hopefully someone can give me a suggestion on how to go about it. Basically I have an incoming date, for example "01/27/1993" (time is negligible). The situation is I'm trying to come up with logic to determine if the date lies within a range of dates. If it is, then it sends a '1' to the following filter transformation to mark for update, otherwise '0'. Here are the details:

Date range is the 6th of a month(for example, 01/06/2005) to the 6th of the next month (02/06/2005), non-inclusive. The current (and only relevant) date range is determined by the current date...for instance today's date is 01/27/2005. Hence the current date range is 01/06/2005 to 02/06/2005.

I've only figured out part of the logic, the "date_diff" function only looks at one aspect of dates, like months or days...not the whole date =/ My code only considers days, I'm trying to figure out the months and years part but can't seem to wrap my mind around it. Any help is appreciated!

TRAIN OF THOUGHT:
The incoming date should be checked to see if it's before or after the 6th of the current month. If it's before, it will set the beginning of the range as the 6th of the previous month and the end of the range as the 6th of the current month. Otherwise it will set the 6th of the current month as the beginning of the range and the 6th of the next month as the end of the range. Then there must be a check to see if the incoming date lies within the range that has been set. If yes set variable to '1', '0' otherwise.

(I may be explaining or making this way more complicated than it probably is. So I hope everyone will bear with me =) )

 

[jisoo23]

I guess I could make this an easier question. Is there a more expansive function than DATE_DIFF? DATE_DIFF only allows you to take into account a single aspect of the date (month, day, or year, etc). But I need a function that finds the difference in dates all the way through. Does anyone know of a such a thing?

Thanks,
Jisoo23

 

[jisoo23]

Sudden thought: can date/time datatypes be used without incident in the conditional of an IIF statement? For example:

IIF( current_date > another_date, 1, 0)

Thanks,
Jisoo23

 

[blom0344]

jisoo,

Never tested the expression you mention as such. What will ALWAYS work is the following:

TO_INTEGER(TO_CHAR(CURRENT_DATE,'J'))

This converts the date to an integer julian format that allows all kind of arithmatical actions

Ties Blom
Information analyst