| Author |
Topic |
|
nicci113
Starting Member
11 Posts |
 Posted - 2012-10-14 : 12:14:14
|
| Hi There,I have got a SP in SQL server. I am not able to completely understand the syntax. Can anybody have a look n guide me please. I know its calc two dates over 2 years and merge them, but need more clarrification."CASE WHEN Month(allpermit.issuedate)<4 THEN convert(varchar(4),Year(allpermit.issuedate)-1) + '-' + Right(cast(year(allpermit.issuedate) as varchar),2) WHEN Month(allpermit.issuedate)>=4 THEN convert(varchar(4),Year(allpermit.issuedate)) + '-' + Right(cast(year(allpermit.issuedate)+1 as varchar),2) ELSE 'ERROR'end as 'FinYear', " |
|
|
bitsmed
Aged Yak Warrior
545 Posts |
Posted - 2012-10-14 : 14:04:22
|
| Looks like the output is 4 digit start year, then a dash, then 2 digit end year. April 1. determines which year a given dates belongs- |
 |
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2012-10-14 : 14:56:55
|
In your system, the financial year runs from April 1 of a given year through March 31 of the following year. For example, April 1, 2011 through March 31 2012 is designated as Financial Year 2011.The query is trying to calculate the financial year for a given date - in this case, for allpermit.issuedate.You can get the same result with a shorter expression as shown below:SELECT YEAR(DATEADD(MONTH,-3,allpermit.issuedate)) AS FinYear |
|
|
|
bitsmed
Aged Yak Warrior
545 Posts |
Posted - 2012-10-14 : 15:06:37
|
| Could be financial period, like sunitabeck suggested.But the format is still yyyy-yyEx. May 15. 2012 would be 2012-13, February 10. 2012 would be 2011-12 |
|
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2012-10-14 : 15:16:53
|
quote:
Originally posted by bitsmed
Could be financial period, like sunitabeck suggested.But the format is still yyyy-yyEx. May 15. 2012 would be 2012-13, February 10. 2012 would be 2011-12
Ah, thanks bitsmed; I had missed that. It is trying to set the financial year in the format 2011-2012 or something along those lines. In that case, my short-hand would not work/would need to be modified. |
|
|
|
nicci113
Starting Member
11 Posts |
Posted - 2012-10-15 : 06:50:43
|
quote:
Originally posted by sunitabeck
quote:
Originally posted by bitsmed
Could be financial period, like sunitabeck suggested.But the format is still yyyy-yyEx. May 15. 2012 would be 2012-13, February 10. 2012 would be 2011-12
Ah, thanks bitsmed; I had missed that. It is trying to set the financial year in the format 2011-2012 or something along those lines. In that case, my short-hand would not work/would need to be modified.
So bascially its just a date function. Can I use something like dataadd and use concat to join two dates together? I just feel its a bit more complex function which I might simplyfy moving forward.. Much thanks to your help so far, really means a lot..:) |
|
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2012-10-15 : 06:57:34
|
You can do something like that but the need to concatenate makes even that somewhat complex and unsightly - see below:SELECT CAST(YEAR(DATEADD(MONTH,-3,allpermit.issuedate)) AS CHAR(4)) + '-' + CAST((YEAR(DATEADD(MONTH,-3,allpermit.issuedate))+1)%100 AS CHAR(2)); |
|
|
|
nicci113
Starting Member
11 Posts |
Posted - 2012-10-15 : 07:17:44
|
quote:
Originally posted by sunitabeck
You can do something like that but the need to concatenate makes even that somewhat complex and unsightly - see below:SELECT CAST(YEAR(DATEADD(MONTH,-3,allpermit.issuedate)) AS CHAR(4)) + '-' + CAST((YEAR(DATEADD(MONTH,-3,allpermit.issuedate))+1)%100 AS CHAR(2));
I'm going to try this.. I know but i work in Public sector, they don't like changes lol.. spend soo much time to understand the logic behind. still not sure why its been done that way but going to try yours suggestion to see if that makes a differnt.. cheers.. :) |
|
|
|
nicci113
Starting Member
11 Posts |
Posted - 2012-10-15 : 07:30:43
|
quote:
Originally posted by sunitabeck
You can do something like that but the need to concatenate makes even that somewhat complex and unsightly - see below:SELECT CAST(YEAR(DATEADD(MONTH,-3,allpermit.issuedate)) AS CHAR(4)) + '-' + CAST((YEAR(DATEADD(MONTH,-3,allpermit.issuedate))+1)%100 AS CHAR(2));
it worked, but final Question why did you used "%100"? |
|
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2012-10-15 : 08:18:55
|
The % operator gets the modulus of the value passed in; i.e., it returns 13 when 2013 is passed in. You can see that if you run the two queries below, first one without the % operator and second with the % operator.SELECT CAST((YEAR(DATEADD(MONTH,-3,GETDATE()))+1) AS CHAR(4));SELECT CAST((YEAR(DATEADD(MONTH,-3,GETDATE()))+1)%100 AS CHAR(2)); |
|
|
|
nicci113
Starting Member
11 Posts |
Posted - 2012-10-15 : 09:17:14
|
quote:
Originally posted by sunitabeck
The % operator gets the modulus of the value passed in; i.e., it returns 13 when 2013 is passed in. You can see that if you run the two queries below, first one without the % operator and second with the % operator.SELECT CAST((YEAR(DATEADD(MONTH,-3,GETDATE()))+1) AS CHAR(4));SELECT CAST((YEAR(DATEADD(MONTH,-3,GETDATE()))+1)%100 AS CHAR(2));
Many Thanks for your help through out.. cheers :) |
|
|
|
|
Date Function