First day of april in a given year

Question

Post reply

First day of april in a given year

var05

Hall of Fame

Points: 3011
More actions
June 11, 2013 at 7:56 am

#278876

Hi All,
Could any one please let me know the first Monday of april provided the year is given.
Say year=2013, how to find first Monday of april in year 2013?
Any help in this pls.
Thanks!

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

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

Matthew Darwin SSCertifiable Points: 5513 More actions · Answer 1

SELECT DATEPART(weekday,DATEADD(mm, 3, '2013'))

This will return the day of the week, where 1 = Sunday, 2 = Monday etc.

Bear in mind that if you are only passing in the year as above, that has to be a string. It's probably better to pass it in as a date datatype.

Follow me on twitter @EvoDBACheck out my blog Natural Selection DBA[/url]

Richard Warr SSCertifiable Points: 6963 More actions · Answer 2

Hi,

I'm struggling to think of a year when it wasn't April 1st.

Joking aside, can you clarify what you need here? Is it April 1st typed as a DATE or the weekday corresponding to that date. Or is it the first working day in April?

Thanks

LutzM SSC Guru Points: 107049 More actions · Answer 3

I'm not sure If I really understood your question: but the first day of April in any given year is always April, 1st.

What are you looking for? The Day of Week, Day of Year or ...?

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

UconnDBA Default port Points: 1439 More actions · Answer 4

If you are looking for the day name, this will return "Monday":

select datename (dw, '2013-04-01')

John

var05 Hall of Fame Points: 3011 More actions · Answer 5

Hi All,

Sorry to be unclear

I just wanna find first monday of april given a date or year.

Say if date='2013-02-02' or year='2013',

then my query should find the First Monday of april i.e '2013-04-01'

If date ='2014-08-08',

then I need '2014-04-07'(First Monday of April month)

Hope Im clear.Thanks!

Sean Lange SSC Guru Points: 286573 More actions · Answer 6

This should do it.

declare @MyDate datetime = '2013-03-08'

--set @MyDate = '2014-08-08'

set @MyDate = dateadd(month, 3, dateadd(yy, datediff(yy, 0, @MyDate), 0)) --gets April 1st for the current year.

select dateadd(wk, datediff(wk, 0, dateadd(dd, 6 - datepart(day, @MyDate), @MyDate)), 0)

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 7

Sean Lange (6/11/2013)
This should do it.
declare @MyDate datetime = '2013-03-08'
--set @MyDate = '2014-08-08'
set @MyDate = dateadd(month, 3, dateadd(yy, datediff(yy, 0, @MyDate), 0)) --gets April 1st for the current year.
select dateadd(wk, datediff(wk, 0, dateadd(dd, 6 - datepart(day, @MyDate), @MyDate)), 0)

He isn't looking for April 1st of any given year, he wants the first Monday in April of any given year.

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

This works by finding the 7th day of April for a given year, and then finding the Monday on or before that date.

select

a.DT,

FirstMondayInApril =

dateadd(dd,(datediff(dd,'17530101',dateadd(yy,datediff(yy,'17530407',a.DT),'17530407'))/7)*7,'17530101')

from

( -- Test Dates

select DT = getdate()union all

select DT = dateadd(yy,1,getdate())union all

select DT = dateadd(yy,2,getdate())union all

select DT = dateadd(yy,3,getdate())union all

select DT = dateadd(yy,4,getdate())union all

select DT = dateadd(yy,5,getdate())union all

select DT = dateadd(yy,6,getdate())union all

select DT = dateadd(yy,7,getdate())union all

select DT = dateadd(yy,8,getdate())union all

select DT = dateadd(yy,9,getdate())

) a

Results:

DT FirstMondayInApril

----------------------- -----------------------

2013-06-11 11:07:48.610 2013-04-01 00:00:00.000

2014-06-11 11:07:48.610 2014-04-07 00:00:00.000

2015-06-11 11:07:48.610 2015-04-06 00:00:00.000

2016-06-11 11:07:48.610 2016-04-04 00:00:00.000

2017-06-11 11:07:48.610 2017-04-03 00:00:00.000

2018-06-11 11:07:48.610 2018-04-02 00:00:00.000

2019-06-11 11:07:48.610 2019-04-01 00:00:00.000

2020-06-11 11:07:48.610 2020-04-06 00:00:00.000

2021-06-11 11:07:48.610 2021-04-05 00:00:00.000

2022-06-11 11:07:48.610 2022-04-04 00:00:00.000

var05 Hall of Fame Points: 3011 More actions · Answer 9

var05

Hall of Fame

Points: 3011

June 11, 2013 at 9:15 am

#1623006

Thank u...it works:)

Sean Lange SSC Guru Points: 286573 More actions · Answer 10

Lynn Pettis (6/11/2013)
Sean Lange (6/11/2013)
This should do it.
declare @MyDate datetime = '2013-03-08'
--set @MyDate = '2014-08-08'
set @MyDate = dateadd(month, 3, dateadd(yy, datediff(yy, 0, @MyDate), 0)) --gets April 1st for the current year.
select dateadd(wk, datediff(wk, 0, dateadd(dd, 6 - datepart(day, @MyDate), @MyDate)), 0)
He isn't looking for April 1st of any given year, he wants the first Monday in April of any given year.

It sounds to me like that is exactly what he is after.

I just wanna find first monday of april given a date or year.
Say if date='2013-02-02' or year='2013',
then my query should find the First Monday of april i.e '2013-04-01'
If date ='2014-08-08',
then I need '2014-04-07'(First Monday of April month)

He wants to find the first monday in April for a given date. Maybe I am missing something.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

LutzM SSC Guru Points: 107049 More actions · Answer 11

But the most "elegant" way most probably still is the calendar table:

SELECT TOP 1 dateValue

FROM myCalendar

WHERE dateName ='Monday'

AND dateValue >= CAST(CAST(YEAR(@MyDate) AS CHAR(4)) +'0401' AS DATETIME)

ORDER BY dateValue

😉

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 12

Sean Lange (6/11/2013)
Lynn Pettis (6/11/2013)
Sean Lange (6/11/2013)
This should do it.
declare @MyDate datetime = '2013-03-08'
--set @MyDate = '2014-08-08'
set @MyDate = dateadd(month, 3, dateadd(yy, datediff(yy, 0, @MyDate), 0)) --gets April 1st for the current year.
select dateadd(wk, datediff(wk, 0, dateadd(dd, 6 - datepart(day, @MyDate), @MyDate)), 0)
He isn't looking for April 1st of any given year, he wants the first Monday in April of any given year.
It sounds to me like that is exactly what he is after.
I just wanna find first monday of april given a date or year.
Say if date='2013-02-02' or year='2013',
then my query should find the First Monday of april i.e '2013-04-01'
If date ='2014-08-08',
then I need '2014-04-07'(First Monday of April month)
He wants to find the first monday in April for a given date. Maybe I am missing something.

I only took a quick look (didn't test it first). I apologize and stand corrected.

I also blame this on a lack of caffeine as I am staying away from it until I see my doctor this afternoon.

Sean Lange SSC Guru Points: 286573 More actions · Answer 13

Lynn Pettis (6/11/2013)
I also blame this on a lack of caffeine as I am staying away from it until I see my doctor this afternoon.

I hope everything is ok. Not being able to have caffeine is a serious issue, especially for someone like yourself who doesn't drink. 😀

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/