SQL Convert Week Number to Date (Dd/Mm)

SQL Convert Week Number to Date (dd/MM)

Try this,

declare @wk int  set @wk = 21
declare @yr int  set @yr = 2016

select dateadd (week, @wk-1, dateadd (year, @yr-1900, 0)) - 4 -
       datepart(dw, dateadd (week, @wk-1, dateadd (year, @yr-1900, 0)) - 4) + 1

or try this way

declare @wk int  = 21

select dateadd(week,@wk-1, DATEADD(wk, DATEDIFF(wk,-1,DATEADD(yy, DATEDIFF(yy,0,getdate()), 0)), 0)) 

Convert week number and year to a date in Access-SQL?

This is not so simple, as the ISO years rarely are in sync with calendar years.

But this function will do:

' Returns the date of Monday for the ISO 8601 week of IsoYear and Week.
' Optionally, returns the date of any other weekday of that week.
'
' 2017-05-03. Gustav Brock, Cactus Data ApS, CPH.
'
Public Function DateYearWeek( _
    ByVal IsoWeek As Integer, _
    Optional ByVal IsoYear As Integer, _
    Optional ByVal DayOfWeek As VbDayOfWeek = VbDayOfWeek.vbMonday) _
    As Date
    
    Dim WeekDate    As Date
    Dim ResultDate  As Date
    
    If IsoYear = 0 Then
        IsoYear = Year(Date)
    End If
    
    ' Validate parameters.
    If Not IsWeekday(DayOfWeek) Then
        ' Don't accept invalid values for DayOfWeek.
        Err.Raise DtError.dtInvalidProcedureCallOrArgument
        Exit Function
    End If
    If Not IsWeek(IsoWeek, IsoYear) Then
        ' A valid week number must be passed.
        Err.Raise DtError.dtInvalidProcedureCallOrArgument
        Exit Function
    End If
    
    WeekDate = DateAdd(IntervalSetting(dtWeek), IsoWeek - 1, DateFirstWeekYear(IsoYear))
    ResultDate = DateThisWeekPrimo(WeekDate, DayOfWeek)
    
    DateYearWeek = ResultDate

End Function

However, it uses a series of supporting functions, like:

' Returns the primo date of the week of the date passed.
'
' 2016-01-13. Gustav Brock, Cactus Data ApS, CPH.
'
Public Function DateThisWeekPrimo( _
    ByVal DateThisWeek As Date, _
    Optional ByVal FirstDayOfWeek As VbDayOfWeek = vbSunday) _
    As Date

    Dim Interval    As String
    Dim Number      As Double
    Dim ResultDate  As Date
    
    Number = 0
    Interval = IntervalSetting(DtInterval.dtWeek)
    
    ResultDate = DateIntervalPrimo(Interval, Number, DateThisWeek, FirstDayOfWeek)
    
    DateThisWeekPrimo = ResultDate
    
End Function

and several more - way too much to post here.

So, please refer to my project at GitHub, VBA.Date, for the modules holding the full code.

How to get start date and end date by selecting week number in SQL Server

In addition to a week number, you will need to include a year number:

declare @wk int  set @wk = 40
declare @yr int  set @yr = 2020

select dateadd (week, @wk-1, dateadd (year, @yr-1900, 0)) - 4 -
       datepart(dw, dateadd (week, @wk-1, dateadd (year, @yr-1900, 0)) - 4) + 1 StartDate,
    dateadd (week, @wk-1, dateadd (year, @yr-1900, 0)) - 4 -
       datepart(dw, dateadd (week, @wk-1, dateadd (year, @yr-1900, 0)) - 4) + 6 EndDate

StartDate               EndDate
2020-09-26 00:00:00.000 2020-10-01 00:00:00.000

Slightly modified from this great answer: SQL Convert Week Number to Date (dd/MM)

Week number to Date SQL

Made a couple variables to parse out the year and week. Then using DATEDIFF to calculate the week and then DATEADD to add the weeks to that value and present the date.

DECLARE @InputValue as varchar(6) = '201751';
DECLARE @YearNum as varchar(4) = LEFT(@InputValue,4);
DECLARE @WeekNum as varchar(2) = RIGHT(@InputValue,2);

SELECT DATEADD(wk, DATEDIFF(wk, 6, '1/1/' + @YearNum) + (@WeekNum-1), 6) AS StartOfWeek,
       DATEADD(wk, DATEDIFF(wk, 5, '1/1/' + @YearNum) + (@WeekNum-1), 5) AS EndOfWeek;

Result:

 StartOfWeek                EndOfWeek
 2017-12-17 00:00:00.000    2017-12-23 00:00:00.000

Note that this will only work for your 6 character string input where the first 4 characters are the year and the last two characters are the month.

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.

How to convert week number to date range in Oracle?

"week no:1 date range is 01-01-2017 to 08-01-2017"

No it isn't. You're confusing 'IW' (which runs MON - SUN) with 'WW' which runs from the first day of the year:

SQL> with dts as (
  2       select date '2017-01-01' + (level-1) as dt
  3       from dual
  4       connect by level <= 8
  5  )
  6  select dt
  7         , to_char(dt, 'DY') as dy_dt
  8         , to_char(dt, 'IW') as iw_dt
  9         , to_char(dt, 'WW') as ww_dt
 10  from dts
 11  order by 1;

DT        DY_DT        IW WW
--------- ------------ -- --
01-JAN-17 SUN          52 01
02-JAN-17 MON          01 01
03-JAN-17 TUE          01 01
04-JAN-17 WED          01 01
05-JAN-17 THU          01 01
06-JAN-17 FRI          01 01
07-JAN-17 SAT          01 01
08-JAN-17 SUN          01 02

8 rows selected.

SQL> 

However, it's easy enough to generate a range for the the IW week number. You need to multiple the IW number by 7 which you can convert to a date with the day of year mask. Then you can use next_day() function to get the previous Monday and the next Sunday relative to that date:

SQL> with tgt as (
  2      select to_date( &iw *7, 'DDD') as dt from dual
  3      )
  4  select next_day(dt-8, 'mon') as start_date
  5         , next_day(dt, 'sun') as end_date
  6* from tgt;
Enter value for iw: 23
old   2:     select to_date( &iw *7, 'DDD') as dt from dual
new   2:     select to_date( 23 *7, 'DDD') as dt from dual

START_DAT END_DATE
--------- ---------
05-JUN-17 11-JUN-17

SQL> 

Obvious this solution uses my NLS Settings (English): you may need to tweak the solution if you use different settings.

Selecting week / ISO week number from a date/time field

The DATEPART function expects a date / datetime / datetime2 value. You are passing in an integer representing the day or month number.

Assuming you're storing your dates correctly, you just need to pass in the date value directly:

DATEPART(WEEK, Max(AuditHistory.ActionedDateTime)) As WeekNo

convert numeric week of year to a date (yyyy-mm-dd) in hiveql

To get accurate date you need to provide also week day along with year and week number in a year.

select date_format('2020-10-18','w'), from_unixtime(unix_timestamp('2020, 43, 7', 'yyyy, w, u'), 'yyyy-MM-dd');

Returns:

43   2020-10-18

It looks like week number in a year counted from Sundays and day number in a week is counted from Mondays because Monday is 19th:

select date_format('2020-10-18','w u'), from_unixtime(unix_timestamp('2020, 43, 1', 'yyyy, w, u'), 'yyyy-MM-dd');

returns

43  2020-10-19

If that is true, you can fix it by subtracting 60*60*24 from unix_timestamp:

select date_format('2020-10-18','w'), from_unixtime(unix_timestamp('2020, 43, 1', 'yyyy, w, u')-60*60*24, 'yyyy-MM-dd');

Returns:

43  2020-10-18

UPDATE: Surprisingly, if not providing day in a week, only year and week number, it works also counting Sunday as a week day by default but it will be not correct for other dates for example 2020-01-20, it will return the same Sunday 2020-01-18, check it yourself:

select date_format('2020-10-18','w'), from_unixtime(unix_timestamp('2020, 43', 'yyyy, w'), 'yyyy-MM-dd');

returns:

43  2020-10-18

So, if you do not have day in a week and do not need absolutely accurate date, then use

from_unixtime(unix_timestamp('2020, 43', 'yyyy, w'), 'yyyy-MM-dd');

Or like this (year and week number are selected from the table):

  select from_unixtime(unix_timestamp(concat(col_year, ', ', col_week), 'yyyy, w'), 'yyyy-MM-dd') from your_table;

convert date column into week number, date, month, year

In Sql server, you don't wrap your identifiers with `.

However, since Time is a data type in sql server, I've wrapped it with []:
Try this query instead:

SELECT DATEPART(Week,[Time]) As [Week],
       DATEPART(Day,[Time]) As [Day],
       DATEPART(Month,[Time]) As [Month],
       DATEPART(Year,[Time]) As [Year] 
FROM Test

Also, there was an extra comma after the last element in the select clause,
and the Where clause was meaningless, so I've removed it.

---

Related Topics