Updating data when date is in condition

Question

I am facing an issue to update the dataset with data type is date in Condition

Script:

UPDATE blse
   SET employment = 2066.3
 WHERE date = (i am not sure how)
   AND industry = 'Total Nonfarm'
   AND state = 'Alabama'
;

usereeXl1 · Answer

The Date type formate is YYYY-MM-DD

Example:

SELECT * FROM posts WHERE date = "1982-02-22";

so the command must be like

UPDATE blse 
SET employment = 2066.3 
WHERE 
    date = "2022-09-22" AND 
    industry = 'Total Nonfarm' AND 
    state = 'Alabama';

Cosmin Gruian · Answer

I would recommend to use to_date() if you are casting from a string to accommodate a specific format. Example:

SELECT to_date('2000/JUN','YYYY/MON'); -- ok
SELECT '2000/JUN'::date; -- error