November 11, 2011 at 9:28 am
For example:
declare @CalendarDate smalldatetime
select @CalendarDate = '11/04/2011'
-- to return 10/29/2011 --previous Sat
select @CalendarDate = '11/05/2011'
-- to return 11/05/2011 today, as a Sat
select @CalendarDate = '11/06/2011'
-- to return 11/05/2011 yesterday, Sat
select @CalendarDate = '11/07/2011'
-- to return 11/05/2011 two days ago, Sat
Thanks.
November 11, 2011 at 9:40 am
How about this?
; with numberofdays(n) as
( select 0 union all select 1 union all
select 2 union all select 3 union all
select 4 union all select 5 union all
select 6
)
,Weekdays(Dts) as
( select DATEADD(DD, -n , @CalendarDate) from numberofdays
)
select top 1 Dts
from Weekdays
where DATENAME(DW,dts) ='Saturday'
order by Dts desc
November 11, 2011 at 9:42 am
Try this: -
SELECT DATEADD(DAY, DATEDIFF(DAY, 5, @calenderDate) /7 * 7, 5)
DECLARE @calenderDate SMALLDATETIME
SET @calenderDate = '2011-11-04'
SELECT DATEADD(DAY, DATEDIFF(DAY, 5, @calenderDate) /7 * 7, 5)
SET @calenderDate = '2011-11-05'
SELECT DATEADD(DAY, DATEDIFF(DAY, 5, @calenderDate) /7 * 7, 5)
SET @calenderDate = '2011-11-06'
SELECT DATEADD(DAY, DATEDIFF(DAY, 5, @calenderDate) /7 * 7, 5)
SET @calenderDate = '2011-11-07'
SELECT DATEADD(DAY, DATEDIFF(DAY, 5, @calenderDate) /7 * 7, 5)
November 11, 2011 at 9:46 am
Also try creating calendar table in your database which is handy if you need to use lot of date criteria. I use when i need to do cross apply when there is no data for specific month or week and i need to show 0.
This link shows the benefits of calendar table and how to use it.
November 11, 2011 at 9:58 am
+1 on the calendar table.
It's not like this is a hard query to write =>
SELECT TOP 1
*
FROM
dbo.Calendar C
WHERE
C.M = MONTH(DATEADD(M , -1 , GETDATE()))
AND C.Y = YEAR(DATEADD(M , -1 , GETDATE()))
AND C.DW = 7
ORDER BY dt
My calendar table => http://www.sqlservercentral.com/Forums/Attachment8839.aspx
Just change the primary group to your own.
November 11, 2011 at 9:59 am
Thanks to all, that's great. Yes, I'm storing these dates in a table for future use.
Last one, a variation on the same, this time to find future Fridays.
select @CalendarDate = '11/03/2011'
--11/04/2011 --tommorrow, as Fri
select @CalendarDate = '11/04/2011'
-- 11/04/2011 --today, as Fri
select @CalendarDate = '11/05/2011'
-- 11/11/2011, -- next Fri
November 11, 2011 at 10:00 am
Cadavre (11/11/2011)
Try this: -
SELECT DATEADD(DAY, DATEDIFF(DAY, 5, @calenderDate) /7 * 7, 5)
This doesn't work for dates falling on Saturday, it will give you the current Saturday rather than the previous Saturday. The following code will work.
SELECT DATEADD(wk, DATEDIFF(wk, -1, @calendarDate), -2)
-1 is SUNDAY 1899-12-31 and -2 is SATURDAY 1899-12-30
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
November 11, 2011 at 10:03 am
One way is to calculate the number of days that passed since your date, then divide it by seven and multiply it by seven. Since the function datediff returns an int, when you divide it by 7, you’ll get the number of weeks that passed since that date. You have to make sure that Saturday is day number 7, so I added the set datefirst statement.
SET DATEFIRST 1
declare @CalendarDate smalldatetime
select @CalendarDate = '11/04/2011'
-- to return 10/29/2011 --previous Sat
select DATEADD(dd,datediff(dd,'20000101', @CalendarDate) / 7 * 7, '20000101'), @CalendarDate
select @CalendarDate = '11/05/2011'
-- to return 11/05/2011 today, as a Sat
select DATEADD(dd,datediff(dd,'20000101', @CalendarDate) / 7 * 7, '20000101'), @CalendarDate
select @CalendarDate = '11/06/2011'
-- to return 11/05/2011 yesterday, Sat
select DATEADD(dd,datediff(dd,'20000101', @CalendarDate) / 7 * 7, '20000101'), @CalendarDate
select @CalendarDate = '11/07/2011'
-- to return 11/05/2011 two days ago, Sat
select DATEADD(dd,datediff(dd,'20000101', @CalendarDate) / 7 * 7, '20000101'), @CalendarDate
PS. I Noticed that this way was already written when I hit the reply. Most times I cancel my post when it happens, but this time I post it because I think that in such case it is important to make sure that the solution will work regardless of the server configuration. By adding the set datefirst statement, I made sure of that. If you don't add it, then it might work on some servers and not work on other servers.
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
November 11, 2011 at 10:06 am
Ninja's_RGR'us (11/11/2011)
+1 on the calendar table.It's not like this is a hard query to write =>
SELECT TOP 1*
FROM
dbo.Calendar C
WHERE
C.M = MONTH(DATEADD(M , -1 , GETDATE()))
AND C.Y = YEAR(DATEADD(M , -1 , GETDATE()))
AND C.DW = 7
ORDER BY dt
My calendar table => http://www.sqlservercentral.com/Forums/Attachment8839.aspx
Just change the primary group to your own.
Seriously, unless you enjoy the headache of the math timewarps(), calendar table is a super easy way to do this!
November 11, 2011 at 10:11 am
You're right drew, didn't think about that. This works though: -
SELECT DATEADD(DAY, DATEDIFF(DAY, 6, @calenderDate) /7 * 7, 5)
November 11, 2011 at 10:38 am
Adi Cohn-120898 (11/11/2011)
One way is to calculate the number of days that passed since your date, then divide it by seven and multiply it by seven. Since the function datediff returns an int, when you divide it by 7, you’ll get the number of weeks that passed since that date. You have to make sure that Saturday is day number 7, so I added the set datefirst statement.
The reason that your result produces a Saturday is that your reference date 2000-01-01 is a Saturday. It has absolutely nothing to do with the value of DATEFIRST, because your calculations make no reference to the dw date part. You could set DATEFIRST to any legal value and it would produce the same results.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
November 11, 2011 at 10:47 am
drew.allen (11/11/2011)
Adi Cohn-120898 (11/11/2011)
One way is to calculate the number of days that passed since your date, then divide it by seven and multiply it by seven. Since the function datediff returns an int, when you divide it by 7, you’ll get the number of weeks that passed since that date. You have to make sure that Saturday is day number 7, so I added the set datefirst statement.The reason that your result produces a Saturday is that your reference date 2000-01-01 is a Saturday. It has absolutely nothing to do with the value of DATEFIRST, because your calculations make no reference to the dw date part. You could set DATEFIRST to any legal value and it would produce the same results.
Drew
Sorry for that. I was doing something at first using the function datepart and dw. The set firstdate is not important in the way that I was using at the end. Just left it there without thinking. My bad:(
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply