Post reply

Propblem with Where clause (days in month)

  • August 23, 2005 at 3:10 am

    #165353

    Hi - SQL Server 2000.

    Can some one help with the following please.

    Customers make a payment on a certain day number of the month.

    My SP needs to pick up all customers who pay on the day number by comparing it with the day number of the date passed into the SP.

    For example if 23/08/2005 is passed in I need to get all those who pay on the 23rd of the month. Straight forward so far using DatePart fuction. However, If the customer pays on the 30th of the month and the month is a feb, I need to pick up the customer if it is the 28th of the month (29th on a leap year). And likewise If the 30th of September is passed in and the customer is due to pay on the 31st of the month, I need to pick up that customer because there are not 31 days in September.

    Anyone have any idea how to structure my where clause to solve this?

    Thanks

    CCB

     

  • August 23, 2005 at 3:56 am

    #584169

    Assuming @DParm (datetime) is parameter passed to SP and DateCol (datetime) is date column in table, I think this should work?

    (I haven't tested this.)

    WHERE (DatePart(day, @DParm) = DatePart(day, DateCol)) OR

    (DatePart(day, @DParm) > 28 AND DatePart(month, DateCol) = 2 AND DatePart(day, DateCol) >= 28) OR

    (DatePart(day, @DParm) > 30 AND DatePart(month, DateCol) IN (4,6,9,11) AND DatePart(day, DateCol) >= 30)

  • August 23, 2005 at 4:45 am

    #584174

    Thanks Paul

    CCB

Viewing 3 posts - 1 through 2 (of 2 total)

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