| Author |
Message |
eckler78
Newbie
Joined: 07 Nov 2017
Online Status: Offline
Posts: 10
|
   Topic: Convert Date to YYWW
Posted: 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.
|
 IP Logged |
|
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 10 Apr 2018 at 7:38am |
|
maybe this?
totext(datefield,'yy') + totext(datepart('ww',datefield))
|
|
IP Logged |
|
eckler78
Newbie
Joined: 07 Nov 2017
Online Status: Offline
Posts: 10
|
Posted: 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
|
|
IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 10 Apr 2018 at 10:38am |
|
lower case 'yy' not upper case 'YY'
|
|
IP Logged |
|
eckler78
Newbie
Joined: 07 Nov 2017
Online Status: Offline
Posts: 10
|
Posted: 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}))
|
|
IP Logged |
|
eckler78
Newbie
Joined: 07 Nov 2017
Online Status: Offline
Posts: 10
|
Posted: 11 Apr 2018 at 3:48am |
|
Or in the case of an early week 8 to 08?
|
|
IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 11 Apr 2018 at 3:57am |
|
totext({V_LMINVTRY_MANUFACTURED.PROD_DATE},'yy') + totext(datepart('ww',{V_LMINVTRY_MANUFACTURED.PROD_DATE}),"00",0)
|
|
IP Logged |
|
eckler78
Newbie
Joined: 07 Nov 2017
Online Status: Offline
Posts: 10
|
Posted: 11 Apr 2018 at 6:44am |
|
Worked great, thanks Dan!
|
|
IP Logged |
|
|
|
