3

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.

Gabriel
  • 237
  • 1
  • 7
  • 19
  • 2
    You should store dates as dates, not strings. – Gordon Linoff Sep 19 '17 at 01:37
  • @GordonLinoff kind `INTERVAL 5 DAY`? can you show me an example? – Gabriel Sep 19 '17 at 01:38
  • the where clause doesn't compare the data to the calculated date, it needs operators such as >= – Paul Maxwell Sep 19 '17 at 01:42
  • (probably) your main problem is that you're not actually specifying a column in your `where` clause... How about `WHERE name_of_date_column >= DATE_SUB(CURDATE(), INTERVAL 1 DAY)` ? – trs Sep 19 '17 at 01:44
  • 1
    @trs my back end process `SELECT registeredDaySell FROM new_sell WHERE registeredDaySell >= DATE_SUB(CURDATE(), INTERVAL -1 DAY)` but she's just returning it in my json `[]` – Gabriel Sep 19 '17 at 02:22

4 Answers4

1

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

Dismi Paul
  • 187
  • 1
  • 5
0

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.

Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360
  • i give error of syntax definition `Uncaught Exception: SyntaxError: ../server/index.js: Unexpected token` this my router get `router.get('/sell/home-card-last-sell-yesterday', function (req, res) { connection.query('SELECT producFinalPrice FROM new_sell WHERE STR_TO_DATE(date_col, '%d %M, %Y') >= DATE_SUB(CURDATE(), INTERVAL 1 DAY)', function (err, lastSellYesterdayCardHome, fields) { if (err) { throw err; } res.send(lastSellYesterdayCardHome); } ); });` – Gabriel Sep 19 '17 at 01:45
  • @GabrielCorrea There are unescaped single quotes in your string, that won't work. Try wrapping the entire query in _double_ quotes, e.g. `connection.query("YOUR QUERY HERE")` – Tim Biegeleisen Sep 19 '17 at 01:47
  • i must change `date_col` to my `producFinalPrice`? – Gabriel Sep 19 '17 at 02:12
  • i change to double quotes but given error in column `ER_BAD_FIELD_ERROR: Unknown column 'date_col' in 'where clause'` – Gabriel Sep 19 '17 at 02:17
  • @GabrielCorrea I don't know your schema. Replace `date_col` with the name of the column in your table which stores the dates for each record. – Tim Biegeleisen Sep 19 '17 at 02:22
  • i am not storage data to current-time-stamp my date record in data picker to my form send date to my table in sql. my back end process request but my response json is `[]` – Gabriel Sep 19 '17 at 02:30
  • Update your question with what query you are actually running. – Tim Biegeleisen Sep 19 '17 at 03:32
  • already update replace `producFinalPrice` to `registeredDaySell` – Gabriel Sep 19 '17 at 04:11
0
SELECT producFinalPrice 
FROM new_sell
WHERE WEEK (date) = WEEK( current_date ) - 1
Mad Dog Tannen
  • 7,129
  • 5
  • 31
  • 55
Naveen Chathuranga
  • 38
  • 7
  • `Uncaught Exception: Error: ER_BAD_FIELD_ERROR: Unknown column 'date' in 'where clause'` I must fill out with my producFinalPrice in `(date) =`? – Gabriel Sep 19 '17 at 02:07
0
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.

Jenn Briden
  • 41
  • 5