Day of Year Problem - SQL Server Forums

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

Day of Year Problem

Author

Topic

raysefo
Constraint Violating Yak Guru

260 Posts

Posted - 2011-04-05 : 04:06:06

Hi,

I have a table which has two columns ; name and birthdate.Below is a sample data. The wierd thing is both data have th same birthday of year?! How come this can happen? How can I fix this?

Thanks in advance.

Best Regards

TABLE
name Birthdate
AHMET DURSUN 04.04.1960
Cenk GITSIN 05.04.1977

SELECT name, birthdate AS Birthdate,DATEPART(dy, getdate() ) as day_of_year,DATEPART(dy, CONVERT (datetime, birthdate, 104) ) as bd_day_of_year FROM Users
where DATEPART(dy, CONVERT (datetime, birthdate, 104) ) - DATEPART(dy, getdate()) >=0 and
DATEPART(dy, CONVERT (datetime, birthdate, 104) ) - DATEPART(dy, getdate()) < 8

RESULT
name Birthdate day_of_year bd_day_of_year
AHMET DURSUN 04.04.1960 95 95
Cenk GITSIN 05.04.1977 95 95

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-04-05 : 04:18:44

Your query investigates which people have birthday the next 7 days.

N 56°04'39.26"
E 12°55'05.63"

raysefo
Constraint Violating Yak Guru

260 Posts

Posted - 2011-04-05 : 04:21:40

Yes thats true but how come it brings this result?

AHMET DURSUN 04.04.1960 95 95

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-04-05 : 04:30:53

Because the birthdays are NOT stored using proper datatype; DATETIME or DATE.
You are using VARCHAR, so SQL Server has to convert the string to a valid DATETIME/DATE value and doing this SQL Server follows certain rules.
For example; is 05.04.1977 the 5th of April 1977 or the 4th of May 1977?

This is why it is so important to USE PROPER DATATYPE when storing data.

N 56°04'39.26"
E 12°55'05.63"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-04-05 : 04:33:50

And you should use a proper function for calculating the birthday and not use DAYOFYEAR parameter.
Have you even considered leap years?

See this function here http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=80709

N 56°04'39.26"
E 12°55'05.63"

raysefo
Constraint Violating Yak Guru

260 Posts

Posted - 2011-04-05 : 04:38:18

Hi,

Yes in table the birthdate field is nvarchar(50). And the date 05.04.1977 is 5th of April 1977, thats why I m using CONVERT (datetime, birthdate, 104). Is there a way to fix this?

Thanks in advance.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-04-05 : 04:41:22

quote:
Originally posted by raysefo

Is there a way to fix this?

Yes.
Stop using VARCHAR when you are storing dates.

N 56°04'39.26"
E 12°55'05.63"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-04-05 : 04:43:26

quote:
Originally posted by raysefo

Yes thats true but how come it brings this result?

AHMET DURSUN 04.04.1960 95 95

1960 is a leapyear. That's why this year the 4th of April is the 95th day of the year, and not the 94th as it normally is.

USE PROPER DATATYPES!

And listen and learn from more experienced developers...

N 56°04'39.26"
E 12°55'05.63"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources