How to find Last Friday without using sp

Question

Post reply

How to find Last Friday without using sp

leezhang

Valued Member

Points: 68
More actions
August 18, 2005 at 10:09 am

#90562

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!

Viewing 15 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply

Roy Rogers SSCertifiable Points: 7712 More actions · Answer 1

~~CREATE VIEW myView~~

AS

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

noeld SSC Guru Points: 96590 More actions · Answer 2

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

Roy Rogers SSCertifiable Points: 7712 More actions · Answer 3

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

Roy Rogers SSCertifiable Points: 7712 More actions · Answer 4

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

leezhang Valued Member Points: 68 More actions · Answer 5

leezhang

Valued Member

Points: 68

August 18, 2005 at 11:37 am

#583221

Govinn,

Thanks! Your script works for me.

Roy Rogers SSCertifiable Points: 7712 More actions · Answer 6

Roy Rogers

SSCertifiable

Points: 7712

August 18, 2005 at 12:28 pm

#583244

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

David A. Long SSCrazy Eights Points: 8421 More actions · Answer 7

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

David A. Long SSCrazy Eights Points: 8421 More actions · Answer 8

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

Scott Coleman One Orange Chip Points: 27451 More actions · Answer 9

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

&nbsp x

) y

leezhang Valued Member Points: 68 More actions · Answer 10

leezhang

Valued Member

Points: 68

August 19, 2005 at 11:18 am

#583635

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!

Scott Coleman One Orange Chip Points: 27451 More actions · Answer 11

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&gt, 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)".

gamma Valued Member Points: 58 More actions · Answer 12

gamma

Valued Member

Points: 58

August 19, 2005 at 3:05 pm

#583717

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

leezhang Valued Member Points: 68 More actions · Answer 13

leezhang

Valued Member

Points: 68

August 22, 2005 at 8:26 am

#583939

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!

Scott Coleman One Orange Chip Points: 27451 More actions · Answer 14

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

&nbsp x

) y