sql-serverdatetype-conversionnvarchardatepart

Using CONVERT and extracting a week number


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.


Solution

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