Select records for the last 24 hours

[Eberman]

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.

[gandolf989]

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.

[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

[Eberman]

Thank you! It works.

[gandolf989]

For the record, there is no difference between the two statements.

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;

[TomazZ]

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.

[SANJAY_G]

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 ??

[tauqirhussain]

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???

[gbrabham]

This was an Oracle thread ... you are correct ... those functions are not valid in SQL Server ...

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