Format Date as a Week Number in a BAQ?

I’m bringing in JobHeader_ JobCompletionDate into a query in e10 but require to format it as a year/week number.
i.e. 2020-01-13 would return as 2003.
Could anyone advise how I can achieve this please?
Thanks in advance.

Most T-SQL commands are available to you in a calculated field even if not displayed in the functions panel. Try using DATEPART.

1 Like

HI Mark
Thanks for the help. I cant seem to get DATEPART to work in the calculated field?

Works fine here.


Are you using “Week” as your field name? That won’t work. Choosing an Integer for the return? Can you elaborate what “can’t seem to get DATEPART to work” means?

1 Like
(DATEPART(yr, your_field)-2000)*100 + DATEPART(wk, your_field)

Only valid for dates from 1/1/2000 to 12/31/2099.

Thank you both - now sorted.

1 Like