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
 use of operator '%' in sql server
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

learning_grsql
Posting Yak Master

230 Posts

Posted - 09/25/2012 :  16:16:22  Show Profile  Reply with Quote
while was reading one of the old posts here, I came across the code below. I could not understand the use of '%' in this code.

link : http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=83637


declare @d datetime
set @d='2100-01-01'
Select 
	case   
		when year(@d)%400=0 then 'yes'
		when year(@d)%100=0 then 'no'
		when year(@d)%4=0 then 'yes'
	else 'no'  
	end 
as is_leap_year


link


I thought it was divide but dividing by 400 would not get 0 so I'm confused.

webfred
Flowing Fount of Yak Knowledge

Germany
8765 Posts

Posted - 09/25/2012 :  16:20:13  Show Profile  Visit webfred's Homepage  Reply with Quote
modulo


Too old to Rock'n'Roll too young to die.
Go to Top of Page

learning_grsql
Posting Yak Master

230 Posts

Posted - 09/26/2012 :  03:53:44  Show Profile  Reply with Quote
Thanks Webfred
I just read through more about modulo in Google and it seems modulo is just an another word for remainder.

But in the above example suppose date was 2005 then 2005 DIVIDED BY 100 comes 5 as remainder, not 0. Then how it works?

Edited by - learning_grsql on 09/26/2012 03:55:00
Go to Top of Page

webfred
Flowing Fount of Yak Knowledge

Germany
8765 Posts

Posted - 09/26/2012 :  04:55:20  Show Profile  Visit webfred's Homepage  Reply with Quote
quote:
Originally posted by learning_grsql

Thanks Webfred
I just read through more about modulo in Google and it seems modulo is just an another word for remainder.

But in the above example suppose date was 2005 then 2005 DIVIDED BY 100 comes 5 as remainder, not 0. Then how it works?



2005 devided by 100
If the remainder isn't zero then it is no leap year.

Where are your problems in understandig:
- the use of modulo or
- the rules to say if a year is a leap year?


Too old to Rock'n'Roll too young to die.
Go to Top of Page

learning_grsql
Posting Yak Master

230 Posts

Posted - 09/26/2012 :  12:40:44  Show Profile  Reply with Quote
I think the below statement I cannot understand


when year % 100 = 0 Then  no leap year


As it stands, I understand when a year is divided by 100 and the remainder is zero, then it is not a leap year.

If I just assume, the year is 2005 then 2005 % 100 comes remainder 5 then as per condition it is a leap year but in fact it is not when I run the code. So I'm confused.

Edited by - learning_grsql on 09/26/2012 12:41:49
Go to Top of Page

bitsmed
Constraint Violating Yak Guru

415 Posts

Posted - 09/26/2012 :  14:07:58  Show Profile  Reply with Quote
You seem to focus only on part of the case statement, and not the whole statement.

Evaluating the code with the year 2005:

when year(@d)%400=0 <--- this is not true, so we continue evaluating
when year(@d)%100=0 <--- this is not true, so we continue evaluating
when year(@d)%4     <--- this is not true, so we continue evaluating
else                <--- this is true, so we return "no"
Go to Top of Page

learning_grsql
Posting Yak Master

230 Posts

Posted - 09/26/2012 :  16:05:59  Show Profile  Reply with Quote
Thank you very much bitsmed and Webfred.
Now I have got it.

Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22765 Posts

Posted - 10/05/2012 :  05:17:44  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
There are various methods to find out if it is a leap year http://beyondrelational.com/modules/2/blogs/70/posts/10934/different-ways-to-find-out-a-leap-year.aspx

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts

Posted - 10/06/2012 :  02:54:17  Show Profile  Reply with Quote
Here is a simple method. Find the day of year for the last day of the year and subtract 365 to give 0 for non-leap year and 1 for leap year.
select
	a.[Year],
	IsLeapYear = datepart(dy,dateadd(yy,a.[Year]-1899,-1))-365
from
	( -- Test Data
	select [Year] = 1900 union all
	select [Year] = 1999 union all
	select [Year] = 2000 union all
	select [Year] = 2001 union all
	select [Year] = 2002 union all
	select [Year] = 2003 union all
	select [Year] = 2004
	) a

Results:
Year        IsLeapYear
----------- -----------
1900        0
1999        0
2000        1
2001        0
2002        0
2003        0
2004        1



CODO ERGO SUM
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22765 Posts

Posted - 10/16/2012 :  09:47:30  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
Thats good one MVJ

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts

Posted - 10/16/2012 :  10:39:47  Show Profile  Reply with Quote
quote:
Originally posted by madhivanan

Thats good one MVJ

Madhivanan

Failing to plan is Planning to fail



Feel free to add it to your blog post.





CODO ERGO SUM
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22765 Posts

Posted - 10/16/2012 :  10:52:34  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
quote:
Originally posted by Michael Valentine Jones

quote:
Originally posted by madhivanan

Thats good one MVJ

Madhivanan

Failing to plan is Planning to fail



Feel free to add it to your blog post.





CODO ERGO SUM


Done. Thanks

Madhivanan

Failing to plan is Planning to fail
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.11 seconds. Powered By: Snitz Forums 2000