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))
DATENAME instead of DATEPART (which I would suggest is more appropriate here). Also, WEEK and ISO_WEEK don't provide the same behaviour, even if you are American.
WEEK the week number is based on the DATEFIRST setting. So, for example, 20220102 is week 2 for those with Sunday as the first day of the week (DATEFIRST is set to 7), where as those with it set to Monday (1), it would be week 1. Conversely, for ISO_WEEK is would be week 52, as it's not the first week in the year with a Thursday in it, and ISO Weeks run Monday - Sunday. See ISO week date.WEEK is never carried over to the following year, so any dates prior to the "first" day of the week in the new year would be week 1; if the first day of the week is also 01 Jan, it would obviously be week 1. ISO_WEEKs, however, carry over until the 1st week of the next year starts; which is the first week (Monday-Sunday) that contains a Thursday. So as 20220102 was a Sunday, it's the 2nd week for Americans (as a new week has started), the 1st week for Europeans, and the 52nd week (of 2021) in ISO terms.