Sorry - this may be a basic question, but I have been banging my head against this for a week.

I have a database field with the format "dd/mm/yyyy hh:mm:ss" called UpdateTime and referencing max(AuditHistory.ActionedDateTime) in the database.

I am trying to identify the Week / ISO Week from the date part of this field only using the dataset in ReportBuilder3.

I am trying to achieve an integer entry in a column called "WeekNo" giving me the week of the year that a transaction was made so I can use this for grouping results by year | by week number on a report for senior management.

I have tried many combinations of: ,DATEPART(WEEK,DAY(max(AuditHistory.ActionedDateTime)) AS WeekNo and ,DATEPART(WEEK,MONTH(max(AuditHistory.ActionedDateTime)) AS WeekNo.

If I use a static date, e.g. , DATEPART(WEEK,DAY('1900-01-20')) AS WeekNo, it returns perfectly as "4" but I cannot for the life of me get the datepart format correct to identify the week from the format of the field.

I believe my issue is getting SQL to accept that the field is "dd/mm/yyyy hh:mm:ss" and work out the week from the date element.

Before I go mad - I thought I'd ask if there is a quick way to achieve this.

CodePudding user response：

The DATEPART function expects a date / datetime / datetime2 value. You are passing in an integer representing the day or month number.

Assuming you're storing your dates correctly, you just need to pass in the date value directly:

DATEPART(WEEK, Max(AuditHistory.ActionedDateTime)) As WeekNo