Help with Date logic

[pdntsap]

Hello,

We have two date fields, D1and D2, representing start and end dates. According to business rules, we need to increase the year of the dates by one but keep the same day and month. If the year increment results in an invalid date, we make the date field null or store any default value.

For example, let us assume D1 is 02/29/12. Now incrementing the year by one makes D1 as 02/29/13 which is invalid and hence we need to make the date to be null (or default value). What is the best method of handling this logic?

Thanks.

[Kryt0n]

As 29 Feb is the only time you would get an invalid date, just check for that.

However, what's wrong with adding 1 year to a date? Just sounds a strange decision to me

[qt_ky]

There are many date functions to choose from in a parallel job...

See the Parallel Job Developer's Guide Chapter 2. Designing parallel jobs. Look under the "Date and time formats" section for all the possible date and time format tags available. See Appendix B. Parallel Transform functions for date and time functions to choose from.

[ray.wurlod]

DateFromDaysSince() should be the function you need. Offset is 365, or 366 if you're in a leap year.

[pdntsap]

Thanks for the replies.

I used the DateFromDaysSince() function and made the offset as 366. This works perfect for dates less than 02/29/12 but for dates greater than and equal to 02/29/12 the offset should be 365. One option would be checking each date to be less or greater than 02/29/12 but I believe there might be better options than this. Any help is greatlly appreciated.
Also, how can the logic be made more generic and not just for year 2012?

[qt_ky]

Another option is the DateOffsetByComponents() function. It takes 4 arguments: your date column, year offset, month offset, day offset. Offsets can be positive or negative. Offset a valid Feb 29 date by plus or minus one year will produce a valid result with date of March 1. It's as if your input date were actually March 1. No special leap year handling required, unless you must detect Feb 29 as input dates, then you can add an If Then Else to handle it.

Code: Select all

DateOffsetByComponents(lnk.D1, 1, 0, 0)

[kandyshandy]

got this one from wiki.. may be useful to know.

How to find if a year is a leap or common?

Not evenly divisible by 100 but evenly divisible by 4.
or
Evenly divisible by 100 and evenly divisible by 400.



Any other logic available?

[qt_ky]

Any other logic available?

Sure, just use the DaysInYear() parallel function on any give date.

(it's in the doc I mentioned above)

[kandyshandy]

Eric, good one.. i was asking about any other mathematical way

[chulett]

That is the long established "mathematical way".

[Kryt0n]

I would question if that logic is even true... after all, the year 2000 was a leap year and is divisible by 100... or am I missing something?

[ray.wurlod]

kandyshandy left out one "not".
1900 was not a leap year.
2000 was a leap year.
2100 will not be a leap year.
2400 will be a leap year.

[kandyshandy]

ah.. Should have phrased it differently.

Kryt0n, 2000 is evenly divisible by 100 and 400, & so it is a leap year.

My other condition is "Not evenly divisible by 100 but evenly divisible by 4". This is applicable for years that are not centenaries..

If none of the above conditions is satisfied, then it is a common year

[qt_ky]

Next you can determine leap seconds...

[kandyshandy]

fainted