CODEWITHSUNDEEP
sql-serverdate
Doosu
Doosu

Reputation: 13

Get date from week and year - date must be first day of the year - sql

I have a query that converts week and year to date. But it returns the exact date.

But what i want is, I need the date to be such a day, that is same as the first day of the year. 

dateadd (week, PromisedWeek-1, dateadd (year, PromisedYear-1900, 0)) - 4 -
datepart(dw, dateadd (week, PromisedWeek-1, dateadd (year, PromisedYear-1900, 0)) - 4) + 1

Hypothetical example. My current query does is: If week is 4 and year 2017, returns 26-Sun-2017

My need: If week is 4 and year 2017 and January 1st was a Wednesday, its should return 29-Wednesday-2017.

Hoping that you guys get what I am trying to explain.

I need the query to return such a date which has the same day as that of the current year's 1st day.

Upvotes: 1

Views: 227

Answers (2)

Rominus
Rominus

Reputation: 1221

This is pretty easy if you have a calendar table. A calendar table is a table that contains every date and its parts for a given range. I created mine for 2010 to 2020 with this code:

declare @start_dt as date = '1/1/2010';     
declare @end_dt as date = '1/1/2020';       

declare @dates as table (
 date_id date primary key,
 date_year smallint,
 date_month tinyint,
 date_day tinyint,
 weekday_id tinyint,
 weekday_nm varchar(10),
 month_nm varchar(10),
 day_of_year smallint,
 quarter_id tinyint,
 first_day_of_month date,
 last_day_of_month date,
 start_dts datetime,
 end_dts datetime
)

while @start_dt < @end_dt
begin
    insert into @dates(
        date_id, date_year, date_month, date_day, 
        weekday_id, weekday_nm, month_nm, day_of_year, quarter_id, 
        first_day_of_month, last_day_of_month, 
        start_dts, end_dts
    )   
    values(
        @start_dt, year(@start_dt), month(@start_dt), day(@start_dt), 
        datepart(weekday, @start_dt), datename(weekday, @start_dt), datename(month, @start_dt), datepart(dayofyear, @start_dt), datepart(quarter, @start_dt),
        dateadd(day,-(day(@start_dt)-1),@start_dt), dateadd(day,-(day(dateadd(month,1,@start_dt))),dateadd(month,1,@start_dt)), 
        cast(@start_dt as datetime), dateadd(second,-1,cast(dateadd(day, 1, @start_dt) as datetime))
    )
    set @start_dt = dateadd(day, 1, @start_dt)
end

select * 
into Calendar
from @dates

Once you have a calendar table you can query for the correct weekday in the PromisedWeek

declare @PromisedYear as numeric
declare @PromisedWeek as int

set @PromisedYear = 2017
set @PromisedWeek = 4

select concat(date_day, '-', weekday_nm, '-', @PromisedYear)
from Calendar as c
where 
    weekday_id = 
        (select weekday_ID 
        from Calendar 
        where date_year = @PromisedYear
            and day_of_year = 1
        )
    and datepart(week, date_id) = @PromisedWeek
    and date_year = @PromisedYear

Turns out that January 1st 2017 was a Sunday and the 4th Sunday in 2017 was January 22nd, so the return is 22-Sunday-2017

Upvotes: 0

Joel Coehoorn
Joel Coehoorn

Reputation: 415665

Unless I'm reading this wrong, you're making things too hard on yourself. Just do this:

declare @year int; set @year = 2017;
declare @week int; set @week = 4;

select dateadd(week, @week, dateadd(year, @year - 1900, 0))

Result:

2017-01-29 00:00:00.000

The inner dateadd() gives you the first day of the requested year (Jan 1), regardless of weekday. If it's a Wednesday, you'll get a Wednesday. This year, it's Sunday. The outer dateadd() then adds full weeks to that, so you end up with the same day of the week, just like you asked.

If that's not what you want, please explain how it needs to be more complicated.

Upvotes: 0

Related Questions