Post reply

Help with Datediff function

  • March 30, 2010 at 1:14 pm

    #213222

    Hi Everyone,

    I have a table called "PaymentsSchedule" as below:

    IDSequenceScheduleDateAmount

    4909712008-01-10 00:00:00.000333.33

    4909822009-01-10 00:00:00.000333.33

    4909932010-01-10 00:00:00.000333.34

    ------------------------------------------------------------------------------------------------

    I have another table called "Payments" as below:

    IDPaymentDatePaymentAmount OrganizationID

    13999992007-10-31 00:00:00.000 50.001

    ------------------------------------------------------------------------------------------------

    According to the Payments table, customer made a payment of only $50.00 on 2007-10-31 00:00:00.000

    Now I want to find out the number of days due for a payment.

    Please help me.

    Thanks in advance.

  • March 30, 2010 at 1:19 pm

    #1142617

    Can you give an example of the result set you are looking for? Also, how are these 2 tables related?

    John Rowan

    ======================================================
    ======================================================
    Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

  • March 30, 2010 at 1:54 pm

    #1142655

    I also have a column called customerid in "PaymentsSchedule" table whose value is 3, which I forgot to mention earlier.

    I have a table called "PaymentDetail" as below:

    IDPaymentIDSequenceOrderIDPaymentAmount

    151390313999991123450.00

    ----------------------------------------------------------------------------------------------------------------------

    Also I have another table called "Customer" as below:

    CustomerID TotalAmountStartDateEndDateNumberOfPaymentsFrequencyOrderID

    3 1000.002008-01-10 00:00:00.0002010-01-10 00:00:00.0003Annually 1234

    AS of now, I want to see the number of days between 2008-01-10 00:00:00.000 and getdate()

    Thank You!

  • March 30, 2010 at 1:58 pm

    #1142661

    DECLARE @Date datetime

    SET @Date = '2008-01-10 00:00:00.000'

    SELECT DATEDIFF(dd, @Date, GETDATE())

    John Rowan

    ======================================================
    ======================================================
    Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

  • March 30, 2010 at 2:00 pm

    #1142663

    i dint know how to map your tables but the basic SQL would look like this if you want no of days from startdate to current date

    SELECT C.* , DATEDIFF(Day, C.StartDate,getdate()) FROM Customer C

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

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