Return the day value of a date

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Return the day value of a date

Author

Topic

Bex
Aged Yak Warrior

580 Posts

Posted - 2005-03-11 : 07:25:56

Afternoon

I have a sproc that generates maildates for records loaded into the database each week day. Depending on the type of letter, each document will be mailed that day, the following month or will have a 2 day delay. I am using a CASE statement to determine how to set the mail date.

However, for those with the 2day delay, those that are received on a thursday or a friday will have a mail date that falls on a saturday or sunday, which is not possible. Therefore, I want to treat the 2day delays differently for thursdays and fridays. However, i cannot seem to find a function that returns a character string or integer that represents the specific day of the week.

I have tried DAY(GETDATE()), DATENAME(DAY, GETDATE()) but these return the day of the month: 13 as opposed to 'Friday' (or an integer representing friday(depending on what DATEFIRST is set to).

Can anyone help?

Here is the code for setting the mail date parameter:

SET @mailDate = CASE (SELECT Frequency FROM SendFrequency F INNER JOIN LetterCode L ON F.SendFrequencyID = L.SendFrequencyID WHERE LetterCode = @letterCode)
	 		WHEN 'Daily' THEN @todaysDate
			WHEN '2Day Delay' THEN DATEADD(DAY, 2,@todaysDate)
			WHEN 'Monthly' THEN  CASE (SELECT DATEPART(MONTH, @todaysDate))
									WHEN 2 THEN  '16 March 2005'
									WHEN 3 THEN  '16 April 2005'
									WHEN 4 THEN  '14 May 2005'
									WHEN 5 THEN  '18 June 2005'
									WHEN 6 THEN  '16 July 2005'
									WHEN 7 THEN  '20 August 2005'
									WHEN 8 THEN  '17 September 2005'
									WHEN 9 THEN  '17 October 2005'
									WHEN 10 THEN  '19 November 2005'
									WHEN 11 THEN  '17 December 2005'
						END
		  END

What I want to say is something like:

CASE WHEN @todaysDate ='Thursday' THEN DATEADD(DAY, 4,@mailDate)

WHEN @todaysDate ='Friday' THEN DATEADD(DAY, 4,@mailDate)

What function can I use to do the above??

Thanks in advance

Hearty head pats

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-03-11 : 07:36:25

select datename(dw, getdate()) gives you the the name of the day.
BOL is your best friend.

Go with the flow & have fun! Else fight the flow

Bex
Aged Yak Warrior

580 Posts

Posted - 2005-03-11 : 07:52:02

Hey Spirit

Hope you are well and dandy!! Thanks for the info! DOH! I tried DAY but not weekday, how stupid am I???? Yeah, BOL is the bollards, although sometimes it is hard to find what you are looking for if you don't know the solution, if you know what I mean!

Anyway, have a smashing weekend!

Hearty head pats

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-03-11 : 08:00:08

no worries bexy.

we all have those days.

Go with the flow & have fun! Else fight the flow

Bex
Aged Yak Warrior

580 Posts

Posted - 2005-03-11 : 08:02:00

LOL, Thanks, but some people (like myself) tend to have them more than others. Ho hum, thank god for the weekend eh?

Hearty head pats

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-03-11 : 08:12:29