Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2006
    Posts
    21

    Red face Unanswered: comparing dates(Minutes, Hours etc)

    guys - is this a decent query to pull all columns (dateCreate)
    that have a timestamp less than five minutes?
    i know its simple, but i've never done a date compare with minutes or hours
    in sql server
    thanks
    rik



    select top 10 * from ptpuritm
    where datediff(MINUTE,dateCreate,getdate()) <=5



    select top 10 * from ptpuritm
    where datediff(MINUTE,dateCreate,current_timestamp) <=5

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Type CTRL+K and look at the execution plans for both of the following examples

    Code:
    USE Northwind
    GO
    
    SET NOCOUNT ON
    CREATE TABLE myTable99 (dateCreate datetime)
    GO
    
    CREATE INDEX myIndex99 ON myTable99(dateCreate)
    GO
    
    
    INSERT INTO myTable99(dateCreate)
    SELECT '12/31/1999 23:00:00' UNION ALL
    SELECT '12/31/1999 23:10:00' UNION ALL
    SELECT '12/31/1999 23:20:00' UNION ALL
    SELECT '12/31/1999 23:30:00' UNION ALL
    SELECT '12/31/1999 23:40:00' UNION ALL
    SELECT '12/31/1999 23:50:00' UNION ALL
    SELECT '12/31/1999 23:55:00' UNION ALL
    SELECT '12/31/1999 23:56:00' UNION ALL
    SELECT '12/31/1999 23:57:00' UNION ALL
    SELECT '12/31/1999 23:58:00' UNION ALL
    SELECT '12/31/1999 23:59:00' UNION ALL
    SELECT '12/31/1999 23:59:59'
    GO
    
     
    SELECT *
      FROM myTable99
     WHERE datediff(MINUTE,dateCreate,'1/1/2000 00:00:00') <=5
    
    SELECT *
      FROM myTable99
     WHERE dateCreate <= dateadd(MINUTE,-5,'1/1/2000 00:00:00')
    
    GO
    
    SET NOCOUNT OFF
    DROP TABLE myTable99
    GO
    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.

  3. #3
    Join Date
    Jan 2006
    Posts
    21

    dateDif

    Brett, thanks so much for the help on this. Coming from an Oracle background, i can tell you i'm growing to appreciate SQL SERVER each day.
    thanks
    again

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    You like that?

    Look here

    http://www.sqlteam.com/
    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.

Posting Permissions

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