In Query, what is the Criterion Syntax for the for the previous day record

[AMPVRAM]

In Query, what is the Criterion Syntax for the for the previous day record

[frofa]

Give us an example of a query you have tried out.

[AMPVRAM]

i tried the following syntax
DATE_SUB(CURDATE(), INTERVAL 1 DAY) but i get syntax error ,

The DATE format entered in the Table is DD/MM/YY , am able to execute CURDATE() to rerieve the current date record , but my purpose is to retrieve the yesterdays record

the following is the SQL Code SELECT “DATE” AS 'DATE" FROM "DAILY LOG " TABLE WHERE “DATE” = ____ ???

[mgl]

Hello @AMPVRAM

Assuming the timestamp of each record is a date, not a complete timestamp, with date and time, called “Date”, in a table called “Table” (let’s free our imagination!)

You may use the following syntax : Select *, DAYOFYEAR("Date") = DAYOFYEAR(CURDATE())-1 from "Table"

Kind regards, Michel

[Ratslinger]

Hello,

This doesn’t appear to work if current date is first day of the year:

'2020-01-01' = 1 - 1 = 0

[mgl]

Hello @Ratslinger

You’re perfectly right. I propose this one :

SELECT *
FROM "Table"
WHERE 
DAYOFYEAR("Date") = DAYOFYEAR(CURDATE()) - 1 OR (DAYOFYEAR("Date") = DAYOFYEAR(CAST(YEAR(CURDATE())||'-12-31' AS DATE)) AND DAYOFYEAR(CURDATE()) = 1 AND YEAR("Date") = YEAR(CURDATE()) - 1)

[Ratslinger]

@mgl,

It would be much better if using a more up-to-date database. Unfortunately HSQLDB embedded (v1.8) (of course this is supposition as OP never stated database in use) is lacking in date math as well as many other areas.

While your new proposal fixed one problem, new problem appeared - leap year. The problem portion is:

DAYOFYEAR(CAST(YEAR(CURDATE())||'-12-31' AS DATE)

and I believe this will fix it:

DAYOFYEAR(CAST(YEAR(CURDATE()) - 1 ||'-12-31' AS DATE)

So full statement with this change is:

SELECT *
FROM "Table"
WHERE 
DAYOFYEAR("Date") = DAYOFYEAR(CURDATE()) - 1 OR (DAYOFYEAR("Date") = DAYOFYEAR(CAST(YEAR(CURDATE()) - 1 ||'-12-31' AS DATE)) AND DAYOFYEAR(CURDATE()) = 1 AND YEAR("Date") = YEAR(CURDATE()) - 1)

[mgl]

@Ratslinger

You’re right! Then the last proposal may even be improved by redundancy removal.

Why I did not try to find a nice formula is because I try to split things in easier pieces to understand, at least for me. I started by looking for is “Date” the last day of its year (and then I should have written

OR (DAYOFYEAR("Date") = DAYOFYEAR(CAST(YEAR("Date") ||'-12-31' AS DATE)) 

and it made sense to check also for the current date to be the first day of the current year by

AND DAYOFYEAR(CURDATE()) = 1 AND YEAR("Date") = YEAR(CURDATE()) - 1

Now, as you proposed

(DAYOFYEAR("Date") = DAYOFYEAR(CAST(YEAR(CURDATE()) - 1 ||'-12-31' AS DATE))

the “Date” is obviously the last day of the past year and then there is no need to check for

AND YEAR("Date") = YEAR(CURDATE()) - 1

[mgl]

Eventually, I propose two equivalent forms:

SELECT *
FROM "Table"
WHERE DAYOFYEAR("Date") = DAYOFYEAR(CURDATE()) - 1 OR (DAYOFYEAR("Date") = DAYOFYEAR(CAST(YEAR("Date") ||'-12-31' AS DATE)) AND DAYOFYEAR(CURDATE()) = 1 AND YEAR("Date") = YEAR(CURDATE()) - 1)

or

SELECT *
FROM "Table"
WHERE DAYOFYEAR("Date") = DAYOFYEAR(CURDATE()) - 1 OR (DAYOFYEAR("Date") = DAYOFYEAR(CAST(YEAR(CURDATE()) - 1 ||'-12-31' AS DATE)) AND DAYOFYEAR(CURDATE()) = 1 )

Kind regards, Michel

[Ratslinger]

@mgl,

Only need the second one

[AMPVRAM]

curdate() - 1 is not working at all , am using Libre office Version: 5.3.1.2
Build ID: e80a0e0fd1875e1696614d24c32df0f95f03deb2
CPU Threads: 4; OS Version: Windows 6.2; UI Render: default; Layout Engine: new;
Locale: en-IN (en_IN); Calc: group

[AMPVRAM]

my requirement is simple , want to retrieve yesterdays record from the Table , the field name text that is created in the Table is DATE

[AMPVRAM]

DAYOFYEAR(“DATE”) = DAYOFYEAR(CURDATE()) - 1 is working fine thanks you guys…