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",

Output from the above code

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.

CodePudding user response：

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

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))