Determine if a date is a Friday

Question

Post reply

Determine if a date is a Friday

Arif Hafeez-Imam

SSChasing Mays

Points: 631
More actions
October 20, 2009 at 7:07 am

#72999

Hi,
I need to set a variable to 1 of 2 values, I know i can acomplish this using the IF statement however 1 of the values will only be set if the day is a Friday, any other day would the other value. Does anyone know how I can determine the day of a date so that if it is a Friday then value2 will be set else then value1 will be set please?
Hope this makes sense.
Arif
😀

Viewing 8 posts - 1 through 7 (of 7 total)

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

Dave Ballantyne SSC-Dedicated Points: 33667 More actions · Answer 1

you need "datepart(dw,"

Check BOL for more details

http://msdn.microsoft.com/en-us/library/ms174420.aspx

Clear Sky SQL
My Blog[/url]

GSquared SSC Guru Points: 260824 More actions · Answer 2

If I'm reading your request correctly, you might want to use something like this:

declare @Variable sql_variant;

select @Variable =

case

when datepart(weekday, getdate()) = 6 then FridayValue

else NonFridayValue

end;

There can be problems with datepart weekday, so I recommend looking into using a more robust method.

1 Jan 2000 was a Saturday. This means if we divide the number of days since then by 7, we can find the day of the week by the remainder (modulus) of the division.

select datediff(day, '1/1/2000', getdate())%7;

If that is 0, the date is a Saturday, 1 = Sunday, 2 = Monday, 3 = Tuesday, etc.

Since it's possible to change a server setting and change how datepart(weekday) works, using the mathematical method is more certain.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Arif Hafeez-Imam SSChasing Mays Points: 631 More actions · Answer 3

thanks Dave for your rapid reply.

All sorted now!!

:hehe:

Arif Hafeez-Imam SSChasing Mays Points: 631 More actions · Answer 4

Thanks GSquared, only just read your post again very useful for the reports I am generating.

Thanks both.

GSquared SSC Guru Points: 260824 More actions · Answer 5

You're welcome.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

andrewd.smith SSCrazy Eights Points: 8444 More actions · Answer 6

The trouble with DATEPART is that it depends on the value of @@DATEFIRST.

The value of this expression:

DATEDIFF(day, '17530101', @TestDate) % 7 + 1

will always be 5 if the value of @TestDate (datetime) is a Friday. This is independent of the value of @@DATEFIRST. Alternatively, you could also use the DATENAME function.

DECLARE @dt datetime

SELECT @dt = '20091016' /* Last Friday */

SET DATEFIRST 1

SELECT DATEPART(dw, @dt) AS [DatePart],

DATENAME(dw, @dt) AS [DateName],

DATEDIFF(day, '17530101', @dt) % 7 + 1 AS [DateDiffModulus]

SET DATEFIRST 7

SELECT DATEPART(dw, @dt) AS [DatePart],

DATENAME(dw, @dt) AS [DateName],

DATEDIFF(day, '17530101', @dt) % 7 + 1 AS [DateDiffModulus]

By the way, '17530101' represents the earliest valid datetime value.

EDIT: beaten to it by GSquared.

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 7

The value returned by the DATENAME function is dependant on the language setting of the connection; the DATEDIFF method is independent of language.