Re: RPGLESQL group by year, week# descending from a 9/0 field

[Booth Martin <booth@xxxxxxxxxxxx>]

Thank you!

On 10/9/2019 11:07 PM, Birgitta Hauser wrote:
> Use embedded SQL! ... but you first need to convert your numeric date into a real date and then use the WEEK_ISO (week according the ISO guide lines, i.e. week starts with Monday and the majority of the days in week 1 is from the new year) or WEEK (January first is always in the first week of a year).
>
> Determining the week according the ISO guidelines with SQL from a 9 digit numeric date
> Week_ISO(Right(Digits(YourNumDate) concat '000000', 14))
>
> When Using the VarChar_Format Function you can even determine the year:
> The following expression will return the Year/Week. If December 29 is in the first week of the next year, you'll get NextYear/01.
> VarChar_Format(Timestamp(Right(Digits(YourNumDate) concat '000000', 14)), 'IYYY/IW')
>
> In this way it is possible to group and sort your dates.
>
> Mit freundlichen Grüßen / Best regards
>
> Birgitta Hauser