Accounting Period - SQL Server Forums

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 2008 Forums

Transact-SQL (2008)

Accounting Period

Author

Topic

NickC
Yak Posting Veteran

68 Posts

Posted - 2012-09-13 : 05:57:14

Hi

I'm writing a report which needs to have the following default paramater and not sure how to write it

when yoa = current year using year(getdate()) then use the previous month to produce an account month period

i.e. if current year is 2012 and month in year is september then I want it to show 201208, if it was 201310 then 201309 etc

however if the yoa is not a current year then i want it to be the last month in the year so 200912 for 2009 , 200812 for 2008 etc

does this make sense?

Thanks

nick

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-09-13 : 06:50:50

Here is a way to do this - to get the last day of the year, there probably are simpler ways than what I am showing below - google would tell you.

DECLARE @yoa INT = 2011;

SELECT
	CASE 
		WHEN @yoa = YEAR(GETDATE()) THEN CONVERT(CHAR(6), DATEADD(mm,-1,GETDATE()),112)
		WHEN @yoa < YEAR(GETDATE()) THEN CONVERT(CHAR(6),DATEADD(dd,-1,DATEADD(mm,12*(@yoa-1899),0)),112)
		ELSE 'Future'
	END

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources