Date conversion

[LOUM]

Hello All!

my core data in my report query is in the following formats.

15,062,011
and
Hundred year date

I am trying to filter using a data item with the function _add_days(current_date,-91) but because of the funky dates it is not working.

Any suggestions, tryiing to pull the last 90 days of the records out of my data library.

Regards,
Lou

[MFGF]

So your dates are stored in integer datatype?  Ignoring the commas (which are just formatting) the date you quoted would be 15062011 which equates to 15th June 2011 - is that correct?

If so, to use _add_days() you will need this to be a date-time datatype.  You could engineer this using some simple maths and a make_timestamp() function.  You will need to plug in values for year, month and day to use make_timestamp(), so this is where the maths comes in.  To get the day, divide your integer date by 1000000 and round it down (giving 15).  To get the month, divide by 10000, round down, then use the mod() function to grab the remainder when you divide the remaining 1506 by 100.  To get the year, use the same modulus function but this time to get the remainder when you divide your original value by 10000.

Once you have these three, use them in make_timestamp() to get a proper date, and you can then use this within your _add_days expression.

Good luck!!

MF.

[LOUM]

MFGF thank you for the assistance.

My apologies for being green here.

My first pull to get the Day looks like this in th data item [SQL4].[PJTDAT]/10000

When I apply the logic to pull out the month it brings back what appears to be the correct day, 6.15201, when I then apply the _round([Day],0) the result is 615

What did I do wrong in this instance? If I did not give you enough information please let me know.

[MFGF]

That doesn't look correct.  Surely the first pull - dividing by 1000000 would return the day, so if the original value was 17062011 it would return 17.06211?  If you used a floor or round function to bring it to an integer, it should return 17.  Or am I misunderstanding somewhere?

MF.

[LOUM]

I just updated the post, I type month when I meant day. The month calculation came out perfect with a 6 after rounding it.

[MFGF]

What does the value look like if you don't round it? Does it have the decimal place in the expected position?

[LOUM]

Here is a specifc date and look.

November 2, 2010

Original date:  11,022,010

Data Item = [SQL4].[PJTDAT]/10000
Day = 11,102.201
Rnd= 1,102

Data Item = [SQL4].[PJTDAT]/1000000
Mnth = 11.02201
Rnd = 11

Data Item = [SQL4].[PJTDAT]/100
Year = 11022.1
Rnd = 110,220

Did I miss the boat completely?

Thank you,
Lou

[MFGF]

Ah - so your dates are in MMDDYYYY format not DDMMYYYY.  I see! (sorry - I'm a Brit so the American date format is a little alien to me! :) )

Month is easy, and you have got that sorted ok.

The next bit is day...

Start by dividing by 10000 and round down to give 1102 (as you have done) then use the mod() function to return the remainder of dividing this result by 100 ie mod([your rounded number],100)
This should return a result of 2 because 100 divides into 1102 eleven times leaving a remainder of 2.

Finally the year...
Do not divide by anything initially - just use the mod() function to return the remainder of dividing by 10000 ie mod([SQL4].[PJTDAT],10000)

Now you have the month, day and year, you can use these in a _make_timestamp function.

Regards,

MF.

[Lynn]

Sounds like DB2 to me!! Why dates are stored as integers remains a mystery I've yet to solve.

I have found that converting the integer data item to a date in order to perform filtering compared to current date means I don't hit indexes and performance is, well, umm, <expletive deleted>.

Therefore, I transform the current date to an integer as the basis for the filter and compare that to the unadulterated date integer in the database. In my case, the integer dates are YYYYMMDD. The below code works in that scenario, and the example I've posted retrieves records with a date that is less than or equal to 15 days from today to identify events coming up.

You'd need to tweak for MMDDYYYY format by swapping the year, month, and day accordingly.

Code Select Expand


[Date Number] <=
extract(year, _add_days(current_date, 15))*10000 +
extract(month, _add_days(current_date, 15))*100 +
extract(day, _add_days(current_date, 15))


[Lynn]

Oh, and when I do want to convert the integer to a date, I approach it as below (again, based on YYYYMMDD format for the integer in DB2). I do this in the FM model for every integer date field that I include, exposing both the integer query item and the transformed date query item.

Can't say this is better or worse or indifferent....it's just what I do, FWIW  :P

Code Select Expand


date(
substr(char( [Database Layer].[TABLE].[INTDATE] ),1,4) || '-' ||
substr(char( [Database Layer].[TABLE].[INTDATE] ),5,2) || '-' ||
substr(char( [Database Layer].[TABLE].[INTDATE] ),7,2)
)


[LOUM]

MF,

[Date stamp] looks good. Date appears as November 02, 2010.

I then apply the logic to a new data item as follows

IF ([Date Stamp] >= _add_days(current_date, -91)) then ([PJTQTY]) else (0)

The result should be 30 but it coming back with a zero. The 30 were issued on 5/30/2011, so within the last 90 days.

[LOUM]

Lynn,

Thank you for the review and the reply. I have added this code to a data items being in mind my format is MMDDYYYY.

date(
substr(char([SQL4].[PJTDAT],1,2) || '-' ||
substr(char([SQL4].[PJTDAT],3,2) || '-' ||
substr(char([SQL4].[PJTDAT],5,4)
)

I get a parsing error on this?

Lou

[Lynn]

Is your database DB2? The link below has some information on dealing with dates and times in DB2.

It looks to me like you're missing a close parenthesis on each of the substring lines. You need to close both the char() function and the substr() function.

I think the date function is expecting YYYY-MM-DD format, so maybe re-ordering is also called for? Can't say for certain, but maybe the below is what you need. 

Code Select Expand


date(
substr(char([SQL4].[PJTDAT],5,4)) || '-' ||
substr(char([SQL4].[PJTDAT],1,2)) || '-' ||
substr(char([SQL4].[PJTDAT],3,2))
)


http://www.ibm.com/developerworks/data/library/techarticle/0211yip/0211yip3.html

[MFGF]

MF,

[Date stamp] looks good. Date appears as November 02, 2010.

I then apply the logic to a new data item as follows

IF ([Date Stamp] >= _add_days(current_date, -91)) then ([PJTQTY]) else (0)

The result should be 30 but it coming back with a zero. The 30 were issued on 5/30/2011, so within the last 90 days.

Ignoring for a moment the great stuff Lynn has suggested, I'm a little confused by what you're doing here.  Initially you said you were trying to filter, but the expression above looks like something from a calculation.

Surely if this was a filter, the expression would be simply

[Date Stamp] >= _add_days(current_date, -91)

Or am I missing something obvious (highly likely, as anyone who knows me will tell you! :) )

If you are using DB2, then you might want to look at Lynn's solution - she has loads of experience in getting impossibly slow, complex reports to run quickly in that environment, and her advice is always excellent.

MF.

[LOUM]

 ;D

I am writing the filter with the add_days the only way I know at this time, if there is another way of achieving the results using it or filtering on the date please share as you guys have been wonderful!

I am trying to filter (only pull) the last 90 days worth of records by the date field.

Thank you,
Lou

[MFGF]

Did you try the expression I suggested?  Does it give expected results?

Regards,

MF.

[LOUM]

MF,

Thank you for all your help. It works like a champ. My next question as there is always a follow up! What if I wanted to use the date time stamp and hardcode between dates?

[MFGF]

MF,

Thank you for all your help. It works like a champ. My next question as there is always a follow up! What if I wanted to use the date time stamp and hardcode between dates?

Just a slightly different expression...

[Date Stamp] between _add_days(current_date, -91) and _add_days(current_date), -10)

Obviously I made up the second date in the expression, but you can use whatever dates are required.

Regards,

MF.