February 6, 2018 at 11:22 am
Hi,
I have a calendar table that have the days, I need to add the following columns
Days in a month
Days in a quarter
Days in a year
Weeks in a month
Weeks in a quarter
I am finding the same issue with calculating how long to someone birthday.
Here is what I wrote
--days in a month datediff(day, [Date], dateadd(month, 1, [Date])), [Date]
--days in a quarter datediff(day, [Date], dateadd(quarter, 1, [Date]))
--days in a year datediff(day, [Date], dateadd(year, 1, [Date]))
--weeks in a quarter datediff(WEEK, [Date], dateadd(QUARTER, 1, [Date]))
Ideas please 🙂
February 6, 2018 at 11:38 am
Lynn Pettis wrote a quick article on a bunch of useful date functions. It's here:
http://www.sqlservercentral.com/blogs/lynnpettis/2009/03/25/some-common-date-routines/
February 6, 2018 at 11:41 am
thanks, but that is not what i was looking for
February 6, 2018 at 12:00 pm
astrid 69000 - Tuesday, February 6, 2018 11:22 AMHi,
I have a calendar table that have the days, I need to add the following columns
Days in a month
Days in a quarter
Days in a year
Weeks in a month
Weeks in a quarter
I am finding the same issue with calculating how long to someone birthday.
Here is what I wrote
--days in a month datediff(day, [Date], dateadd(month, 1, [Date])), [Date]
--days in a quarter datediff(day, [Date], dateadd(quarter, 1, [Date]))
--days in a year datediff(day, [Date], dateadd(year, 1, [Date]))
--weeks in a quarter datediff(WEEK, [Date], dateadd(QUARTER, 1, [Date]))Ideas please 🙂
You're on the right track for days in a quarter and days in a year, but you need to use the first day in a quarter/year for the comparison. You've already been pointed to a website of useful date functions which includes finding the first day in a period.
Weeks in a quarter is more problematic, because week could be defined several different ways, and each of those ways would have a slightly different coding. For instance, what day does your week start? How do you want to handle quarters that start/end in the middle of the week?
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
February 6, 2018 at 12:07 pm
I know where my problem is ::), but whatever I coded didn't give me the right answer. that is why I asked for help. 🙂
February 7, 2018 at 6:02 am
To be able to answer this question correctly, you need to tell us what a week, month, and quarter is.
What day of the week does a week start on?
What do you want done with months that don't have a whole week, especially the first and last weeks of any given year?
Since years don't have a whole number of weeks, how do you want to handle that?
What's a month? Does it start on the 1st and end the day before the 1st of the next month or does it start and end based on your definition of a week?
What's a quarter? We all know the classic definition based on calendar days but that and the definition of what a month is all go flying out the window if you start talking about whole weeks.
Or, does it even matter if weeks and months and quarters don't actually start on the same day of the week?
Or, have they really messed things up with a fiscal calendar?
So, how do you want to define what week, month, and quarter is?
--Jeff Moden
Change is inevitable... Change for the better is not.
February 7, 2018 at 8:10 am
Basically we are talking about a fiscal year.
The year starts Jan 1st
the months start on each month. as the quarters do too and last but not least the weekday stars on Sunday (I don't even know why I did that)
February 7, 2018 at 11:52 am
oki, I have almost figured it our, I have a table with a columns date, so here is what I did (I am not a 100% about the weeks in a quarter)
with firstdayoff
as
(
select
[date],
FirstDayOfMonth = FirstOfMonth,
LastDayOfMonth = MAX([date]) OVER (PARTITION BY [year], [month]),
FirstDayOfQuarter = MIN([date]) OVER (PARTITION BY [year], [quarter]),
LastDayOfQuarter = MAX([date]) OVER (PARTITION BY [year], [quarter]),
FirstDayOfYear = FirstOfYear,
LastDayOfYear = MAX([date]) OVER (PARTITION BY [year]),
FirstDayOfNextMonth = DATEADD(MONTH, 1, FirstOfMonth),
FirstDayOfNextYear = DATEADD(YEAR, 1, FirstOfYear)
from #dim
)
select
dd.[Date],
datediff(day, ff.FirstDayOfMonth, dateadd(month, 0, dateadd(dd,1,ff.LastDayOfMonth))) as [DaysinMonth],
datediff(day, ff.FirstDayOfQuarter, dateadd(QUARTER, 0, dateadd(dd,1,ff.LastDayOfQuarter))) [DaysinQuarter],
datediff(day, ff.FirstDayOfYear, dateadd(year, 0, dateadd(dd,1,ff.LastDayOfYear))) [DaysinYear],
datediff(WEEK, ff.FirstDayOfQuarter, dateadd(QUARTER, 0, dateadd(dd,1,ff.LastDayOfQuarter)))
from #dim dd inner join firstdayoff ff on dd.[Date] = ff.[date]
order by dd.[Date]
February 7, 2018 at 11:55 am
It's wasteful to store monthly, quarterly and yearly data in a daily table (actually, it's wasteful to store days in a year anywhere, since your year is a calendar year). Create a separate table and store the values by month.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
February 7, 2018 at 12:41 pm
astrid 69000 - Wednesday, February 7, 2018 11:52 AMoki, I have almost figured it our, I have a table with a columns date, so here is what I did (I am not a 100% about the weeks in a quarter)
with firstdayoff
as
(
select
[date],
FirstDayOfMonth = FirstOfMonth,
LastDayOfMonth = MAX([date]) OVER (PARTITION BY [year], [month]),
FirstDayOfQuarter = MIN([date]) OVER (PARTITION BY [year], [quarter]),
LastDayOfQuarter = MAX([date]) OVER (PARTITION BY [year], [quarter]),
FirstDayOfYear = FirstOfYear,
LastDayOfYear = MAX([date]) OVER (PARTITION BY [year]),
FirstDayOfNextMonth = DATEADD(MONTH, 1, FirstOfMonth),
FirstDayOfNextYear = DATEADD(YEAR, 1, FirstOfYear)
from #dim
)
select
dd.[Date],
datediff(day, ff.FirstDayOfMonth, dateadd(month, 0, dateadd(dd,1,ff.LastDayOfMonth))) as [DaysinMonth],
datediff(day, ff.FirstDayOfQuarter, dateadd(QUARTER, 0, dateadd(dd,1,ff.LastDayOfQuarter))) [DaysinQuarter],
datediff(day, ff.FirstDayOfYear, dateadd(year, 0, dateadd(dd,1,ff.LastDayOfYear))) [DaysinYear],
datediff(WEEK, ff.FirstDayOfQuarter, dateadd(QUARTER, 0, dateadd(dd,1,ff.LastDayOfQuarter)))
from #dim dd inner join firstdayoff ff on dd.[Date] = ff.[date]
order by dd.[Date]
There is absolutely NO REASON to use Windowed functions here. The last day of this month is the first day of next month - 1. All of the dates here can be calculated by a similar method as is outlined in the link provided very early in this thread. Did you even bother to read that link?
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
February 7, 2018 at 1:02 pm
yes i read it and i tried, but it didnt work, it game me wrong date on 5% of the dates, and no need to be nasty, we are all here to learn or to exchange ideas.
February 8, 2018 at 5:52 am
astrid 69000 - Tuesday, February 6, 2018 11:22 AMHi,
I have a calendar table that have the days, I need to add the following columns
Days in a month
Days in a quarter
Days in a year
Weeks in a month
Weeks in a quarter
I am finding the same issue with calculating how long to someone birthday.
Here is what I wrote
--days in a month datediff(day, [Date], dateadd(month, 1, [Date])), [Date]
--days in a quarter datediff(day, [Date], dateadd(quarter, 1, [Date]))
--days in a year datediff(day, [Date], dateadd(year, 1, [Date]))
--weeks in a quarter datediff(WEEK, [Date], dateadd(QUARTER, 1, [Date]))Ideas please 🙂
It looks like you are asking how to add values to an existing calendar table, is that right? So for the entry for today's date, Feb 8, you want to know that it's the 8th day of the month, the 39th day of the quarter and 39th day of the year? (Xth day of the month is redundant, it's simply the day number.) And maybe that it's in the 6th week of the quarter (depending on how you define partial weeks at the beginning and end of the quarter)?
Your queries tell how many days it is until the next month, quarter, year, etc. I'm sure that's not what you want, since the answer for year will be 365 in most cases, 366 for leap years.
As far as birthdays go, we have one every year. Mine was last month. Do you want to know that my birthday in this year was 20-something days ago, or that my next birthday will be 300-something days from now?
Give us a little more info on what you want to achieve, and you might get more help.
February 8, 2018 at 6:32 am
Thanks, I solved it already 🙂
and belated happy birthday!!!! 🙂
February 8, 2018 at 7:17 am
astrid 69000 - Thursday, February 8, 2018 6:32 AMThanks, I solved it already 🙂
and belated happy birthday!!!! 🙂
Two way street here, astrid. Please post the code that you created to solve it.
As a bit of a sidebar, using a single "provides everything" Calendar table may actually work against you when it comes to performance. Even specialized Calendar tables may work against you there, depending on what you're trying to do. "Must look eye". 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
February 8, 2018 at 8:05 am
astrid 69000 - Wednesday, February 7, 2018 1:02 PMyes i read it and i tried, but it didnt work, it game me wrong date on 5% of the dates, and no need to be nasty, we are all here to learn or to exchange ideas.
Which dates did it get wrong? Those formulas are widely used precisely because they work and they're fast.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply