how to find year end and year start

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

General SQL Server Forums

New to SQL Server Programming

how to find year end and year start

Author

Topic

qutesanju
Posting Yak Master

193 Posts

Posted - 2009-02-05 : 08:55:44

how to find year end and year start from date entered?

Jai Krishna
Constraint Violating Yak Guru

333 Posts

Posted - 2009-02-05 : 09:05:08

select dateadd(d,-datepart(dy,getdate())+1,getdate()) as yearstart,
dateadd(d,-datepart(d,getdate()),dateadd(m,13-datepart(m,getdate()),getdate())) as yearend

Jai Krishna

Rajesh Jonnalagadda
Starting Member

45 Posts

Posted - 2009-02-05 : 09:37:32

Hi,
A small change in Jai krishna yearstart query,

SELECT DateAdd(d, -DatePart(dy,getdate()-1),getdate()) as YearStart,
DateAdd(d, -DatePart(d,getdate()), Dateadd(m,13-datepart(m,getdate()),getdate())) as YearEnd

Rajesh Jonnalagadda
[url="http://www.ggktech.com
"]GGK TECH[/url]

Jai Krishna
Constraint Violating Yak Guru

333 Posts

Posted - 2009-02-05 : 10:04:21

quote:
Originally posted by Rajesh Jonnalagadda

Hi,
A small change in Jai krishna yearstart query,

SELECT DateAdd(d, -DatePart(dy,getdate()-1),getdate()) as YearStart,
DateAdd(d, -DatePart(d,getdate()), Dateadd(m,13-datepart(m,getdate()),getdate())) as YearEnd

Rajesh Jonnalagadda
[url="http://www.ggktech.com
"]GGK TECH[/url]

I think change is not that necessary as my query is producing correct results

Jai Krishna

jek2201
Starting Member

1 Post

Posted - 2009-05-26 : 04:40:44

A simple one if you want also to have hours, minuttes and the seconds right as well:

--Year Start
select dateadd(year, (select year(getdate())) - 1900 , '01-01-1900')

--Year End (for some reason -1 wont work and -2 really gives minus 3 but you are into miliseconds)
select dateadd(ms, -2, (select dateadd(year, (select year(getdate())) - 1900 + 1 , '01-01-1900')))

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-05-26 : 04:55:15

hai rajesh,
both queries will give u same output
select dateadd(d,-datepart(dy,getdate())+1,getdate()) as yearstart,
dateadd(d,-datepart(d,getdate()),dateadd(m,13-datepart(m,getdate()),getdate())) as yearend

SELECT DateAdd(d, -DatePart(dy,getdate()-1),getdate()) as YearStart,
DateAdd(d, -DatePart(d,getdate()), Dateadd(m,13-datepart(m,getdate()),getdate())) as YearEnd

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-05-26 : 05:11:50

Keep it simple

SELECT	DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()), 0) AS YearStart,
	DATEADD(YEAR, DATEDIFF(YEAR, -1, GETDATE()), -1) AS YearEnd

E 12°55'05.63"
N 56°04'39.26"

HenryJin
Starting Member

1 Post

Posted - 2012-11-07 : 20:59:49

SwePeso's code is working perfect for me

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2012-11-17 : 04:50:09

Go to the end of the page and find lot of other such methods
http://beyondrelational.com/modules/2/blogs/70/posts/10899/understanding-datetime-column-part-iii.aspx

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources