CODEWITHSUNDEEP
sqloracle-database
Gioli
Gioli

Reputation: 55

How to get data from previous day

I want to get data from the previous day, but my SQL Statements doesn't work. I get an error:

ORA-00904: "DATEDIFF": invalid identifier

SELECT * 
FROM table_name 
WHERE DATEDIFF(day,table_name .creation_dt ,GETDATE()) = 1;

SELECT *
FROM table_name 
WHERE DATEDIFF(DAY, DATEADD(DAY, -1 , CURRENT_TIMESTAMP), creation_dt) = 0;

SELECT *
FROM table_name 
WHERE `date` = creation_dt.CURDATE() - INTERVAL 1 DAY;

thanks Khati

Upvotes: 0

Views: 435

Answers (4)

Gordon Linoff
Gordon Linoff

Reputation: 1269593

I want to get data from the previous day,

In Oracle, I would recommend:

where table_name.creation_dt >= trunc(sysdate) - interval '1' day and
      table_name.creation_dt < trunc(sysdate)

This can make use of an index on creation_dt. I also recommend that you use the interval syntax for working with dates. Although you can use trunc(sysdate) - 1, that looks old-fashioned to me (Oracle did not always support interval).

You can also phrase this more concisely as:

where trunc(table_name.creation_dt) = trunc(sysdate) - interval '1' day

However, that might be a little bit more challenging for the optimizer.

Upvotes: 1

Littlefoot
Littlefoot

Reputation: 142705

As DATE datatype in Oracle contains both date and time, and as "previous day" starts at 00:00:00 hours (:min:sec), then truncating SYSDATE "resets" time to mentioned 00:00:00, while subtracting 1 day moves you to midnight at start of yesterday's day.

SQL> select trunc(sysdate) - 1 from dual;

TRUNC(SYSDATE)-1
-------------------
18.06.2020 00:00:00

So:

select *
from your_table
where date_column >= trunc(sysdate) - 1;

Upvotes: 1

Jānis Freibergs
Jānis Freibergs

Reputation: 36

For oracle previous day you could get using sysdate - 1 

select * from table_name where trunc(date) = trunc(sysdate)-1

Upvotes: 2

Volodymyr Puzdriak
Volodymyr Puzdriak

Reputation: 139

DATEDIFF is SQL Server specific function. Do you use SQL Server or another Database Engine?

Upvotes: 0

Related Questions