sql - 'WEEK' function doesn't work when wrapping a CASE statement

Question

Closed. This question is not reproducible or was caused by typos. It is not currently accepting answers. question from:https://stackoverflow.com/questions/65915300/week-function-doesnt-work-when-wrapping-a-case-statement

Answer 1

You should use DATEPART function. The correct syntax is :

SELECT DATEPART (WEEK, YourDate)

Or,

SELECT DATEPART (WW, YourDate)

Or,

SELECT DATEPART (WK, YourDate)

The DATEPART() takes two arguments:

• date_part is the part of a date to be extracted. ( See the valid date parts in the table below).
• input_date is the date from which the date part is extracted.

So your query should be like below : SQL Fiddle

MS SQL Server 2017 Schema Setup:

CREATE TABLE Test(DATEPHYSICAL DATE,DATEEXPECTED DATE)
INSERT INTO Test(DATEPHYSICAL,DATEEXPECTED) VALUES ('1900-01-01','2020-01-01'),('2021-01-01','2020-01-01')

Query 1:

SELECT DATEPART(WEEK,CASE WHEN DATEPHYSICAL = '1900-01-01' THEN DATEEXPECTED ELSE DATEPHYSICAL END) AS TheWeek FROM Test

Results:

| TheWeek |
|---------|
|       1 |
|       1 |