DBAsupport.com Forums - Powered by vBulletin
Results 1 to 9 of 9

Thread: Select records for the last 24 hours

  1. #1
    Join Date
    Nov 2000
    Posts
    57

    Select records for the last 24 hours

    I need some help on writing SQL to get data for last 24 hours. For example
    SELECT TO_char(date_field) FROM TEST_TABLE Where ??

    What should I use in WHERE clouse to get desire result.

    Thanks to anybody who would be able to help me.

  2. #2
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,166
    I think this is what you are looking for.

    SELECT TO_char(date_field) FROM TEST_TABLE Where
    TO_char(date_field) > SYSDATE - 1;

    subtractict 1 from SYSDATE subtracts 1 day. you could also subtract 1/24 which subtracts 1 hour, 2/24 subtracts 2 hours etc.

  3. #3
    Join Date
    Apr 2003
    Location
    South Carolina
    Posts
    148
    The following SQL gives you exactly 24 hours from the time the
    trx is entered...

    SELECT TO_CHAR(sysdate-1440/1440,'DD-MON-YY HH:MI:SS pm') FROM DUAL

    Gregg

  4. #4
    Join Date
    Nov 2000
    Posts
    57
    Thank you! It works.

  5. #5
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,166
    For the record, there is no difference between the two statements.

    Code:
    SQL> SELECT TO_CHAR(sysdate-1440/1440,'DD-MON-YY HH:MI:SS pm') FROM DUAL;
    
    TO_CHAR(SYSDATE-1440/
    ---------------------
    19-JUN-03 03:45:54 pm
    
    SQL> SELECT TO_CHAR(sysdate-1,'DD-MON-YY HH:MI:SS pm') FROM DUAL;
    
    TO_CHAR(SYSDATE-1,'DD
    ---------------------
    19-JUN-03 03:45:54 pm
    
    So where TO_char(date_field) > SYSDATE - 1; is the same as
       where TO_char(date_field) > SYSDATE - 1440/1440;

  6. #6
    Join Date
    Jul 2001
    Location
    Slovenia
    Posts
    422
    Originally posted by gandolf989
    SELECT TO_char(date_field) FROM TEST_TABLE Where
    TO_char(date_field) > SYSDATE - 1;
    Look, left and right expression in where predicate are of different datatypes here.
    I wonder what will happen? If right expression implicitly converts to char, then this is wrong.

    Bottom line: get rid of this to_char in where clause.
    Tomaž
    "A common mistake that people make when trying to design something completely
    foolproof is to underestimate the ingenuity of complete fools" - Douglas Adams

  7. #7
    Join Date
    Feb 2000
    Location
    Singapore
    Posts
    1,758
    Originally posted by gbrabham
    The following SQL gives you exactly 24 hours from the time the
    trx is entered...

    SELECT TO_CHAR(sysdate-1440/1440,'DD-MON-YY HH:MI:SS pm') FROM DUAL

    Gregg
    Isn't it same as SYSDATE-1 ??
    Sanjay G.
    Oracle Certified Professional 8i, 9i.

    "The degree of normality in a database is inversely proportional to that of its DBA"

  8. #8
    Join Date
    May 2009
    Posts
    1
    i got following error in SQL 2000
    'TO_CHAR' is not a recognized function name.
    also the sysdate function is not working in SQL 2000

    so how can i get records entered in last 24 hours???

  9. #9
    Join Date
    Apr 2003
    Location
    South Carolina
    Posts
    148
    This was an Oracle thread ... you are correct ... those functions are not valid in SQL Server ...

    however: .... how about try getdate()-1

Posting Permissions

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


Click Here to Expand Forum to Full Width