BSFN to convert date to number and format YYYYMMDD

[bthompson]

I have a table conversion for our Pension carrier that is requiring all dates be format as YYYYMMDD and numeric data type. I have tired several BSFN, converting date to string then string to numeric. I've tried just making the data type a string and converting date to string (B9800460) and using Date Format of EMD. Before I start parsing this data just wanted to check to see if anyone knew of another BSFN that would solve my issue.

Any and all input is greatly appreciated; this is the last issue before I can send this file to carrier for testing.

 

[BOster]

Can't think of one off hand but I am sure there is something that wraps the C api call FormatDate. If not you could roll your own:

JCHAR szdate[9]={0};

FormatDate(szdate, lpDS->jdDate, J("EOA"));
ParseNumericString(&lpDS->mnYYYYMMDD, szdate);

 

[peterbruce]

Beth,

Here are two options: A NER BSFN and Event Rule code"

NER BSFN

N0701500, Convert Date To String Based On Format Code

Input: Date and Format Code

Format
Code Output String Format
1 MMDDYYYY
2 YYYYMMDD
3 DDMMYYYY
4 MMDDYY
5 YYMMDD
6 DDMMYY

Where:
DD = Day
MM = Month
YY = Year (2-digit)
YYYY = Year (4-digit)

All are left-padded with zeroes


Error
test/dev Code Explanation
blank No Errors
1 Format Code Not Recognized
2 Date is blank (null date)

==============================================================

Event Rule Code

I'm sure I have used this before, but can't test it at the moment because our system is down for the day.

szDateStringYYYYMMDD = concat(concat(date_year([VA rpt_jdDate]),rpad(date_month([VA rpt_jdDate]),'0',2)),rpad(date_day([VA rpt_jdDate]),'0',2))

 

[Adrian_Chimirel]

Great post Peter!
Should we not left pad the month & day (vs rpad)?
Thank you,

 

[peterbruce]

Adrian,

Thanks for the sentiment.

Much to my embarrassment

, you are correct (of course

). Yes the month and day should be left padded (lpad) and not right padded (rpad). I would normally have checked/tested it (and thus picked up my mistake), but our test sytem is not available at the moment. Hopefully it will be this morning sometime.