September 5, 2017 at 2: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.
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.
Jeez, Mr. Celko, layoff the COBOL references. You seem to think that if anyone wrote any COBOL they are guilty as sin about everything. Plus, most of the people posting here probably don't even know COBOL and wouldn't recognize it if they saw it.
Try helping people instead of always being so hyper-critical all the time.
September 5, 2017 at 2:19 pm
Jeff Moden - Monday, September 4, 2017 6:30 PMEd 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.
Because way back when and calendars were first getting printed, somebody decided that Sunday should be the first day on the calendar. Just a guess because I didn't feel like Googling it.
September 5, 2017 at 2:20 pm
Ok, so let's throw this out the window and get to the real point of what I want to accomplish. I have a review date field. It is in table of supplier id's. Another field in the same table has values: weekly, 2 weeks, 3 weeks, monthly, 2 months, 3 month, 4 months, 6 months and yearly. These are review cycles. I wanted to have a nightly update that looked at the review date to see if it had passed. If so, add the appropriate number of days from the review cycle to populate a new future review date. Such as,CASE WHEN review_date < GETDATE() AND cycle = 'weekly' THEN DATEADD(DD, 7, review_date) ELSE review_date END
I plan of resetting all current review dates before I start the nightly job for the first time. That way I'm not just adding 7 days to a date 8 months ago. After that the job should keep the table updated.
I thought a calendar table holding dates would be best and update from that, but I am not going to claim I know what's best. I'm also struggling with the 2 week, 3 week, etc. dates. The dates can always be a Monday because we'll look at the suppliers that need reviewed for the week of x, not a specific date.
September 5, 2017 at 2:52 pm
Ed Wagner - Tuesday, September 5, 2017 2:16 PMJeff 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:
No. Not a joke. He also uses YYYY-00-00 for year notations and stores such notations in tables that way, which violates the very ISO and "Presentation Layer" rules he's ranting about not to mention at least 2 "Best Practices" (Thou shalt not store dates as text and Thou shalt practice what thy preacheth.) 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
September 5, 2017 at 2:55 pm
Lynn Pettis - Tuesday, September 5, 2017 2:19 PMJeff Moden - Monday, September 4, 2017 6:30 PMEd 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.
Because way back when and calendars were first getting printed, somebody decided that Sunday should be the first day on the calendar. Just a guess because I didn't feel like Googling it.
Must've been a project manager... "And on the first day, he rested". 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
September 5, 2017 at 3:51 pm
Jeff Moden - Tuesday, September 5, 2017 2:52 PMEd Wagner - Tuesday, September 5, 2017 2:16 PMJeff 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:
No. Not a joke. He also uses YYYY-00-00 for year notations and stores such notations in tables that way, which violates the very ISO and "Presentation Layer" rules he's ranting about not to mention at least 2 "Best Practices" (Thou shalt not store dates as text and Thou shalt practice what thy preacheth.) 😉
It seems to be his favorite feature of MySQL.
September 5, 2017 at 10:05 pm
Lynn Pettis - Tuesday, September 5, 2017 3:51 PMJeff Moden - Tuesday, September 5, 2017 2:52 PMEd Wagner - Tuesday, September 5, 2017 2:16 PMJeff 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:
No. Not a joke. He also uses YYYY-00-00 for year notations and stores such notations in tables that way, which violates the very ISO and "Presentation Layer" rules he's ranting about not to mention at least 2 "Best Practices" (Thou shalt not store dates as text and Thou shalt practice what thy preacheth.) 😉
It seems to be his favorite feature of MySQL.
In that case, he's breaking another of his own rules. Not only is that NOT ISO compliant, it sounds like it may also be proprietary to MySQL. How often does he chastise about that?
--Jeff Moden
Change is inevitable... Change for the better is not.
September 5, 2017 at 10:18 pm
jcobb 20350 - Tuesday, September 5, 2017 2:20 PMOk, so let's throw this out the window and get to the real point of what I want to accomplish. I have a review date field. It is in table of supplier id's. Another field in the same table has values: weekly, 2 weeks, 3 weeks, monthly, 2 months, 3 month, 4 months, 6 months and yearly. These are review cycles. I wanted to have a nightly update that looked at the review date to see if it had passed. If so, add the appropriate number of days from the review cycle to populate a new future review date. Such as,CASE WHEN review_date < GETDATE() AND cycle = 'weekly' THEN DATEADD(DD, 7, review_date) ELSE review_date END
I plan of resetting all current review dates before I start the nightly job for the first time. That way I'm not just adding 7 days to a date 8 months ago. After that the job should keep the table updated.I thought a calendar table holding dates would be best and update from that, but I am not going to claim I know what's best. I'm also struggling with the 2 week, 3 week, etc. dates. The dates can always be a Monday because we'll look at the suppliers that need reviewed for the week of x, not a specific date.
Weeks can certainly and painlessly start on Mondays. Calendar months and years aren't so friendly unless... you adopt something like the ISO Week standards. Would those fit the bill or are we relegated to things like the first Monday of a Calendar month or Calendar year?
--Jeff Moden
Change is inevitable... Change for the better is not.
September 6, 2017 at 5:13 am
Jeff Moden - Tuesday, September 5, 2017 10:05 PMLynn Pettis - Tuesday, September 5, 2017 3:51 PMJeff Moden - Tuesday, September 5, 2017 2:52 PMEd Wagner - Tuesday, September 5, 2017 2:16 PMJeff 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:
No. Not a joke. He also uses YYYY-00-00 for year notations and stores such notations in tables that way, which violates the very ISO and "Presentation Layer" rules he's ranting about not to mention at least 2 "Best Practices" (Thou shalt not store dates as text and Thou shalt practice what thy preacheth.) 😉
It seems to be his favorite feature of MySQL.
In that case, he's breaking another of his own rules. Not only is that NOT ISO compliant, it sounds like it may also be proprietary to MySQL. How often does he chastise about that?
Well, it's sick. Perhaps the ranting, berating and double-posting whole paragraphs are to cover up or draw attention away from garbage like this.
September 6, 2017 at 5:32 am
Jeff Moden - Tuesday, September 5, 2017 2:55 PMLynn Pettis - Tuesday, September 5, 2017 2:19 PMJeff Moden - Monday, September 4, 2017 6:30 PMEd 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.
Because way back when and calendars were first getting printed, somebody decided that Sunday should be the first day on the calendar. Just a guess because I didn't feel like Googling it.
Must've been a project manager... "And on the first day, he rested". 😉
It's the difference between Jewish (and Seventh Day Adventist) and Christian traditions.
The Jewish Sabbath is on the 7th day (because "He rested"). The early Christian church changed the worship day to Sunday in honor of the Resurrection on Easter Sunday.
September 6, 2017 at 10:21 am
I think the q/problem is:
given a date, if that date is before/on the first Monday of the current month, use the current month's first Monday,
else, use the first Monday of the next month.
If so, I think this simple calc will do it; definitely no need for a calendar table here.
SELECT
StartDate,
DATEADD(DAY, CASE
WHEN most_recent_monday = StartDate THEN 0
WHEN MONTH(most_recent_monday) <> MONTH(StartDate) THEN 7
ELSE CEILING(DATEDIFF(DAY, most_recent_monday, DATEADD(MONTH, DATEDIFF(MONTH, 0, most_recent_monday) + 1, 0)) / 7.0) * 7
END, most_recent_monday) AS next_first_monday_of_month
FROM (
VALUES(CAST('20170101' AS date)),('20170102'),('20170106'),('20170131'),('20170205'),('20170207')
) AS test_data(StartDate)
CROSS APPLY (
SELECT DATEADD(DAY, -(DATEDIFF(DAY, 0, StartDate) % 7), StartDate) AS most_recent_monday
) AS date_calcs1
Edit:
If you prefer to avoid decimal division in the date calc, we can do this:
DATEDIFF(DAY, most_recent_monday, DATEADD(MONTH, DATEDIFF(MONTH, 0, most_recent_monday) + 1, 0)) / 7 * 7 + 7
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".
September 6, 2017 at 1:04 pm
gvoshol 73146 - Wednesday, September 6, 2017 5:32 AMJeff Moden - Tuesday, September 5, 2017 2:55 PMLynn Pettis - Tuesday, September 5, 2017 2:19 PMJeff Moden - Monday, September 4, 2017 6:30 PMEd 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.
Because way back when and calendars were first getting printed, somebody decided that Sunday should be the first day on the calendar. Just a guess because I didn't feel like Googling it.
Must've been a project manager... "And on the first day, he rested". 😉
It's the difference between Jewish (and Seventh Day Adventist) and Christian traditions.
The Jewish Sabbath is on the 7th day (because "He rested"). The early Christian church changed the worship day to Sunday in honor of the Resurrection on Easter Sunday.
Thanks for the interesting information.
I guess, in the long run, that it doesn't really matter so long as we're all religiously paid on time. 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
September 6, 2017 at 1:13 pm
Jeff Moden - Wednesday, September 6, 2017 1:04 PMThanks for the interesting information.I guess, in the long run, that it doesn't really matter so long as we're all religiously paid on time. 😉
Hahaha, bang on, Jeff!
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
September 6, 2017 at 2:36 pm
Phil Parkin - Wednesday, September 6, 2017 1:13 PMJeff Moden - Wednesday, September 6, 2017 1:04 PMThanks for the interesting information.I guess, in the long run, that it doesn't really matter so long as we're all religiously paid on time. 😉
Hahaha, bang on, Jeff!
Yeah, he hit the nail on the head with that one, didn't he? That's important.
September 6, 2017 at 4:01 pm
Jeff Moden - Tuesday, September 5, 2017 10:18 PMjcobb 20350 - Tuesday, September 5, 2017 2:20 PMOk, so let's throw this out the window and get to the real point of what I want to accomplish. I have a review date field. It is in table of supplier id's. Another field in the same table has values: weekly, 2 weeks, 3 weeks, monthly, 2 months, 3 month, 4 months, 6 months and yearly. These are review cycles. I wanted to have a nightly update that looked at the review date to see if it had passed. If so, add the appropriate number of days from the review cycle to populate a new future review date. Such as,CASE WHEN review_date < GETDATE() AND cycle = 'weekly' THEN DATEADD(DD, 7, review_date) ELSE review_date END
I plan of resetting all current review dates before I start the nightly job for the first time. That way I'm not just adding 7 days to a date 8 months ago. After that the job should keep the table updated.I thought a calendar table holding dates would be best and update from that, but I am not going to claim I know what's best. I'm also struggling with the 2 week, 3 week, etc. dates. The dates can always be a Monday because we'll look at the suppliers that need reviewed for the week of x, not a specific date.
Weeks can certainly and painlessly start on Mondays. Calendar months and years aren't so friendly unless... you adopt something like the ISO Week standards. Would those fit the bill or are we relegated to things like the first Monday of a Calendar month or Calendar year?
I'm not stuck on Mondays. Weeks would work as well.
Viewing 15 posts - 16 through 30 (of 43 total)
You must be logged in to reply to this topic. Login to reply