How to Extract Week Number in SQL

How to extract week number in sql

After converting your varchar2 date to a true date datatype, then convert back to varchar2 with the desired mask:

to_char(to_date('01/02/2012','MM/DD/YYYY'),'WW')

If you want the week number in a number datatype, you can wrap the statement in to_number():

to_number(to_char(to_date('01/02/2012','MM/DD/YYYY'),'WW'))

However, you have several week number options to consider:

WW  Week of year (1-53) where week 1 starts on the first day of the year and continues to the seventh day of the year.
W   Week of month (1-5) where week 1 starts on the first day of the month and ends on the seventh.
IW  Week of year (1-52 or 1-53) based on the ISO standard.

Get week number in year from date

You probably need to specify the first day of your week with set datefirst:

set datefirst 1;
select datepart(week, '2017-02-01');

returns 6

---

Depending on the default language, your datefirst might be set to 7.

set datefirst 7;
select datepart(week, '2017-02-01');

returns 5

rextester demo: http://rextester.com/KEPB52852

How to get date from week number and day in sql?

This following sample script might help you. Hope all necessary values are available in your database and you have pass them to the function accordingly-

SELECT STR_TO_DATE('2013 10 Tuesday', '%X %V %W');

--2013 is the year value
--10 is the week number
--Tuesday is the day name

If you have all three values available in your table and run the STR_TO_DATE function providing appropriate values - this will return you a date like - "2013-03-12".

You can check the below script-

SELECT 
STR_TO_DATE(concat('2019',' ', WeekID,' ', DayofWeek), '%X %V %W') 
FROM (
    SELECT 1 RecordID, 'text1' Record, 43 WeekID,'mon' DayofWeek UNION ALL
    SELECT 2,'text2',43,'tue' UNION ALL
    SELECT 3,'text3',44,'wed'
)A;

Your final query should be as below-

SELECT 
STR_TO_DATE(concat('2019',' ', WeekID,' ', DayofWeek), '%X %V %W') 
FROM your_table_name A;

Note: Year 2019 is fixed as this value is not available in your table. If available, you can also use that column dynamically as other columns are used.

How to get week number of the month from the date in sql server 2008

Here are 2 different ways, both are assuming the week starts on monday

If you want weeks to be whole, so they belong to the month in which they start:
So saturday 2012-09-01 and sunday 2012-09-02 is week 4 and monday 2012-09-03 is week 1 use this:

DECLARE @date date = '2012-09-01'
SELECT (day(datediff(d,0,@date)/7*7)-1)/7+1

If your weeks cut on monthchange so saturday 2012-09-01 and sunday 2012-09-02 is week 1 and monday 2012-09-03 is week 2 use this:

DECLARE @date date = '2012-09-01'
SELECT 
  datediff(ww,datediff(d,0,dateadd(m,datediff(m,7,@date),0)
    )/7*7,dateadd(d,-1,@date))+1

I received an email from Gerald. He pointed out a flaw in the second method. This should be fixed now

I received an email from Ben Wilkins. He pointed out a flaw in the first method. This should be fixed now

Get week number from a list of dates in SQL

I usually use the ROW_NUMBER() function to accomplish this:

select 
    Date,
    Time,
    EndDate,
    EndTime, 
    ROW_NUMBER() over (partition by year(EndDate), datepart(weekday, EndDate) order by EndDate) as WeekNumInYear
FROM Test
WHERE 
    (StartDate >= '01.01.2019')
ORDER BY 
    StartDate

How to get the week number, start date and end date of Compelete year in SQL Server?

If you are interested in 2019 only, then the following code will produce exactly what you are looking for:

DECLARE @weekNum INT = 1;

WITH Weeks AS (
    SELECT @weekNum AS WeekNo
    UNION ALL
    SELECT WeekNo + 1 FROM Weeks WHERE WeekNo + 1 <= 53
)
SELECT  WeekNo,
        CASE
          WHEN WeekStartDate < '2019-01-01' THEN CONVERT(DATE, '2019-01-01')
          ELSE CONVERT(DATE, WeekStartDate)
        END AS WeekStartDate,
        CASE
          WHEN WeekEndDate > '2019-12-31' THEN CONVERT(DATE, '2019-12-31')
          ELSE CONVERT(DATE, WeekEndDate)
        END AS WeekEndDate
  FROM  (
        SELECT  WeekNo,
                DATEADD(WEEK, WeekNo - 1, '2018-12-30') AS WeekStartDate,
                DATEADD(WEEK, WeekNo - 1, '2019-01-05') AS WeekEndDate
          FROM  Weeks
        ) a

OUTPUT:

WeekNo  WeekStartDate   WeekEndDate
1       2019-01-01      2019-01-05
2       2019-01-06      2019-01-12
3       2019-01-13      2019-01-19
4       2019-01-20      2019-01-26
5       2019-01-27      2019-02-02
6       2019-02-03      2019-02-09
7       2019-02-10      2019-02-16
8       2019-02-17      2019-02-23
...
51      2019-12-15      2019-12-21
52      2019-12-22      2019-12-28
53      2019-12-29      2019-12-31

Edit following OP comment about variable start and end dates

Following OP's comment about varying start and end dates, I've revisited the code and made it such that is can work between any two dates:

DECLARE @startDate DATE = CONVERT(DATE, '2019-01-01');
DECLARE @endDate DATE = CONVERT(DATE, '2019-12-31');
DECLARE @weekNum INT = 1;

WITH Weeks AS (
    SELECT @weekNum AS WeekNo
    UNION ALL
    SELECT WeekNo + 1 FROM Weeks WHERE WeekNo + 1 <= DATEDIFF(WEEK, @StartDate, @EndDate) + 1
)
SELECT  WeekNo,
        CASE
          WHEN WeekStartDate < @startDate THEN @startDate
          ELSE CONVERT(DATE, WeekStartDate)
        END AS WeekStartDate,
        CASE
          WHEN WeekEndDate > @endDate THEN @endDate
          ELSE CONVERT(DATE, WeekEndDate)
        END AS WeekEndDate
  FROM  (
        SELECT  WeekNo,
                DATEADD(WEEK, WeekNo - 1, OffsetStartDate) AS WeekStartDate,
                DATEADD(WEEK, WeekNo - 1, OffsetEndDate) AS WeekEndDate
          FROM  Weeks
            INNER JOIN  (
                        SELECT  CASE
                                    WHEN DATEPART(WEEKDAY, @startDate) = 1 THEN @startDate
                                    ELSE DATEADD(DAY, 1 - DATEPART(WEEKDAY, @startDate), @startDate)
                                END AS OffsetStartDate,
                                CASE
                                    WHEN DATEPART(WEEKDAY, @startDate) = 1 THEN DATEADD(DAY, 6, @startDate)
                                    ELSE DATEADD(DAY, 7 - DATEPART(WEEKDAY, @startDate), @startDate)
                                END AS OffsetEndDate
                        ) a ON 1 = 1
        ) a

Simply modify @startDate and @endDate to reflect the desired start and end dates. The format of the string is YYYY-MM-DD.

This will output a variable number of weeks between the two dates, starting and ending on the specified date (creating partial weeks as needed). Hopefully, as per the requirement.

---

Related Topics