Date Formatting - YYYYWW

Version 2

    I recently had a requirement to create a field in the format YYYYWW from a date, here is how I did it, in case its useful to someone else...

     

    NOTE: PLEASE SEE RODY's MESSAGE DIRECTLY BELOW THIS POST - THERE IS A BETTER WAY

     

     

    INPUT (a date) - 01/01/2014

    OUTPUT - 201401

    IF LEN(STR(DATEPART('week',[DATE],'monday'))) = 1 THEN RIGHT(STR(DATEPART('year',[DATE])),4)+'0'+STR(DATEPART('week',[DATE],'monday'))

    ELSE RIGHT(STR(DATEPART('year',[DATE])),4)+STR(DATEPART('week',[DATE],'monday')) END

     

     

    The below is exactly the same as above, except it returns a 2 digit year, instead of 4 digits.

    INPUT (a date) - 01/01/2014

    OUTPUT - 1401

    IF LEN(STR(DATEPART('week',[DATE],'monday'))) = 1 THEN RIGHT(STR(DATEPART('year',[DATE])),2)+'0'+STR(DATEPART('week',[DATE],'monday'))

    ELSE RIGHT(STR(DATEPART('year',[DATE])),2)+STR(DATEPART('week',[DATE],'monday')) END

     

    The reason for the IF statement -

    Weeks 1-9 return a single digit, without the IF logic/ test the output would be 151, 152 etc.

    Which means it may sort incorrectly and out of intended order, and it maybe confusing to end users.

    The IF statement is there to deal with the single digit weeks and ensure sorting and formatting work as intended.

     

    Extensions

    • With a space separator

         INPUT (a date) - 01/01/2014

         OUTPUT - 2014 01

         IF LEN(STR(DATEPART('week',[Order Date],'monday'))) = 1 THEN RIGHT(STR(DATEPART('year',[Order Date])),4)+' '+'0'+STR(DATEPART('week',[Order Date],'monday'))

         ELSE RIGHT(STR(DATEPART('year',[Order Date])),4)+' '+STR(DATEPART('week',[Order Date],'monday')) END

     

    • With a dash separator

         INPUT (a date) - 01/01/2014

         OUTPUT - 2014-01

         IF LEN(STR(DATEPART('week',[Order Date],'monday'))) = 1 THEN RIGHT(STR(DATEPART('year',[Order Date])),4)+'-'+'0'+STR(DATEPART('week',[Order Date],'monday'))

         ELSE RIGHT(STR(DATEPART('year',[Order Date])),4)+'-'+STR(DATEPART('week',[Order Date],'monday')) END

     

    Screenshot

    Cheers

    Mark