CODEWITHSUNDEEP
sqloracle-database
Clement Ros
Clement Ros

Reputation: 329

SQL - Get only data from 2 days ago

I want to get data from n days ago to 2 days ago (ealiest data).

Here is my SQL request : 

SELECT CDCATEGORY, DTLOGDATE
FROM ERRORS

How can i do that ?

Upvotes: 0

Views: 1592

Answers (5)

Popeye
Popeye

Reputation: 35900

You need to calculate the last time till when you want the data.

You want data before 12-dec-2019 00:00:00 which will include all the data on or before 11-dec-2019.

Use following query:

SELECT CDCATEGORY, DTLOGDATE FROM ERRORS WHERE DTLOGDATE < TRUNC(SYSDATE) - 1;

Cheers!!

Upvotes: 0

Praveen ND
Praveen ND

Reputation: 560

Please try to make use of below code :

DECLARE @ERRORS TABLE (CDCATEGORY NVARCHAR(100),DTLOGDATE DATE)
DECLARE @StartDate DATE = GETUTCDATE() - 10, -- Your 'n' days
        @EndDate   DATE = GETUTCDATE() - 02

INSERT INTO @ERRORS
VALUES
('A','2019-12-01'),
('B','2019-12-05'),
('Z','2019-12-06'),
('R','2019-12-07'),
('Y','2019-12-10'),
('A','2019-12-11'),
('G','2019-12-12'),
('C','2019-12-13')

SELECT
    CDCATEGORY, 
    DTLOGDATE
FROM
    @ERRORS
WHERE 
    DTLOGDATE BETWEEN @StartDate AND @EndDate

Upvotes: 0

SRG
SRG

Reputation: 345

this code will give you last 2 day's data from database(DTLOGDATE) last date.

SELECT CDCATEGORY,  DTLOGDATE
FROM ERRORS
WHERE DTLOGDATE > (SELECT DATEADD(day, -2, MAX(DTLOGDATE) FROM ERRORS)

Upvotes: 0

zip
zip

Reputation: 4061

Try this:

SELECT CDCATEGORY, DTLOGDATE
FROM ERRORS where DTLOGDATE DTLOGDATE >= SYSDATE - n and DTLOGDATE <= SYSDATE - 2

Upvotes: 0

GMB
GMB

Reputation: 222432

In Oracle you can substract days from a date.

So assuming that DTLOGDATE is a date, you can just do:

SELECT CDCATEGORY, DTLOGDATE FROM ERRORS WHERE DTLOGDATE > SYSDATE - 2

Or possibly, if you want the entire day:

SELECT CDCATEGORY, DTLOGDATE FROM ERRORS WHERE DTLOGDATE > TRUNC(SYSDATE) - 2

Upvotes: 1

Related Questions