February 23, 2011 at 10:34 am
Hi
I need to concatenate the following (which executes a function to select the financial year from a DateTime field):
SELECT *,
dbo.Fn_FinMonth (DatePart(MM,[Graded Date])) FinMth,
e.g. Month 04 = Financial Month 01
with the financial year as selected here:
FinYear = CASE
WHEN MONTH([Graded Date]) < 4 THEN YEAR([Graded Date])-1
ELSE YEAR([Graded Date]) END
e.g 2010
My required output from the above example would therefore be 201004
Both fields are numeric, so if I use the standard SQL concatenation symbol (+) it sums the 2 fields to give 2011. I know I should be using convert or cast here, but I can't quite work out how.
As usual, any help would be much appreciated.
Cheers
-Rich
February 23, 2011 at 12:39 pm
richard.kirby (2/23/2011)
HiI need to concatenate the following (which executes a function to select the financial year from a DateTime field):
SELECT *,dbo.Fn_FinMonth (DatePart(MM,[Graded Date])) FinMth,
e.g. Month 04 = Financial Month 01
with the financial year as selected here:
FinYear = CASEWHEN MONTH([Graded Date]) < 4 THEN YEAR([Graded Date])-1
ELSE YEAR([Graded Date]) END
e.g 2010
My required output from the above example would therefore be 201004
Both fields are numeric, so if I use the standard SQL concatenation symbol (+) it sums the 2 fields to give 2011. I know I should be using convert or cast here, but I can't quite work out how.
As usual, any help would be much appreciated.
Cheers
-Rich
There are a number of ways to do this - this is just one of them. Hopefully, the sample code below will get you moving in the right direction...
declare @testDate date
set @testDate = '2010-04-15'
select cast(YEAR(@testdate) as varchar) +
right('00' + cast(MONTH(@testdate) as varchar),2)
This returns 201004 as the result.
-Ki
-Ki
February 23, 2011 at 2:58 pm
Do you really want to return fiscal year plus calendar month rather than fiscal year plus fiscal period?
I also dislike using a CASE statement to return the fiscal year, because it disguises the fact that it's a linear function of the date. Here is how to find the fiscal year starting April 1st without using a CASE statement. It will need to be adjusted for other fiscal years. This is also useful if you really want to return the fiscal period rather than the calendar month.
SELECT Year(DateAdd(Month,-3,YourDateField)) AS FiscalYear
, Convert(char(6), DateAdd(Month, -3, YourDateField), 112) AS FiscalPeriod
FROM YourTable
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
February 23, 2011 at 3:05 pm
drew.allen (2/23/2011)
Do you really want to return fiscal year plus calendar month rather than fiscal year plus fiscal period?
Good point. I was only focused on the technical "how do you concatenate the parts of the date" portion, not the business logic behind it.
Kind of funny, because at my office, my first priority is always to dig into the business logic!
- Ki
-Ki
February 24, 2011 at 3:39 am
Thanks Kiara and Drew - this is great stuff and I shall share it with my colleagues to help them write more efficient SQL as like me they probabaly use more long winded methodologies using case etc. to come up with Fiscal Year data.
Much appreciated.
Cheers
-Rich
February 26, 2011 at 7:57 am
Hi
often i have the same issue. If there is no absolute need for the result being a char, but an integer value is ok too, then i use
SELECT Year(testdate) * 100 + month(testdate)
If the first fiscal period is f.ex april, then i use:
SELECT case when month(testdate) < 4 then (year(testdate)+1)*100 + month(testdate) - 3
else year(testdate)*100 + month(testdate) + 9 end as FiscalPeriod
regards
piet
February 28, 2011 at 2:55 am
Hi Piet
Thanks for this - I have placed your code in our syntax library, so my colleagues can make their choice!
Cheers
-Rich
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply