Calculating 2 weeks for every week SQL

Question

I'm still new to SQL so this question I am asking may be easy for you. So I am creating a report that for every week generates the prior 14 days (Or 2 weeks) of the funded contracts. I know this has to be Hardcoded to a specific company. The ID for that specific company is '55' So can someone help me with this function? My query I know is not yet finished I am just stuck on how to enter the Date function for this.

Create PROC [dbo].[spAdminFundedDateee]

   Begin

        SELECT  c.program_id, d.dealer_code,b.last_name As DealerName, a.account_no, a.vin, 
                ((e.last_name)+','+(e.first_name)) As Name, a.funded_date, a.cancel_refund_date,
                  a.purchase_date,a.miles, a.duration,a.sale_price,a.number_of_payments,
                  a.sales_tax, a.downpayment

        from tDealer d 
    Join tContact b ON d.contact_id = b.contact_id 
    Join tContract a On d.dealer_id = a.dealer_id 
    Join tCompany c ON d.company_id= c.company_id
    Join tContact E On e.contact_id = a.contact_id

        Where c.program_id = 55 And a.funded_date between 

      End

    exec spAdminFundedDateee '05/1/2014','05/30/2014','55'

Answer 1

In order to check if a.funded_date is between today's date and two weeks ago, you are going to need several sql server functions. The first one is GetDate(). This returns the current date and time as a datetime value.

Now, you want to check only for the date parameter (not the time). If someone runs your stored procedure at 1pm, you don't want to eliminate all of the data from before 1pm from the day 14 days ago. You want all the data, no matter the time, beginning from 14 days ago. To solve this issue, we want to change getDate() to a date only. so, cast(getDate() as date). Today, this would return 6-18-14.

Lastly, you want to check for the date two weeks ago. dateAdd allows you to add any amount of time that you specify to a date or a time. in this case, you want the information from 14 days ago. this is going to look like dateadd(dd, -14, cast(getDate() as date)).

Since between is inclusive, all you need to do now is put it together!

between dateadd(dd, -14, cast(getDate() as date)) and cast(getDate() as date)