Help with SQL View

  • I have most of a veiw written but Iā€™m not sure how to compare two dates. I have a subscription.paid_thru and an order_date. I want to filter by:

    Show record if order_date is within 30 days plus or minus of the subscription.paid_thru date.

    I can make it work with filtering on order date and saying =subscription.paid_thru + 30 and that gives me the records that have the order date of the subscription date + 30 days but I need the range of all records + or ā€“ 30 days from the subscription.paid_thru.

    Can anyone help?

  • You could try using DateAdd and give a between condition. Dateadd(dd,30,ColumnName) and Dateadd(dd,-30,columnName)

    -Roy

  • Roy,

    thank you but that is still only giving me the order_date that is exaclty 30 days + or - the subscriptions.paid_thru. I need any date that falls within that 30 day window on either side of the subscriptions.paid_thru.

  • Roy,

    thank you but that is still only giving me the order_date that is exaclty 30 days + or - the subscriptions.paid_thru. I need any date that falls within that 30 day window on either side of the subscriptions.paid_thru.

    Mike,

    Roy was saying to use the query as below, which does give you everything >=-30 and <=30 days of the subscription date.

    select *

    from table

    where order_date between Dateadd(dd,-30,paid_thru) and Dateadd(dd,30,paid_thru)

  • Ah, thank you, that worked perfectly.

    Very much appreciated.

  • Thanks Adam. I still am not good at answering questions yet I guess. I am not good at explaining..:-)

    -Roy

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

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