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 |
与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…