Post reply

Selecting records with same day and month as today

  • June 29, 2004 at 7:50 am

    #108419

    Hi all,

    I am having trouble with a query with which I want to return all records which match the day and month (but not the year) to the day and month of the current date.

    It is a "Born on this day" list which dips into a SQL Server 2000 DB. The dates of birth are stored in datetime format.

    I am hoping that there is an easy way to do it in a sproc as opposed to looping over the results in my webapp.

    Any info appreciated.

    JM

  • June 29, 2004 at 8:07 am

    #512442

    this should help

    select xys from mytable where datepart(dd,mytable.datefield)=datepart(dd,getdate()) and datepart(mm,mytable.datefield)=datepart(mm,getdate())

    Cheers

    MVDBA

  • June 29, 2004 at 9:24 am

    #512467

    Thanks, that did it!

     

    JW

  • June 29, 2004 at 9:46 am

    #512478

    Just for the sake of another option

    where right(convert(char(8),mytable.datefield,112),4) = right(convert(char(8),getdate(),112),4)

  • June 30, 2004 at 3:32 am

    #512598

    out of interest is it quicker?

    MVDBA

  • June 30, 2004 at 5:18 am

    #512609

    Don't know for sure. I usually just test a few options to see which gives best performance for the data in question, just make sure you run several times for comparison and clean the buffers between runs for best compare if possible.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply