I'm trying to get a week number from "EndDate" column (nvarchar data type). Convert function works fine below.
SELECT
EndDate,
CONVERT(DATE,EndDate,113) as "Date",
Now, I would like to extract a week number. What would be the best way to do it? I tried datepart() but struggling with incorporating into my convert function.
To extract the weeknumber from a date you can use the datename or datepart tfunction
select datename(week, '20220408'),
datename(iso_week, '20220408'),
datepart(week, '20220408'),
datepart(iso_week, '20220408')
returns
COLUMN1 | COLUMN2 | COLUMN6 | COLUMN4 |
---|---|---|---|
15 | 14 | 15 | 14 |
As for using it in your convert function, why would you want to ?
The function works the same on date and datetime, so just use it like this
SELECT EndDate,
CONVERT(DATE, EndDate, 113) as "Date",
datepart(week, EndDate)
or if you really want to
datepart(week, convert(date, EndDate, 113))