Your achievements

Level 1

0% to

Level 2

Tip /
Sign in

Sign in to Community

to gain points, level up, and earn exciting badges like the new
BedrockMission!

Learn More

View all

Sign in to view all badges

DayOfYear Query returns two days

Avatar

Avatar
Validate 1
Level 2
Bruno_Cotrim
Level 2

Likes

5 likes

Total Posts

9 posts

Correct Reply

1 solution
Top badges earned
Validate 1
Boost 5
Boost 3
Boost 1
Affirm 1
View profile

Avatar
Validate 1
Level 2
Bruno_Cotrim
Level 2

Likes

5 likes

Total Posts

9 posts

Correct Reply

1 solution
Top badges earned
Validate 1
Boost 5
Boost 3
Boost 1
Affirm 1
View profile
Bruno_Cotrim
Level 2

14-05-2019

Hello guys,


I've a simple query that I've not been able to solve. I'm comparing day of year to day of year to find out someones birthday and it it returning me two days.

The query:

birthday query 1.JPG

Note: the @birthday field is a "Date"

Result (today 05/14):

birthday query result.JPG

Sql Query:

(Extract(DOY FROM E0.tsBirthdate) = Extract(DOY FROM (GetDate()) AT TIME ZONE 'Europe/Lisbon'))

As far as I understand the problem seems to be in the Time Zone but I can't see how to solve it...

Does any one have a suggestion on how to solve this issue?


Kind Regards,

Bruno Cotrim

Accepted Solutions (1)

Accepted Solutions (1)

Avatar

Avatar
Validate 1
Level 4
pierrec70731455
Level 4

Likes

62 likes

Total Posts

59 posts

Correct Reply

14 solutions
Top badges earned
Validate 1
Boost 50
Boost 5
Boost 3
Boost 25
View profile

Avatar
Validate 1
Level 4
pierrec70731455
Level 4

Likes

62 likes

Total Posts

59 posts

Correct Reply

14 solutions
Top badges earned
Validate 1
Boost 50
Boost 5
Boost 3
Boost 25
View profile
pierrec70731455
Level 4

14-05-2019

Hi Bruno,

my bad I got confused with the sql name tsBirthdate

The reason why your method doesn't work is because of leap years.

there are as many days from 01/01/1948 to 13/05/1948 as from 01/01/1942 to 14/05/1942 because of the 29th of February.

I recommand extracting the day in the month and the month and comparing it to the current day and month.

Kind regards,

Pierre

Answers (3)

Answers (3)

Avatar

Avatar
Validate 1
Level 2
Bruno_Cotrim
Level 2

Likes

5 likes

Total Posts

9 posts

Correct Reply

1 solution
Top badges earned
Validate 1
Boost 5
Boost 3
Boost 1
Affirm 1
View profile

Avatar
Validate 1
Level 2
Bruno_Cotrim
Level 2

Likes

5 likes

Total Posts

9 posts

Correct Reply

1 solution
Top badges earned
Validate 1
Boost 5
Boost 3
Boost 1
Affirm 1
View profile
Bruno_Cotrim
Level 2

14-05-2019

Hello Pierre


Thank you, that seems to have been exactly the problem. I didn't know the method DayOfYear translated the date to the number of days, good to keep in mind.


Kind regards,

Bruno Cotrim

Avatar

Avatar
Validate 1
Level 2
Bruno_Cotrim
Level 2

Likes

5 likes

Total Posts

9 posts

Correct Reply

1 solution
Top badges earned
Validate 1
Boost 5
Boost 3
Boost 1
Affirm 1
View profile

Avatar
Validate 1
Level 2
Bruno_Cotrim
Level 2

Likes

5 likes

Total Posts

9 posts

Correct Reply

1 solution
Top badges earned
Validate 1
Boost 5
Boost 3
Boost 1
Affirm 1
View profile
Bruno_Cotrim
Level 2

14-05-2019

Hello Pierre,

Thank you for the suggestion.


It is a Date not a Datetime, non the less I've already tried that

Kind Regards,

Bruno Cotrim

Avatar

Avatar
Validate 1
Level 4
pierrec70731455
Level 4

Likes

62 likes

Total Posts

59 posts

Correct Reply

14 solutions
Top badges earned
Validate 1
Boost 50
Boost 5
Boost 3
Boost 25
View profile

Avatar
Validate 1
Level 4
pierrec70731455
Level 4

Likes

62 likes

Total Posts

59 posts

Correct Reply

14 solutions
Top badges earned
Validate 1
Boost 50
Boost 5
Boost 3
Boost 25
View profile
pierrec70731455
Level 4

14-05-2019

Hi Bruno,

the birthdate being a datetime, did you try casting the birthdate to a date with ToDate(@birthday)?

Kind regards,

Pierre