Date Breakdown

Question

Post reply

Date Breakdown

alorenzini

SSCarpal Tunnel

Points: 4620
More actions
August 7, 2008 at 3:19 pm

#211037

How can I calcuate the first date and last date of the year when I pass in just the year.
Something like:
@year = 2008
FirstDay = '01/01/2008
Lastday = '12/31/2008'
Thanks,
Art
Database Analyst
Tastefully Simple, Inc.
alorenzini@tastefullysimple.com
" Some days, it's not even worth chewing through the restraints!

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

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

Buzz-523717 SSC-Addicted Points: 494 More actions · Answer 1

Since the year always starts on January 1

DECLARE@Year int,

@Date datetime

-- SET UP THE YEAR

SET@Year = 2008

-- SET THE DATE EQUAL TO

-- JANUARY FIRST OF THE YEAR

-- ADD THE 1 YEAR AND SUBTRACT ONE DAY

SET@Date = CAST('01/01/' + cast(@Year as char(4)) as datetime)

SELECT@Date as FirstDayOfYear,

DATEADD(dd,-1,DATEADD(yy,1,@Date)) as LastDayofYear

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

select

a.MyYear,

FirstDayofYear= dateadd(year,(a.MyYear-1900),0),

LastDayofYear= dateadd(year,(a.MyYear-1899),-1)

from

(

-- Test Data

Select MyYear = 2001union all

Select MyYear = 2002union all

Select MyYear = 2003union all

Select MyYear = 2004union all

Select MyYear = 2005

) a

Results:

MyYear FirstDayofYear LastDayofYear

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

2001 2001-01-01 00:00:00.000 2001-12-31 00:00:00.000

2002 2002-01-01 00:00:00.000 2002-12-31 00:00:00.000

2003 2003-01-01 00:00:00.000 2003-12-31 00:00:00.000

2004 2004-01-01 00:00:00.000 2004-12-31 00:00:00.000

2005 2005-01-01 00:00:00.000 2005-12-31 00:00:00.000

alorenzini SSCarpal Tunnel Points: 4620 More actions · Answer 3

Thanks that worked.

Thanks,
Art
Database Analyst
Tastefully Simple, Inc.

alorenzini@tastefullysimple.com
" Some days, it's not even worth chewing through the restraints!

Anam Verma SSCertifiable Points: 5365 More actions · Answer 4

I think first and last date of year will always be same irrespective of what year is it. 😉

first date: 01/01/ year

last date: 31/12/ year

therefore, no need to use dateadd functions. use only conversion functions to get the date in datetime format. 🙂

Jeff Moden SSC Guru Points: 1004434 More actions · Answer 5

helloanam (8/7/2008)
I think first and last date of year will always be same irrespective of what year is it. 😉
first date: 01/01/ year
last date: 31/12/ year
therefore, no need to use dateadd functions. use only conversion functions to get the date in datetime format. 🙂

Not a bad idea... But then you end up with a VARCHAR in the conversions which is inherently slower AND you'd need to convert it back to datetime to avoid implicit conversions that may wreck Index Seeks.

--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)

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

helloanam (8/7/2008)
I think first and last date of year will always be same irrespective of what year is it. 😉
first date: 01/01/ year
last date: 31/12/ year
therefore, no need to use dateadd functions. use only conversion functions to get the date in datetime format. 🙂

The reason I used the DATEADD function is because it is more efficient than converting to a string and back again to datetime. Also, the code to do it is much shorter.

For those interested in testing my claims, there was extensive testing of the speed of various methods on these threads:

Converting Year, Month, and Day to DateTime

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=22339

Building a date

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=66471

GSquared SSC Guru Points: 260824 More actions · Answer 7

Thank you for those links. I hadn't seen that one before. Very useful.

- 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