Date Manipulations

Question

Post reply

Date Manipulations

sb_rs

Newbie

Points: 5
More actions
March 15, 2008 at 5:23 am

#188047

Hi,
I have a situation where I need to determine the date of the second saturday of March of a year. How to do that?
For example, for 2008, it should give 9 (as the second sunday of March this is is 9th). For 2009 it should give 8 (as the second sunday of March next year would be the 8th of March), for 2010 it should give 14 (as the second sunday of March in year 2010 is the 14th of March) etc.
Thanks in advance
RS

Viewing 5 posts - 1 through 5 (of 5 total)

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

Jack Corbett SSC Guru Points: 184394 More actions · Answer 1

One way you could do it something like this:

With DayOfWeek As

(

Select

DateName(DW, date) as DayOfWeek,

date

From

dates

Where

date between '2008/03/08' and '2008/03/14'

)

Select

date

From

DayOfWeek

Where

DayofWeek = 'Saturday'

or

With DayOfWeek As

(

Select

DateName(DW, date) as DayOfWeek,

date,

DatePart(DD, date) as DayNum,

DatePart(mm, date) as MonthNum,

From

dates

)

Select

date as DateOfSecondSaturday

From

DayOfWeek

Where

DayofWeek = 'Saturday' And

DayNum between 8 and 14 And

MonthNum = 3

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

Jeff Moden SSC Guru Points: 1004642 More actions · Answer 2

Ummm... just in case you don't have a "Dates" table like what Jack used, please consider the information at the following URL...

http://sqlserver2000.databases.aspfaq.com/why-should-i-consider-using-an-auxiliary-calendar-table.html

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jack Corbett SSC Guru Points: 184394 More actions · Answer 3

Actually Jeff, I did not mean to imply that there was a dates table. The OP did not post any table or column names so I just used dates as a generic name. Now a dates table might be the best solution for this.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

Jeff Moden SSC Guru Points: 1004642 More actions · Answer 4

Oh... ok. Thanks Jack.

Hey, do you have a dates table? I've got a neat trick to show you if you do...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)