How to find Last Friday without using sp
-
I need to create a view that lists events based on day of a week (I don't want to use stored procedures). If current date = event day (First Monday or last Friday of the month), then the view shows the event. Does anyone have some good ideas/script on this? Thnx in advance!
-
CREATE VIEW myViewAS
SELECT CASE WHEN (DATEPART(DAY,GETDATE()) < 8 AND DATENAME(WEEKDAY,GETDATE()) = 'Monday')
OR
(DATEPART(DAY, DATEADD(DAY, 7, GETDATE())) < 8 AND DATENAME(WEEKDAY,GETDATE()) = 'Friday')
THEN 'EVENT'
ELSE
NULL
END
To list all dates you must have list of dates in a table or Create a function to return the dates and use that function in the view.
Regards,
gova -
Not sure again I am guessing govinn meant to put those expressions as the predicate of the where clause!
SELECT * FROM TABLE WHERE
CASE WHEN (DATEPART(DAY,GETDATE()) < 8 AND DATENAME(WEEKDAY,GETDATE()) = 'Monday')
OR
(DATEPART(DAY, DATEADD(DAY, 8, GETDATE())) > 7 AND DATENAME(WEEKDAY,GETDATE()) = 'Friday')
END
* Noel -
Yes Noel considering if we have a table @MyDates with dates. The Select statement can be in a view.
SET NOCOUNT ON
DECLARE @myDates TABLE
(
myDate DATETIME
)
DECLARE @AA INT SET @AA = 0
WHILE @AA < 365
BEGIN
INSERT @myDates VALUES (DATEADD(DAY, @AA, '01/01/2005'))
SET @AA = @AA + 1
END
SELECT myDate,
MyDay = DATENAME(WEEKDAY, myDate),
Event = CASE WHEN (DATEPART(DAY,myDate) < 8 AND DATENAME(WEEKDAY,myDate) = 'Monday')
OR
(DATEPART(DAY, DATEADD(DAY, 7, myDate)) < 8 AND DATENAME(WEEKDAY,myDate) = 'Friday')
THEN 'EVENT'
ELSE
NULL
END
FROM
@myDates
Regards,
gova -
Here is the view with function
SET NOCOUNT ON
GO
CREATE FUNCTION myDates()
RETURNS @myDates TABLE
(
myDate DATETIME
)
AS
BEGIN
DECLARE @AA INT SET @AA = 0
WHILE @AA < 365
BEGIN
INSERT @myDates VALUES (DATEADD(DAY, @AA, '01/01/2005'))
SET @AA = @AA + 1
END
RETURN
END
GO
CREATE VIEW vw_myDates
AS
SELECT myDate,
MyDay = DATENAME(WEEKDAY, myDate),
Event = CASE WHEN (DATEPART(DAY,myDate) < 8 AND DATENAME(WEEKDAY,myDate) = 'Monday')
OR
(DATEPART(DAY, DATEADD(DAY, 7, myDate)) < 8 AND DATENAME(WEEKDAY,myDate) = 'Friday')
THEN 'EVENT'
ELSE
NULL
END
FROM
myDates()
GO
SELECT * FROM vw_myDates
WHERE
Event IS NOT NULL
DROP FUNCTION myDates
DROP VIEW vw_myDates
Regards,
gova -
Govinn,
Thanks! Your script works for me.
-
You are welcome(d).
Function returning dates is hard coded for year 2005. You might want to change it to the dates you need.
Regards,
gova -
Check this out:
DECLARE @Cnt int
SET @Cnt = -1
WHILE @Cnt < 6
BEGIN
SELECT @Cnt, CAST(@Cnt AS datetime)
, DATEADD(wk,DATEDIFF(wk,@Cnt,GETDATE()),@Cnt)
, DATENAME(dw,DATEADD(wk,DATEDIFF(wk,@Cnt,GETDATE()),@Cnt))
SET @Cnt = @Cnt + 1
END
I do not know who figured this out but it has to do with
the zero date = 1900-01-01, which will always be a Monday,
here is your reference point, so use:
DATEADD(wk,DATEDIFF(wk,4,MyDate),4)
to find the closest Friday of the given MyDate
Andy
-
Here is one way of calculating the First Monday Of the Month and Last Friday Of the Month for @MyDate
DECLARE @MyDate datetime, @FDMo datetime, @LDMo datetime
SET @MyDate = '20050918'
SELECT @FDMo = LEFT(CONVERT(varchar,@MyDate,112),6)+'01'
, @LDMo = DATEADD(dd,-1,DATEADD(mm,1,@FDMo))
SELECT @MyDate AS MyDate, @FDMo AS FirstDateOfMonth, @LDMo AS LastDateOfMonth
SELECT CASE WHEN DATEPART(dw, @FDMo)>2 THEN -- need to adjust for next week
DATEADD(wk,DATEDIFF(wk,0,DATEADD(ww,1,@FDMo)),0)
ELSE DATEADD(wk,DATEDIFF(wk,0,@FDMo),0) END AS FirstMondayOfMonth
, CASE WHEN DATEPART(dw, @LDMo)<6 THEN -- need to adjust for previous week
DATEADD(wk,DATEDIFF(wk,4,DATEADD(ww,-1,@LDMo)),4)
ELSE DATEADD(wk,DATEDIFF(wk,4,@LDMo),4) END AS LastFridayOfMonth
Andy
-
select TheDate,
dateadd(d, 6 - ((cast(floor(cast(FirstDayOfMonth as float)) as integer) - 1) % 7), FirstDayOfMonth) as FirstMondayOfMonth,
dateadd(d, -(cast(floor(cast(LastDayOfMonth as float)) as integer) + 3) % 7, LastDayOfMonth) as LastFridayOfMonth
from (
select TheDate, dateadd(d, 1-day(TheDate), TheDate) as FirstDayOfMonth,
dateadd(d, -day(TheDate), dateadd(m, 1, TheDate)) as LastDayOfMonth
from (
select cast('1/1/2005' as datetime) as TheDate
union all select cast('2/28/2005' as datetime) as TheDate
union all select cast('3/15/2005' as datetime) as TheDate
union all select cast('4/30/2005' as datetime) as TheDate
union all select cast('5/1/2005' as datetime) as TheDate
union all select cast('6/21/2005' as datetime) as TheDate
union all select cast('7/4/2005' as datetime) as TheDate
union all select cast('8/19/2005' as datetime) as TheDate
union all select cast('9/29/2005' as datetime) as TheDate
union all select cast('10/5/2005' as datetime) as TheDate
union all select cast('11/25/2005' as datetime) as TheDate
union all select cast('12/25/2005' as datetime) as TheDate
 
x) y
-
Scott,
You query works great! Just out of curiosity, when you cast(date) as float, what does that float number mean? I would like to know why you need to add 3, etc.
Thanks!
-
Converting a datetime to a float gives you a number in days (the fractional part is the time), and floor() will truncate it to a pure date. You get an error if you try floor(<datetime>
, you have to explicitly cast the datetime to a float to use floor().Datetime values start at 1/1/1900 (a Monday), so (date mod 7) returns a number between 0 and 6 that represents the day of the week (Mon = 0, Tue = 1, ..., Sun = 6). To calculate the date of the previous or next Monday, Friday, etc. you offset the given date by some variation of (date mod 7).
The function "date - (date mod 7)" will correct any date to the previous Monday. To get a different day of the week you shift the sequence before using the mod function. Monday is three days after Friday, so the previous Friday function is "date - ((date + 3) mod 7)".
The function "date + 6 - (date mod 7)" will correct any date to the next Sunday. Shifting this by one day gives you a next Monday function of "date + 6 - ((date + 1) mod 7)".
-
We need to find the first Monday for the month next to target one and then subtract 3 days.
declare @d datetime--any date within target month
set @d=getdate()
select LastFriday=dateadd(dd,-3,dateadd(wk,datediff(wk,0,dateadd(mm,datediff(mm,0,@d)+1,0)),0))
Regards,
Leonid
-
Leonid,
Your script is simple and works for most of the months except for September, 2005. It returns 9/23/05 instead of 9/30 as the last Friday of the month.
Thanks!
-
I'm not sure who Leonid is, but over the weekend I realized the script I posted would not work for some dates. (Tells you how exciting my weekends are.)
Here is the correction:
select TheDate,
dateadd(d, 6 - ((cast(floor(cast(FirstDayOfMonth as float)) as integer) - 1) % 7), FirstDayOfMonth) as FirstMondayOfMonth,
dateadd(d, -(cast(floor(cast(LastDayOfMonth as float)) as integer) + 3) % 7, LastDayOfMonth) as LastFridayOfMonth
from (
select TheDate, dateadd(d, 1-day(TheDate), TheDate) as FirstDayOfMonth,
dateadd(d, 1-day(TheDate), dateadd(m, 1, TheDate))-1 as LastDayOfMonth
from (
select cast('1/1/2005' as datetime) as TheDate
union all select cast('2/28/2005' as datetime) as TheDate
union all select cast('3/15/2005' as datetime) as TheDate
union all select cast('4/30/2005' as datetime) as TheDate
union all select cast('5/1/2005' as datetime) as TheDate
union all select cast('6/21/2005' as datetime) as TheDate
union all select cast('7/4/2005' as datetime) as TheDate
union all select cast('8/19/2005' as datetime) as TheDate
union all select cast('9/29/2005' as datetime) as TheDate
union all select cast('10/5/2005' as datetime) as TheDate
union all select cast('11/25/2005' as datetime) as TheDate
union all select cast('12/25/2005' as datetime) as TheDate
 
x) y
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply