August 30, 2017 at 12:08 pm
I am trying to determine the first Mondayof the next Month when the first Monday of this month has passed. Also the samefor a Monday on a week. I have the calendar created, I'm just stuck on tryingto get the next Monday. So if the date is 1-1-17, I know the first Monday ofthe month is 1-2-17. If the date is 1-10-17, the next first Monday of a monthis 2-6-17. I think I can figure out the week, if I just have some help on themonth. Here is my calendar.
(DayID INT IDENTITY(1, 1) ,DayDate SMALLDATETIME ,WeekNumber INT ,DayNumber INT ,NameOfDay VARCHAR(10) )
--------------------------
GO
SET NOCOUNT ON;
DECLARE @Date SMALLDATETIME;
SET DATEFIRST 7;
SET@Date = '2017-01-01';
WHILE@Date < '2040-01-01'
BEGIN
INSERT INTO JEC_CalendarDay (DayDate,WeekNumber,DayNumber,NameOfDay )
SELECT@Date
, (DATEPART(WK, @Date))
, DATEPART(WEEKDAY, @Date)
, CASE DATEPART(WEEKDAY, @Date)
WHEN 2 THEN 'Monday'
WHEN 3 THEN 'Tuesday'
WHEN 4 THEN 'Wednesday'
WHEN 5THEN 'Thursday'
WHEN 6 THEN 'Friday'
WHEN 7THEN 'Saturday'
ELSE 'Sunday' END;
SET@Date = DATEADD(day, 1, @Date);
END
GO
August 30, 2017 at 12:26 pm
I don't have time to do what you want right now, but I can give you some tips on that calendar table.
1) Remove the DayId and create a clustered index on DayDate instead
2) Don't use a loop to populate it, instead do it in one hit, using something like this
DECLARE @StartDate DATE = '20170101';
DECLARE @EndDate DATE = '20400101';
DECLARE @NoOfDays INT = DATEDIFF(DAY, @StartDate, @EndDate);
SELECT
Date = y.d
, DayNumber = DAY(y.d)
, DayName = DATENAME(WEEKDAY, y.d)
, Week = DATEPART(WK, y.d)
FROM
(
SELECT d = DATEADD(DAY, x.rn - 1, @StartDate)
FROM
(
SELECT TOP (@NoOfDays)
rn = ROW_NUMBER() OVER (ORDER BY s1.object_id)
FROM
sys.all_objects s1
CROSS JOIN sys.all_objects s2
ORDER BY s1.object_id
) x
) y;
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
August 30, 2017 at 2:53 pm
If I understand what you want. This will give it to you.
SELECT TOP 1 firstMonday
FROM
(
VALUES
(0, CAST(DATEADD(MONTH, DATEDIFF(MONTH, 6, GETDATE()), 6) AS DATE))
, (1, CAST(DATEADD(MONTH, DATEDIFF(MONTH, 6, GETDATE()) + 1, 6) AS DATE))
) d7(monthOffset, day7OfMonth)
CROSS APPLY
(
SELECT DATEADD(WEEK, DATEDIFF(WEEK, 1, day7OfMonth), 1)
) fm(firstMonday)
WHERE fm.firstMonday >= CAST(GETDATE() AS DATE)
ORDER BY fm.firstMonday
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
August 30, 2017 at 4:40 pm
It looks like you already have your answer. For future reference, Lynn Pettis has a great list of common date functions at http://www.sqlservercentral.com/blogs/lynnpettis/2009/03/25/some-common-date-routines/.
September 4, 2017 at 11:41 am
drew.allen - Wednesday, August 30, 2017 2:53 PMIf I understand what you want. This will give it to you.
SELECT TOP 1 firstMonday
FROM
(
VALUES
(0, CAST(DATEADD(MONTH, DATEDIFF(MONTH, 6, GETDATE()), 6) AS DATE))
, (1, CAST(DATEADD(MONTH, DATEDIFF(MONTH, 6, GETDATE()) + 1, 6) AS DATE))
) d7(monthOffset, day7OfMonth)
CROSS APPLY
(
SELECT DATEADD(WEEK, DATEDIFF(WEEK, 1, day7OfMonth), 1)
) fm(firstMonday)
WHERE fm.firstMonday >= CAST(GETDATE() AS DATE)
ORDER BY fm.firstMondayDrew
If you run that code on 2017-09-04 (or replace GETDATE() with '2017-09-04'), which is a Monday, it returns 2017-09-05, which is not.
--Jeff Moden
Change is inevitable... Change for the better is not.
September 4, 2017 at 12:03 pm
Ed Wagner - Wednesday, August 30, 2017 4:40 PMIt looks like you already have your answer. For future reference, Lynn Pettis has a great list of common date functions at http://www.sqlservercentral.com/blogs/lynnpettis/2009/03/25/some-common-date-routines/.
Those are good but a word of caution on the Week calculations. Lynn's good code always treats Sunday as the start of the week. If you're working with weeks that start on another day, it's not going to work so well.
--Jeff Moden
Change is inevitable... Change for the better is not.
September 4, 2017 at 12:18 pm
jcobb 20350 - Wednesday, August 30, 2017 12:08 PMI am trying to determine the first Mondayof the next Month when the first Monday of this month has passed. Also the samefor a Monday on a week. I have the calendar created, I'm just stuck on tryingto get the next Monday. So if the date is 1-1-17, I know the first Monday ofthe month is 1-2-17. If the date is 1-10-17, the next first Monday of a monthis 2-6-17. I think I can figure out the week, if I just have some help on themonth. Here is my calendar.
CREATE TABLE JEC_CalendarDay(DayID INT IDENTITY(1, 1) ,DayDate SMALLDATETIME ,WeekNumber INT ,DayNumber INT ,NameOfDay VARCHAR(10) )
--------------------------
GO
SET NOCOUNT ON;
DECLARE @Date SMALLDATETIME;
SET DATEFIRST 7;
SET@Date = '2017-01-01';
WHILE@Date < '2040-01-01'BEGIN
INSERT INTO JEC_CalendarDay (DayDate,WeekNumber,DayNumber,NameOfDay )
SELECT@Date
, (DATEPART(WK, @Date))
, DATEPART(WEEKDAY, @Date)
, CASE DATEPART(WEEKDAY, @Date)
WHEN 2 THEN 'Monday'
WHEN 3 THEN 'Tuesday'
WHEN 4 THEN 'Wednesday'
WHEN 5THEN 'Thursday'
WHEN 6 THEN 'Friday'
WHEN 7THEN 'Saturday'
ELSE 'Sunday' END;SET@Date = DATEADD(day, 1, @Date);
END
GO
Just making double sure... What day of the week do your weeks start on? Sunday/7 for sure?
--Jeff Moden
Change is inevitable... Change for the better is not.
September 4, 2017 at 5:16 pm
Jeff Moden - Monday, September 4, 2017 12:03 PMEd Wagner - Wednesday, August 30, 2017 4:40 PMIt looks like you already have your answer. For future reference, Lynn Pettis has a great list of common date functions at http://www.sqlservercentral.com/blogs/lynnpettis/2009/03/25/some-common-date-routines/.Those are good but a word of caution on the Week calculations. Lynn's good code always treats Sunday as the start of the week. If you're working with weeks that start on another day, it's not going to work so well.
Yes it does. That's why they always work so well for me. 😉
September 4, 2017 at 6:30 pm
Ed Wagner - Monday, September 4, 2017 5:16 PMJeff Moden - Monday, September 4, 2017 12:03 PMEd Wagner - Wednesday, August 30, 2017 4:40 PMIt looks like you already have your answer. For future reference, Lynn Pettis has a great list of common date functions at http://www.sqlservercentral.com/blogs/lynnpettis/2009/03/25/some-common-date-routines/.Those are good but a word of caution on the Week calculations. Lynn's good code always treats Sunday as the start of the week. If you're working with weeks that start on another day, it's not going to work so well.
Yes it does. That's why they always work so well for me. 😉
Heh... I'll never understand how Sunday became a "begininator" for weeks. When I was growing up, the "weekend" was always Saturday and Sunday as the last two days of the week on all of the calendars.
--Jeff Moden
Change is inevitable... Change for the better is not.
September 4, 2017 at 11:26 pm
I'm not sure that we need a Calendar table for this. The following function works in all versions of SQL Server from 2005 and up. I'm in the habit of avoiding the "Week" date part because I've been burned by it in other areas before. Document the function as you see fit. I'll let you explain how it works and how to use it. You've gotta have some of the fun. 😉 I didn't add a "Week Number" output because SQL Week Numbers are actually incorrect. For example, Week 1 of 2016 doesn't even have a Monday in it and the last day of 2015, which is in the same calendar week, has a different week number. You would be better off with ISO Weeks but I'll let you make that call. Add it to the following function if you really think you need it.
CREATE FUNCTION dbo.GetNextMondays
(@SomeDT DATETIME)
RETURNS TABLE WITH SCHEMABINDING AS
RETURN
WITH cteMondays AS
(
SELECT ThisMonthMonday = DATEADD(dd,DATEDIFF(dd,'1753',DATEADD(mm,DATEDIFF(mm,'1753',@SomeDT) ,'1753')+6)/7*7,'1753')
,NextMonthMonday = DATEADD(dd,DATEDIFF(dd,'1753',DATEADD(mm,DATEDIFF(mm,'1753',@SomeDT)+1,'1753')+6)/7*7,'1753')
)
SELECT DayDate = @SomeDT
,DayNumber = (DATEDIFF(dd,'1753',@SomeDT))%7+1
,NameOfDay = DATENAME(dw,@SomeDT)
,NextWeekMonday = DATEADD(dd,DATEDIFF(dd,'1753',@SomeDT)/7*7+7,'1753')
,NextMonthMonday = CASE
WHEN @SomeDT >= ThisMonthMonday
THEN NextMonthMonday
ELSE ThisMonthMonday
END
FROM cteMondays
;
To use it for a single date, just do something like this...
SELECT * FROM dbo.GetNextMondays (GETDATE());
To use it against a table, do something like this...
SELECT mon.*
FROM dbo.SomeTable st
CROSS APPLY dbo.GetNextMondays(st.SomeDateColumn) mon
;
The cool part about the function is that, although it'll sometimes be a bit slower than a Calendar table, it produces no logical reads.
If you're hell bent on having a Calendar table, post back and I'll show you another function that you'll need someday soon.
--Jeff Moden
Change is inevitable... Change for the better is not.
September 5, 2017 at 12:00 pm
jcobb 20350 - Wednesday, August 30, 2017 12:08 PMI am trying to determine the first Mondayof the next Month when the first Monday of this month has passed. Also the samefor a Monday on a week. I have the calendar created, I'm just stuck on tryingto get the next Monday. So if the date is 1-1-17, I know the first Monday ofthe month is 1-2-17. If the date is 1-10-17, the next first Monday of a monthis 2-6-17. I think I can figure out the week, if I just have some help on themonth. Here is my calendar.
CREATE TABLE JEC_CalendarDay(DayID INT IDENTITY(1, 1) ,DayDate SMALLDATETIME ,WeekNumber INT ,DayNumber INT ,NameOfDay VARCHAR(10) )
--------------------------
GO
SET NOCOUNT ON;
DECLARE @Date SMALLDATETIME;
SET DATEFIRST 7;
SET@Date = '2017-01-01';
WHILE@Date < '2040-01-01'BEGIN
INSERT INTO JEC_CalendarDay (DayDate,WeekNumber,DayNumber,NameOfDay )
SELECT@Date
, (DATEPART(WK, @Date))
, DATEPART(WEEKDAY, @Date)
, CASE DATEPART(WEEKDAY, @Date)
WHEN 2 THEN 'Monday'
WHEN 3 THEN 'Tuesday'
WHEN 4 THEN 'Wednesday'
WHEN 5THEN 'Thursday'
WHEN 6 THEN 'Friday'
WHEN 7THEN 'Saturday'
ELSE 'Sunday' END;SET@Date = DATEADD(day, 1, @Date);
END
GO
Why do you have an IDENTITY table property in a calendar table? The calendar date is, by definition, the natural key of the table! An identity table property is a left over from Sybase and UNIX to identify record numbers on a physical tape!
You also need to read the ISO 8601 standards for temporal display formats. Among them is a week date format, which uses the year (four digits) a letter “W†as a separator, The number of the week within the year, which is between 01 and 52 or 53, a dash as a separator, ending with the day of the week number (1 = Monday, 7 = Sunday). You can find websites from which you can download calendars in this format. It’s popular in the Nordic countries..
Since the only format allowed in ANSI/ISO standard SQL is “yyyy-mm-ddâ€, We know that you’ve never read anything about standard SQL by your posting. You use some local dialect; why?
The basic calendar table would look something like this skeleton
CREATE TABLE Calendar
(cal_date DATE NOT NULL PRIMARY KEY,
week_date CHAR(10) NOT NULL
CHECK (week_date LIKE ‘[12][0-9][0-9][0-9]W[0-5][0-9]-[1-7]’),
..);
Instead of writing SQL as if it was COBOL, try writing it as if it was a database language. That means we don’t do the display formatting by printing out the weekday names as English language strings. That would be done in a presentation layer in a properly designed schema. For fun, look up the names of the days of the week in Czech and Slovak then compare them. Those guys used to be one country!
Quit writing in loops. SQL is a declarative language and by definition, we don’t have loops.
I will leave it to you to figure out how to write the SQL for your particular query. Obviously, the Monday in any week can be found with x LIKE ‘____W__-1’ in a string match predicate. This gives you the super-set of Mondays; you will need to filter the calendar date by month.
Youalso need to read the ISO 8601 standards for temporal displayformats. Among them is a week date format, whichuses the year (four digits) a letter “W†as a separator, Thenumber of the week within the year, which is between 01 and 52 or 53,a dash as a separator, endingwith the day of the week number (1 = Monday, 7 = Sunday). You canfind websites from which you can download calendars in this format.It’s popular in the Nordic countries..
Sincethe only format allowed in ANSI/ISO standard SQL is “yyyy-mm-ddâ€,We know that you’ve never read anything about standard SQL by yourposting. You use some local dialect; why?
Thebasic calendar table would look something like this skeleton
CREATETABLE Calendar
(cal_dateDATE NOT NULL PRIMARY KEY,
week_dateCHAR(10) NOT NULL
CHECK(week_date LIKE ‘[12][0-9][0-9][0-9]W[0-5][0-9]-[1-7]’),
..);
Insteadof writing SQL as if it was COBOL, try writing it as if it was adatabase language. That means we don’t do the display formatting byprinting out the weekday names as English language strings. Thatwould be done in a presentation layer in a properly designed schema.For fun, look up the names of the days of the week in Czech andSlovak then compare them. Those guys used to be one country!
Quitwriting in loops. SQL is a declarative language and by definition, wedon’t have loops.
Iwill leave it to you to figure out how to write the SQL for yourparticular query. Obviously, the Monday in any week can be found with x LIKE ‘____W__-1’ in a string match predicate. This gives youthe super-set of Mondays; you will need to filter the calendar dateby month.
Please post DDL and follow ANSI/ISO standards when asking for help.
September 5, 2017 at 12:14 pm
Jeff Moden - Monday, September 4, 2017 11:41 AMdrew.allen - Wednesday, August 30, 2017 2:53 PMIf I understand what you want. This will give it to you.
SELECT TOP 1 firstMonday
FROM
(
VALUES
(0, CAST(DATEADD(MONTH, DATEDIFF(MONTH, 6, GETDATE()), 6) AS DATE))
, (1, CAST(DATEADD(MONTH, DATEDIFF(MONTH, 6, GETDATE()) + 1, 6) AS DATE))
) d7(monthOffset, day7OfMonth)
CROSS APPLY
(
SELECT DATEADD(WEEK, DATEDIFF(WEEK, 1, day7OfMonth), 1)
) fm(firstMonday)
WHERE fm.firstMonday >= CAST(GETDATE() AS DATE)
ORDER BY fm.firstMondayDrew
If you run that code on 2017-09-04 (or replace GETDATE() with '2017-09-04'), which is a Monday, it returns 2017-09-05, which is not.
I wrote this off-the cuff, and didn't have time to thoroughly test it. The basic concept should work, though.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
September 5, 2017 at 12:57 pm
Thanks to everyone. These really helped. I think I have what I need.
September 5, 2017 at 1:41 pm
jcelko212 32090 - Tuesday, September 5, 2017 12:00 PMjcobb 20350 - Wednesday, August 30, 2017 12:08 PMI am trying to determine the first Mondayof the next Month when the first Monday of this month has passed. Also the samefor a Monday on a week. I have the calendar created, I'm just stuck on tryingto get the next Monday. So if the date is 1-1-17, I know the first Monday ofthe month is 1-2-17. If the date is 1-10-17, the next first Monday of a monthis 2-6-17. I think I can figure out the week, if I just have some help on themonth. Here is my calendar.
CREATE TABLE JEC_CalendarDay(DayID INT IDENTITY(1, 1) ,DayDate SMALLDATETIME ,WeekNumber INT ,DayNumber INT ,NameOfDay VARCHAR(10) )
--------------------------
GO
SET NOCOUNT ON;
DECLARE @Date SMALLDATETIME;
SET DATEFIRST 7;
SET@Date = '2017-01-01';
WHILE@Date < '2040-01-01'BEGIN
INSERT INTO JEC_CalendarDay (DayDate,WeekNumber,DayNumber,NameOfDay )
SELECT@Date
, (DATEPART(WK, @Date))
, DATEPART(WEEKDAY, @Date)
, CASE DATEPART(WEEKDAY, @Date)
WHEN 2 THEN 'Monday'
WHEN 3 THEN 'Tuesday'
WHEN 4 THEN 'Wednesday'
WHEN 5THEN 'Thursday'
WHEN 6 THEN 'Friday'
WHEN 7THEN 'Saturday'
ELSE 'Sunday' END;SET@Date = DATEADD(day, 1, @Date);
END
GO
Why do you have an IDENTITY table property in a calendar table? The calendar date is, by definition, the natural key of the table! An identity table property is a left over from Sybase and UNIX to identify record numbers on a physical tape!
You also need to read the ISO 8601 standards for temporal display formats. Among them is a week date format, which uses the year (four digits) a letter “W†as a separator, The number of the week within the year, which is between 01 and 52 or 53, a dash as a separator, ending with the day of the week number (1 = Monday, 7 = Sunday). You can find websites from which you can download calendars in this format. It’s popular in the Nordic countries..
Since the only format allowed in ANSI/ISO standard SQL is “yyyy-mm-ddâ€, We know that you’ve never read anything about standard SQL by your posting. You use some local dialect; why?
The basic calendar table would look something like this skeleton
CREATE TABLE Calendar
(cal_date DATE NOT NULL PRIMARY KEY,
week_date CHAR(10) NOT NULL
CHECK (week_date LIKE ‘[12][0-9][0-9][0-9]W[0-5][0-9]-[1-7]’),
..);Instead of writing SQL as if it was COBOL, try writing it as if it was a database language. That means we don’t do the display formatting by printing out the weekday names as English language strings. That would be done in a presentation layer in a properly designed schema. For fun, look up the names of the days of the week in Czech and Slovak then compare them. Those guys used to be one country!
Quit writing in loops. SQL is a declarative language and by definition, we don’t have loops.
I will leave it to you to figure out how to write the SQL for your particular query. Obviously, the Monday in any week can be found with x LIKE ‘____W__-1’ in a string match predicate. This gives you the super-set of Mondays; you will need to filter the calendar date by month.
Whydo you have an IDENTITY tableproperty ina calendar table? The calendar date is, by definition, the naturalkey of the table! An identity table property is a left over fromSybase and UNIX to identify record numbers on a physical tape! Youalso need to read the ISO 8601 standards for temporal displayformats. Among them is a week date format, whichuses the year (four digits) a letter “W†as a separator, Thenumber of the week within the year, which is between 01 and 52 or 53,a dash as a separator, endingwith the day of the week number (1 = Monday, 7 = Sunday). You canfind websites from which you can download calendars in this format.It’s popular in the Nordic countries..
Sincethe only format allowed in ANSI/ISO standard SQL is “yyyy-mm-ddâ€,We know that you’ve never read anything about standard SQL by yourposting. You use some local dialect; why?
Thebasic calendar table would look something like this skeleton
CREATETABLE Calendar
(cal_dateDATE NOT NULL PRIMARY KEY,
week_dateCHAR(10) NOT NULL
CHECK(week_date LIKE ‘[12][0-9][0-9][0-9]W[0-5][0-9]-[1-7]’),
..);
Insteadof writing SQL as if it was COBOL, try writing it as if it was adatabase language. That means we don’t do the display formatting byprinting out the weekday names as English language strings. Thatwould be done in a presentation layer in a properly designed schema.For fun, look up the names of the days of the week in Czech andSlovak then compare them. Those guys used to be one country!
Quitwriting in loops. SQL is a declarative language and by definition, wedon’t have loops.
Iwill leave it to you to figure out how to write the SQL for yourparticular query. Obviously, the Monday in any week can be found with x LIKE ‘____W__-1’ in a string match predicate. This gives youthe super-set of Mondays; you will need to filter the calendar dateby month.
Check again. The ISO 8601 standards allow YYYYMMDD, as well. And that bloody ISO week format is useless in SQL Server because there's no such built-in format and there's no such datatype meaning that you usually end up with non-SARGable queries unless you make more than one trip to the (ugh!) Calendar table.
I do agree about the IDENTITY column... mostly useless on a Calendar table except to keep religious DBAs that published rigid but inappropriately religious standards at bay (kinda like some of your insane standards like storing years/months as YYYY-MM-00, which totally violates your take on things "in the presentation layer" and violates the ISO standard which states that the DD portion shall only have values of 01 thru 31 as appropriate for what the value of MM is).
As for the long names for months being relegated to only the presentation layer, I agree but only with the caveat that sometimes a stored procedure IS (unfortunately) the presentation layer.
--Jeff Moden
Change is inevitable... Change for the better is not.
September 5, 2017 at 2:16 pm
Jeff Moden - Tuesday, September 5, 2017 1:41 PMCheck again. The ISO 8601 standards allow YYYYMMDD, as well. And that bloody ISO week format is useless in SQL Server because there's no such built-in format and there's no such datatype meaning that you usually end up with non-SARGable queries unless you make more than one trip to the (ugh!) Calendar table.I do agree about the IDENTITY column... mostly useless on a Calendar table except to keep religious DBAs that published rigid but inappropriately religious standards at bay (kinda like some of your insane standards like storing years/months as YYYY-MM-00, which totally violates your take on things "in the presentation layer" and violates the ISO standard which states that the DD portion shall only have values of 01 thru 31 as appropriate for what the value of MM is).
As for the long names for months being relegated to only the presentation layer, I agree but only with the caveat that sometimes a stored procedure IS (unfortunately) the presentation layer.
YYYY-MM-00????? Is that a joke? :angry::crazy::alien::sick:
Viewing 15 posts - 1 through 15 (of 43 total)
You must be logged in to reply to this topic. Login to reply