Format Date as a Week Number in a BAQ?

IainA · January 15, 2020, 9:06pm

Hi
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.
Iain

Mark_Wonsil · January 15, 2020, 9:13pm

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

IainA · January 15, 2020, 10:16pm

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

Mark_Wonsil · January 15, 2020, 10:29pm

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?

ckrusen · January 16, 2020, 12:34am

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

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

IainA · January 16, 2020, 10:14am

Thank you both - now sorted.