October 31, 2013, 12:45 PM
TRueConvert or Extract from HYYMDs format
I have a date field that starts out as HYYMDs format, and I want to convert or extract just the Year and/or the Month from the date.
As soon as I use HDATE my report comes up with zero data. If I use the original date field in HYYMDs format it comes up fine.
For example, to extract just the year I tried:
HDATE( J001.CUSTOMERHISTORY.ADDEDON , 'YYMD')
... and set the field as YY format.
If I filter by this newly created field, and 'Fetch all Values from Source' to check it, I get all the years as expected as options for filtering. I can pick one and it doesn't argue with me.
Run the report and zero data appears.
Go back to filtering or displaying the base field in HYYMDs format and it all comes up fine.
I have even tried using DPART, DATECVT, HCNVRT. As soon as I filter or include the new field in my report it comes up with zero data.
Is there an issue with extracting parts of a date that starts out in HYYMDs format?
October 31, 2013, 04:37 PM
WazCan you post a better sample of what yuou are doing ?
The HDATE command should work.
October 31, 2013, 05:10 PM
TRueI don't know what to tell you, but I walked away to carve a pumpkin and when I came back this now works.
The formula I had to use, however, is HPART.
AddedYr/I4 = HPART(J001.CUSTOMERHISTORY.ADDEDON , 'YEAR', 'I4')
Did the same thing for Month, using
AdddedMo/I4 = HPART(J001.CUSTOMERHISTORY.ADDEDON , 'MM', 'I4')
I guess I can concatenate these 2 fields to get Year/Mo together, however it would be nice to actually know how/why HDATE eliminates all data from my report...