Convert Date to YYWW

Printed From: Crystal Reports Book — Forum Name: Technical Questions

Posted By: eckler78 — 10 Apr 2018 at 6:06am

Hello All,
I have a customer that needs us to send a date code in the format of YYWW (today 4/10/2018 would translate to: 1815 (2018, week 15)). I cannot figure out how to do this. If anyone else has done this before, I would really appreciate your help! Thank you.

Posted By: DBlank — 10 Apr 2018 at 7:38am

maybe this?
totext(datefield,'yy') + totext(datepart('ww',datefield))

Posted By: eckler78 — 10 Apr 2018 at 9:40am

Thanks Dan. This is really close. The below returns YY43.00 for 10/26/2017. Hoping for 1743

totext({V_LMINVTRY_MANUFACTURED.PROD_DATE},'YY')+ totext(datepart('WW',{V_LMINVTRY_MANUFACTURED.PROD_DATE}))

The year does not populate but the week does although it it is carrying 2 decimal places. I tried tweaking your formula a bit with no luck.

Edited by eckler78 - 10 Apr 2018 at 9:41am

Posted By: DBlank — 10 Apr 2018 at 10:38am

lower case 'yy' not upper case 'YY'

Posted By: eckler78 — 11 Apr 2018 at 3:46am

Thanks Dan. That did it. Now my results are 1743.00. How do I convert the 43.00 to just 43?

totext({V_LMINVTRY_MANUFACTURED.PROD_DATE},'yy')+ totext(datepart('WW',{V_LMINVTRY_MANUFACTURED.PROD_DATE}))

Posted By: eckler78 — 11 Apr 2018 at 3:48am

Or in the case of an early week 8 to 08?

Posted By: DBlank — 11 Apr 2018 at 3:57am

totext({V_LMINVTRY_MANUFACTURED.PROD_DATE},'yy') + totext(datepart('ww',{V_LMINVTRY_MANUFACTURED.PROD_DATE}),"00",0)

Posted By: eckler78 — 11 Apr 2018 at 6:44am

Worked great, thanks Dan!