| Author |
 Topic  |
|
|
learning_grsql
Posting Yak Master
165 Posts |
 Posted - 09/25/2012 : 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
Flowing Fount of Yak Knowledge
Germany
8529 Posts |
Posted - 09/25/2012 : 16:20:13
|
modulo
Too old to Rock'n'Roll too young to die. |
 |
|
|
learning_grsql
Posting Yak Master
165 Posts |
Posted - 09/26/2012 : 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? |
Edited by - learning_grsql on 09/26/2012 03:55:00 |
|
|
|
webfred
Flowing Fount of Yak Knowledge
Germany
8529 Posts |
Posted - 09/26/2012 : 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. |
|
|
|
learning_grsql
Posting Yak Master
165 Posts |
Posted - 09/26/2012 : 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.
|
Edited by - learning_grsql on 09/26/2012 12:41:49 |
|
|
|
bitsmed
Posting Yak Master
Denmark
107 Posts |
Posted - 09/26/2012 : 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"
|
|
|
|
learning_grsql
Posting Yak Master
165 Posts |
Posted - 09/26/2012 : 16:05:59
|
Thank you very much bitsmed and Webfred.
Now I have got it.
|
|
|
|
madhivanan
Premature Yak Congratulator
India
22469 Posts |
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
USA
7004 Posts |
Posted - 10/06/2012 : 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 |
|
|
|
madhivanan
Premature Yak Congratulator
India
22469 Posts |
Posted - 10/16/2012 : 09:47:30
|
Thats good one MVJ 
Madhivanan
Failing to plan is Planning to fail |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
USA
7004 Posts |
Posted - 10/16/2012 : 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 |
|
|
|
madhivanan
Premature Yak Congratulator
India
22469 Posts |
Posted - 10/16/2012 : 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 |
|
|
| |
Topic |
|
use of operator '%' in sql server
