DayOfYear Query returns two days | Community
Skip to main content
B
Bruno_Cotrim
Level 2
May 14, 2019
Solved

DayOfYear Query returns two days

  • May 14, 2019
  • 4 replies
  • 2225 views

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:

Note: the @birthday field is a "Date"

Result (today 05/14):

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

    This post is no longer active and is closed to new replies. Need help? Start a new post to ask your question.
    Best answer by pierrec70731455

    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

    4 replies

    pierrec70731455
    pierrec70731455
    Level 4
    May 14, 2019

    Hi Bruno,

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

    Kind regards,

    Pierre

    B
    Bruno_CotrimAuthor
    Level 2
    May 14, 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

    pierrec70731455
    pierrec70731455Accepted solution
    Level 4
    May 14, 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

      B
      Bruno_CotrimAuthor
      Level 2
      May 14, 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

      Terms & ConditionsAccessibility statement

      Loading footer...