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
 General SQL Server Forums
 New to SQL Server Programming
 use of operator '%' in sql server

Author  Topic 

learning_grsql
Posting Yak Master

230 Posts

Posted - 2012-09-25 : 16:16:22
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
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-09-25 : 16:20:13
modulo


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

learning_grsql
Posting Yak Master

230 Posts

Posted - 2012-09-26 : 03:53:44
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?
Go to Top of Page

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-09-26 : 04:55:20
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 - 2012-09-26 : 12:40:44
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.
Go to Top of Page

bitsmed
Aged Yak Warrior

545 Posts

Posted - 2012-09-26 : 14:07:58
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 - 2012-09-26 : 16:05:59
Thank you very much bitsmed and Webfred.
Now I have got it.

Go to Top of Page

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2012-10-05 : 05:17:44
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)

7020 Posts

Posted - 2012-10-06 : 02:54:17
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

22864 Posts

Posted - 2012-10-16 : 09:47:30
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)

7020 Posts

Posted - 2012-10-16 : 10:39:47
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

22864 Posts

Posted - 2012-10-16 : 10:52:34
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
   

- Advertisement -