Results 1 to 7 of 7
  1. #1
    Join Date
    Mar 2003
    Location
    Memphis, TN, USA
    Posts
    61

    Post Unanswered: Retrieving last 12/24/36 hrs records

    Hi there !!
    I am new on this block. Can anyone help me to make sql queries which can retrieve last 12 hrs / last 24 hrs / last 36 hrs records.

    I tried to make the logic for the last 3 hrs but yet not able to get the data which i want. I am using MS SQL Server 2000 and ASP.

    My table named "campaign" contains the field 'camdate' which is of smalldatetime datatype.

    Also, can i am able to retrieve the time from the same field (camdate)? if yes then how ??

    Any suggestion is most welcome.

    With Thanks
    Last edited by sqlboy; 12-17-03 at 03:30.

  2. #2
    Join Date
    Dec 2003
    Location
    Mumbai
    Posts
    7

    Re: Retrieving last 12/24/36 hrs records

    hi

    SELECT *
    FROM Table1
    where DATEDIFF(hh, camdate, GETDATE()) >= 12

    for last 12 hours

    regards,
    Gautam Vegad

    Originally posted by sqlboy
    Hi there !!
    I am new on this block. Can anyone help me to make sql queries which can retrieve last 12 hrs / last 24 hrs / last 36 hrs records.

    I tried to make the logic for the last 3 hrs but yet not able to get the data which i want. I am using MS SQL Server 2000 and ASP.

    My table named "campaign" contains the field 'camdate' which is of smalldatetime datatype.

    Also, can i am able to retrieve the time from the same field (camdate)? if yes then how ??

    Any suggestion is most welcome.

    With Thanks

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    I think this will run faster, because it should only need to calculate dateadd(hh, -12, GETDATE()) once instead of calculating datediff for every record:

    SELECT *
    FROM Table1
    where camdate >= dateadd(hh, -12, GETDATE())

    Try it and see if the execution plan is different.

    blindman

  4. #4
    Join Date
    Mar 2003
    Location
    Memphis, TN, USA
    Posts
    61
    Thanks to both of you..

    Being a kid, i am not able to find the difference between dateadd and datediff. Can you put some light on it ? I shall be very greatful to you.

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Well, I ran one test on some data that I have, and the dateadd method was about 30% faster.

    As I said, the difference is that the server calculates the result of dateadd once for the entire statement, while it must calculate the result of datediff once for every row.

    blindman

  6. #6
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Well....maybe...

    But the first one is a stage 2 predicate (non-sargable)..it'll have to do a table (or index) scan..

    Blindman's has the ability to use an Index (stage 1, saragable)

    And once you start talking volumes, the performance will be noticable...

    MOO (well not really)
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  7. #7
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Good point. I forgot about the sargableosityness of the two statements.

    blindman

Posting Permissions

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