SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Date Function
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

nicci113
Starting Member

11 Posts

Posted - 10/14/2012 :  12:14:14  Show Profile  Reply with Quote
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
Constraint Violating Yak Guru

406 Posts

Posted - 10/14/2012 :  14:04:22  Show Profile  Reply with Quote
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-
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 10/14/2012 :  14:56:55  Show Profile  Reply with Quote
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
Go to Top of Page

bitsmed
Constraint Violating Yak Guru

406 Posts

Posted - 10/14/2012 :  15:06:37  Show Profile  Reply with Quote
Could be financial period, like sunitabeck suggested.
But the format is still yyyy-yy
Ex. May 15. 2012 would be 2012-13, February 10. 2012 would be 2011-12
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 10/14/2012 :  15:16:53  Show Profile  Reply with Quote
quote:
Originally posted by bitsmed

Could be financial period, like sunitabeck suggested.
But the format is still yyyy-yy
Ex. 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.
Go to Top of Page

nicci113
Starting Member

11 Posts

Posted - 10/15/2012 :  06:50:43  Show Profile  Reply with Quote
quote:
Originally posted by sunitabeck

quote:
Originally posted by bitsmed

Could be financial period, like sunitabeck suggested.
But the format is still yyyy-yy
Ex. 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..:)
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 10/15/2012 :  06:57:34  Show Profile  Reply with Quote
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));
Go to Top of Page

nicci113
Starting Member

11 Posts

Posted - 10/15/2012 :  07:17:44  Show Profile  Reply with Quote
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.. :)
Go to Top of Page

nicci113
Starting Member

11 Posts

Posted - 10/15/2012 :  07:30:43  Show Profile  Reply with Quote
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"?
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 10/15/2012 :  08:18:55  Show Profile  Reply with Quote
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));
Go to Top of Page

nicci113
Starting Member

11 Posts

Posted - 10/15/2012 :  09:17:14  Show Profile  Reply with Quote
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 :)
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000