Date conversiuon - 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

Old Forums

CLOSED - General SQL Server

Date conversiuon

Author

Topic

pmak
Starting Member

41 Posts

Posted - 2006-05-11 : 18:05:28

In a Sql query I need to convert a given date to fiscal year. Fiscal year start from 1-Apr to 31-Mar each year and I need the fiscal year in the format like 2005-06 etc. Thanks

MichaelP
Jedi Yak

2489 Posts

Posted - 2006-05-11 : 19:30:37

This should get you started. Just change the "THEN" clause to do the formatting.

Edit: I don't think this works actually.


DECLARE @MyDate DATETIME
SELECT @MyDate = GetDate()
--SELECT @MyDate = '1/2/2007'

SELECT CASE 
	WHEN @MyDate BETWEEN CAST('4/1/' + CAST(datepart(yyyy, @MyDate) AS CHAR(4)) as Datetime) AND (DATEADD(qq, 1, '4/1/'+  CAST(datepart(yyyy, @MyDate) AS CHAR(4)))) THEN 'q1'
	WHEN @MyDate BETWEEN CAST('7/1/' + CAST(datepart(yyyy, @MyDate) AS CHAR(4)) as Datetime) AND (DATEADD(qq, 1, '7/1/'+  CAST(datepart(yyyy, @MyDate) AS CHAR(4)))) THEN 'q2'
	WHEN @MyDate BETWEEN CAST('10/1/' + CAST(datepart(yyyy, @MyDate) AS CHAR(4)) as Datetime) AND (DATEADD(qq, 1, '10/1/'+  CAST(datepart(yyyy, @MyDate) AS CHAR(4)))) THEN 'q3'
	WHEN @MyDate BETWEEN CAST('1/1/' + CAST(datepart(yyyy, @MyDate) AS CHAR(4)) as Datetime) AND DATEADD(yyyy, 1, (DATEADD(qq, 1, '1/1/'+  CAST(datepart(yyyy, @MyDate) AS CHAR(4))))) THEN 'q4'
END as TheQuater

<Yoda>Use the Search page you must. Find the answer you will. Cursors, path to the Dark Side they are. Avoid them, you must. Use Order By NewID() to get a random record you will.</Yoda>

Opinions expressed in this post are not necessarily those of TeleVox Software, inc. All information is provided "AS IS" with no warranties and confers no rights.

nosepicker
Constraint Violating Yak Guru

366 Posts

Posted - 2006-05-11 : 19:37:31

If you only want to calculate the year, can you just do this?

SELECT FiscalYear = CONVERT(varchar, YEAR(DATEADD(mm, -3, YourDate))) + '-' + CONVERT(varchar, YEAR(DATEADD(mm, -3, YourDate)) +1)

MichaelP
Jedi Yak

2489 Posts

Posted - 2006-05-11 : 19:42:29

Nosepicker for the win!
Well done!

<Yoda>Use the Search page you must. Find the answer you will. Cursors, path to the Dark Side they are. Avoid them, you must. Use Order By NewID() to get a random record you will.</Yoda>

Opinions expressed in this post are not necessarily those of TeleVox Software, inc. All information is provided "AS IS" with no warranties and confers no rights.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources