-
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.
-
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.
-
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
-
-
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;
-
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
-
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"
-
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
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|