CODEWITHSUNDEEP
mysqlsqlnode-mysql
Gabriel
Gabriel

Reputation: 257

Get yesterday date in SQL

i am try to request date only yesterday but without success...

My query request.

SELECT registeredDaySell FROM new_sell WHERE DATE_SUB(CURDATE(), INTERVAL 1 DAY)

My date is organized this way.

16 September, 2017

Thanks for helping me out.

Upvotes: 3

Views: 23316

Answers (4)

Naveen Chathuranga
Naveen Chathuranga

Reputation: 38

SELECT producFinalPrice 
FROM new_sell
WHERE WEEK (date) = WEEK( current_date ) - 1

Upvotes: 0

Dismi Paul
Dismi Paul

Reputation: 205

subdate(now(),1) will return yesterdays timestamp The below code will select all rows with yesterday's timestamp from employee_login page

Select * FROM `employee_login` WHERE `dattime` <= subdate(now(),1) AND `dattime` > subdate(now(),2)

The below code will display yesterday's timestamp 

Select subdate(now(),1) ,subdate(now(),2))

This will give

enter image description here

Upvotes: 1

Jenn Briden
Jenn Briden

Reputation: 81

SELECT producFinalPrice FROM new_sell 
WHERE where date >= DATEADD(day, -1, convert(date, GETDATE()))
   and date < convert(date, GETDATE())

-1 equates to "today" minus 1 day. You can change that number to get the number of days that you want to go back if further than 1.

Upvotes: 1

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522762

As @Gordon mentioned, you should consider storing your dates either in some date type column, or possibly as a UNIX timestamp (seconds since the epoch). A possible workaround here would be to use STR_TO_DATE to convert your string dates to bona fide dates on the fly.

SELECT
    producFinalPrice
FROM new_sell
WHERE
    STR_TO_DATE(date_col, '%d %M, %Y') = DATE_SUB(CURDATE(), INTERVAL 1 DAY)

This assumes that date_col is the name of the column in your table which contains string dates.

Upvotes: 0

Related Questions