Datepart returning different day of the year for same birthdate?

Question

0.00/5 (No votes)

See more:

SQL-Server-2014

Hi All

I am querying for Players who's birthday is within the next 7 days in Sql server

SQL

select TOP 100 Percent PlayerID from vwPlayer WITH(NOLOCK) where ISDATE(answer)=1 AND datepart(dy,CONVERT(datetime,Answer)) > DatePart(dy,GetDate()) AND datepart(dy,CONVERT(datetime,Answer)) < DatePart(dy,DateAdd(dy,7,GetDate()))

but the results are not consistent as

SQL

print datepart(dy,'09/01/1999')--244
print datepart(dy,'09/01/2000')--245
print datepart(dy,getdate()) -- 245

So,

C#

DatePart function returning different day of the year for same birthdate

How to accurately query for Players who's birthday is within the next 7 days

Thanks!

What I have tried:

Caluculating the day of the year from players birthdate and check if it lies in currentdate and currentdate + 7 days
but datepart is returning different day number for same birthdate 09/01/1999 and 09/01/2000

Posted 31-Aug-16 20:27pm

Gaurav_Chaudhary

Updated 31-Aug-16 20:39pm

Add a Solution

Comments

Mehdi Gholam 1-Sep-16 2:34am

Try yyyy-MM-dd format e.g. 1999-09-01

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Accepted Answer · 2016-08-31T20:39:00

The year 2000 was a leap year: so there was an extra day added: Feb 29th 2000 which means that the DayOfYear move on by one. Try this and you'll see what I mean:

SQL

print datepart(dy,'02/28/1999')
print datepart(dy,'03/01/1999')
print datepart(dy,'02/28/2000')
print datepart(dy,'02/29/2000')
print datepart(dy,'03/01/2000')

"Thanks, So Do i have to make a custom function to check if leapyear = true and Month > 2 then add day=day+1 or is there any in-built function in Sql server."

Try offsetting the DOB year to this year, and check the days count:

SQL

DECLARE @DOB as DATE
SET @DOB = '1999-09-24'
PRINT DATEDIFF(dd, GETDATE(), DATEADD(yy,DATEPART(yy, GETDATE()) - DATEPART(yy, @DOB) ,@DOB))