Using CONVERT and extracting a week number

Question

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.

Answer 1

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