Results 1 to 9 of 9

Thread: Datediff

  1. #1
    Join Date
    Jul 2011
    Posts
    33

    Unanswered: Datediff

    Hi everyone. I am looking to compare dates in SQL - do a bit of arithmetic on them. What I have is:

    Two events - Buying a car, and Servicing the car. I am interested in the people who have had to take their car in for a service within one month of buying their car. So the code I have is:


    Code:
    SELECT * FROM
    
    (SELECT DISTINCT Customer, Event_type, Event_date
    FROM Database
    WHERE EVENT_type = "Purchase") A
    
    INNER JOIN 
    
    (SELECT DISTINCT Customer, Event_type, Event_date
    FROM Database
    WHERE EVENT_type = "Service") B
    
    -- bit of code I am struggling with below
    WHERE B.Event_date - A.Event_date BETWEEN '1' AND '30'

    So my logic is that if the service date - the purchase date is between 1 and 30, then they have taken their car in for a service within the first 30 days of having it.

    Any help with that last line of code?

    Thanks in advance!

  2. #2
    Join Date
    Jul 2011
    Posts
    33

    A little progress

    I have altered the last line to

    Code:
    WHERE B.EVENT_DT - A.EVENT_DT <= 30
    but this does not take into account they years and months. I am currently getting dates that the days are up to 30 days apart, the results bring back different years from past cars. But I need the years and months to be fixed (unless of course the car was bought in a different month)

  3. #3
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    try days(B.EVENT_DT) - days(A.EVENT_DT) <= 30
    this should help
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

  4. #4
    Join Date
    Jul 2011
    Posts
    33

    datediff

    When I run that query, I am getting dates like:

    Purchase Date-----Service Date

    2001-10-13 -------2004-04-26

    which is fine on the day criteria, but as you can see, the service date here is not within 30 days after the purchase date, it is 2 years, 6 months AND within 30 days. Is there anyway around this?

  5. #5
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    DAY and DAYS are completely different functions.

  6. #6
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    have you tried DAYS (returns date translated in nbr of days) and not DAY (returns the day of this date)
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

  7. #7
    Join Date
    Jul 2011
    Posts
    33

    tried both

    Both DAY and DAYS are bringing back dates which are over 30 days apart (ie three years apart or a number of months apart). I even checked the day column alone by changing the day diff to 10, and I am getting dates where the number of days difference is greater than 10.

    am I putting the where clause in the correct place?

  8. #8
    Join Date
    Jul 2011
    Posts
    33

    solved it

    I combined it with the same querys for year and months according to my criteria and it worked fine.

  9. #9
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    you are joining the table, but not specifying the join condition
    on a.customer=b.customer
    something like this :
    SELECT * FROM
    FROM Database A, database b
    WHERE A.EVENT_type = "Purchase"
    and B.EVENT_type = "Service"
    and a.customer = b.customer
    and days(B.Event_date) - days(A.Event_date) BETWEEN 1 AND 30
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •