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.
Printable View
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.
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. :cool:
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
Thank you! It works.
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;
Look, left and right expression in where predicate are of different datatypes here.Quote:
Originally posted by gandolf989
SELECT TO_char(date_field) FROM TEST_TABLE Where
TO_char(date_field) > SYSDATE - 1;
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.
Isn't it same as SYSDATE-1 ??Quote:
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
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???
This was an Oracle thread ... you are correct ... those functions are not valid in SQL Server ...
however: .... how about try getdate()-1