Get all Sundays between 2 dates in sql
-
Hi all, I want to get all the sundays between 2 dates in sql. Please help.. Thanks and regards,
CST
-
Hi all, I want to get all the sundays between 2 dates in sql. Please help.. Thanks and regards,
CST
-
Hi all, I want to get all the sundays between 2 dates in sql. Please help.. Thanks and regards,
CST
I'm going to say a bunch of stuff here that ASSUMES SQL Server uses the same date system that Microsoft Offices uses. If that's wrong, then what I say here won't apply. Microsoft Office uses date serial number zero to (sort of) represent Saturday, Dec 30, 1899 (actually, their system is broken below serial number 61 because they assume the year 1900 was a leap year, which it was not -- but that doesn't affect this discussion). Thus, the date serial number modulo 7 gives you the day of the week, counting Sunday as day #1. Taking the interval between two dates, and dividing by 7 gets the number of weeks (and most weeks have one Sunday), then you need to correct for the beginning and end dates in case you get a fraction of a week, or the beginning or end dates are Sunday. numSundays( date1, date2 ) { dayOfWeek1 = MOD(date1,7) dayOfWeek2 = MOD(date2,7) numSundays = (date2-date1)/7 If(dayOfWeek2 < dayOfWeek1) numSundays++ ElseIf(dayOfWeek2=1 OR dayOfWeek1=1) numSundays++ } This should give you the number of Sundays INCLUSIVE (includes both start and end dates). BUT, like I said earlier: won't work if SQL Server doesn't use the same dating system as MS Office. Hope this helps.
-
Hi all, I want to get all the sundays between 2 dates in sql. Please help.. Thanks and regards,
CST
I re-read your post, and realized I hadn't really answered your question in my first response. You asked how to do it in SQL, and you didn't specify which flavor. I answered in pseudo code; not SQL. Here is another shot at it, this time putting the previous pseudo code into SQL. I looked thru the (XOpen) SQL standard, and couldn't find the functions I was looking for, so had to settle for generic functions that you will have to substitute for if your system doesn't support them. In both the procedures below I've assumed: 1) the way to get an integer divide is with the '\' operator. This works in Access, but is not universal across DBMs. 2) the whole part of a date interval is actually the number of days between the two dates. The standard does NOT specify this. 3) the existence of a "WEEKDAY" function. 4) that date2 >= date1 5) that you specify a DATE-LITERAL by delimiting it with # signs. Note that Jan 2, 2000 was a Sunday. First, a procedure that uses ISO SQL standard CASE notation: CREATE PROCEDURE numSundays(date1 DATE, date2 DATE) AS SELECT CINT(date2 - date1) \ 7 + CASE WEEKDAY(date2) < WEEKDAY(date1) WHEN TRUE THEN 1 ELSE CASE WEEKDAY(date2) = WEEKDAY(#JAN-02-2000#) OR WEEKDAY(date1) = WEEKDAY(#JAN-02-2000#) WHEN TRUE THEN 1 ELSE 0 END END ; And here is the same thing in MS Access notation. CREATE PROCEDURE numSundays(date1 DATE, date2 DATE) AS SELECT CINT(date2 - date1) \ 7 + IIF( WEEKDAY(date2) < WEEKDAY(date1), 1, IIF( WEEKDAY(date2) = WEEKDAY(#JAN-02-2000#) OR WEEKDAY(date1) = WEEKDAY(#JAN-02-2000#), 1, 0 )) ; While more complicated than a looping approach to counting the number of Sundays, they are much more efficient as they are effectively only a simple formula. Hope this helps -- modified at 16:35 Tuesday 17th April, 2007
-
I re-read your post, and realized I hadn't really answered your question in my first response. You asked how to do it in SQL, and you didn't specify which flavor. I answered in pseudo code; not SQL. Here is another shot at it, this time putting the previous pseudo code into SQL. I looked thru the (XOpen) SQL standard, and couldn't find the functions I was looking for, so had to settle for generic functions that you will have to substitute for if your system doesn't support them. In both the procedures below I've assumed: 1) the way to get an integer divide is with the '\' operator. This works in Access, but is not universal across DBMs. 2) the whole part of a date interval is actually the number of days between the two dates. The standard does NOT specify this. 3) the existence of a "WEEKDAY" function. 4) that date2 >= date1 5) that you specify a DATE-LITERAL by delimiting it with # signs. Note that Jan 2, 2000 was a Sunday. First, a procedure that uses ISO SQL standard CASE notation: CREATE PROCEDURE numSundays(date1 DATE, date2 DATE) AS SELECT CINT(date2 - date1) \ 7 + CASE WEEKDAY(date2) < WEEKDAY(date1) WHEN TRUE THEN 1 ELSE CASE WEEKDAY(date2) = WEEKDAY(#JAN-02-2000#) OR WEEKDAY(date1) = WEEKDAY(#JAN-02-2000#) WHEN TRUE THEN 1 ELSE 0 END END ; And here is the same thing in MS Access notation. CREATE PROCEDURE numSundays(date1 DATE, date2 DATE) AS SELECT CINT(date2 - date1) \ 7 + IIF( WEEKDAY(date2) < WEEKDAY(date1), 1, IIF( WEEKDAY(date2) = WEEKDAY(#JAN-02-2000#) OR WEEKDAY(date1) = WEEKDAY(#JAN-02-2000#), 1, 0 )) ; While more complicated than a looping approach to counting the number of Sundays, they are much more efficient as they are effectively only a simple formula. Hope this helps -- modified at 16:35 Tuesday 17th April, 2007
-
while(date1<>date2) begin if(datepart(dw, date1)=sunday) begin @sundaycount = @sundaycount +1 end DATEADD ( dd , 1, date1 ) end print @sundaycount
Gautham
-
////////////////////// Table Name ='sysatt' Column Name ='DtAtt' ///////////////////// select DtAtt FROM sysatt WHERE DtAtt BETWEEN '2007-01-01' AND '2007-04-17' AND DATENAME(Dw,DtAtt)='Sunday'
vivek