February 9, 2016 at 8:51 am
Here is a DDL to create a table with the dates that I'm working with
create table [DateTest]
(
CalendarYear int
,DateValue datetime
)
insert into DateTest (CalendarYear, DateValue)
values ('2015', '12/27/2015')
insert into DateTest (CalendarYear, DateValue)
values ('2015', '12/28/2015')
insert into DateTest (CalendarYear, DateValue)
values ('2015', '12/29/2015')
insert into DateTest (CalendarYear, DateValue)
values ('2015', '12/30/2015')
insert into DateTest (CalendarYear, DateValue)
values ('2015', '12/31/2015')
insert into DateTest (CalendarYear, DateValue)
values ('2016', '01/01/2016')
insert into DateTest (CalendarYear, DateValue)
values ('2016', '01/02/2016')
insert into DateTest (CalendarYear, DateValue)
values ('2016', '01/03/2016')
insert into DateTest (CalendarYear, DateValue)
values ('2016', '01/04/2016')
When you run the following SQL statement
select CalendarYear
,case when datepart(dw, [DateValue]) = 1 then
convert(int, convert(char(4), datepart(year, DATEADD(wk, DATEDIFF(wk,0,([DateValue] -1)), 0))) + replicate('0', 2) - len(datepart(week, DATEADD(wk, DATEDIFF(wk,0,([DateValue] -1)), 0))) + convert(char(4), datepart(week, DATEADD(wk, DATEDIFF(wk,0,([DateValue] -1)),0))))
else
convert(int, convert(char(4), datepart(year, DATEADD(wk, DATEDIFF(wk,0,[DateValue]), 0))) + replicate('0', 2) - len(datepart(week, DATEADD(wk, DATEDIFF(wk,0,[DateValue]), 0))) + convert(char(4), datepart(week, DATEADD(wk, DATEDIFF(wk,0,[DateValue]), 0))))
end
NewWeek
, datepart(dw,[DateValue])
from DateTest
where DateValue between '12/28/2015' and '01/03/2016'
You will get results like this
CalendarYear NewWeek DayOfWeek
------------ ----------- -----------
2015 201551 2
2015 201551 3
2015 201551 4
2015 201551 5
2016 201551 6
2016 201551 7
2016 201551 1
As you can see the calendar year is showing 2016 for the January dates, however, I need it to show 2015. The reason for needing it to be 2015 is that per my fiscal calendar they are 2015 dates. The only time I need this to happen (as I didn't provide future dates) is when the week traverses starts in December and ends in January. My fiscal calendar week is Monday to Sunday.
Any help is greatly appreciated
February 9, 2016 at 10:25 pm
What day does your standard week begin on, Saturday, Sunday, Monday,...?
As an aside, this is the kind of stuff I would put into my calendar table. So, if you do not have a calendar table in your system you should consider getting one. And if you do, or once you do, you would have a column called fiscal_calendar_year that stored that data point for every date in your table. At that point the answer for which fiscal year a day belongs to is trivial to lookup. Granted, you have to write the logic to populate said column on the calendar table but the date math you're dealing with is tough to read and tough to debug and having the values in a table just makes this kind of work simple as there will certainly be a need for many more variations in the course of building a system that has dependencies on dates. If you happen to want to stick with the date-math I would definitely recommend locking whatever solution you choose into an inline table-valued function so you never have to copy and paste the date-math code in multiple places and it will be easy to write unit tests against.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
February 10, 2016 at 5:06 am
Standard week is Sunday to Monday.
My mistake, I'm trying to get the information to get the correct value that will place the results into a date table that I have, if I need to create a new column to hold this information I will do so.
February 10, 2016 at 10:19 pm
Here is the logic to find the fiscal calendar year for those days at the beginning of January that are part of the last week in the previous year. The cte just builds a column full of dates for testing so you can adjust the top and start date as needed for testing.
with dates as (select top 2100
dateadd(day,row_number() over (order by (select null))-1,'1/1/2015') as dt
from master.sys.columns c1
cross join master.sys.columns c2)
select dt,
year(dt)-1 as fiscal_calendar_year,
datename(dw,dt) as day_name
from dates
where month(dt) = 1
and datepart(dw,dt) > day(dt);
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply