Quick datepart/date format query.

[MRDM]

Hi, This might be fairly obvious but formats have always been my downfall and I'm up against the clock or I'd try experiment. I'm doing this in an expression on an Extract Transformation in DI Studio.

CASE WHEN DOB  ^= .
THEN input(cats(year(datepart(DOB)),month(datepart(DOB))),6.)
ELSE . END

Gives me 20055, I'd like 200505 (dates like 200512 are already fine). The output is a straight numeric rather than a date so I didn't think it would be an issue, from a quick google I found using z as the format so tried z6. but that didn't work.

Any ideas? Thanks

[Kurt_Bremser]

Why do you want a mostly unusable number, instead of a date with a proper format?

intnx('month',datepart(dob),0,'b') as newvar format=yymmn6.

All raw date values will be aligned to the first of the month, and missing values will automatically result in missing values.

[MRDM]

It's been exported to a delimited file so won't hold the formats anyway, if it was staying in a SAS/SQL table I agree, all I need is a string with the leading zero, I have 20055 already, I just need it to be 200505. I'm not sure that code will drop into the express on an extract and would need to be user written code instead? I've not really got time to start making changes to the layout of the job and more transformations, I was hoping to just edit my current expression slightly. Missing dates are already ignored as I exclude null dates in the first part.

Also if you think that's useless, they also want YEARDAY e.g. 200525  🤣

I tried:

CASE WHEN DOB  ^= .
THEN input(cats(year(datepart(DOB)),intnx('month',datepart(DOB),0,'b')),6.)
ELSE . END

20-10-1983 became 198386?

[Kurt_Bremser]

If you need the string, use

put(datepart(dob),yymmn6.)

For the Julian date (day in a year), use the JULIAN format

[Kurt_Bremser]

PS the PUT statement (used when writing to a text file) will honor the assigned format.

[MRDM]

Thanks, I got it working with the below, I had to change it to go Numeric to Chararter as Numeric to Numeric didn't work as on MONTHDAY it was still dropping leading zeroes (not an issue with YEARMONTH).

CASE WHEN DOB ^= . 
THEN cats(put(year(datepart(DOB)),z4.),put(month(datepart(DOB)),z2.))
ELSE "" END

I tried

put(datepart(dob),yymmn6.)

It got upset I think because it was going datetime to a straight numeric.