-3

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'
;
J Spratt
  • 1,762
  • 1
  • 11
  • 22
RAKANiD6
  • 1
  • 1
  • https://stackoverflow.com/help/how-to-ask – J Spratt Sep 16 '22 at 02:53
  • even if the date was represented as 1/03/19? – RAKANiD6 Sep 16 '22 at 03:59
  • We don't know what the date in the table looks like but if it's stored as a string that's a mistake in my opinion. You should always use the date/time data types: https://www.postgresql.org/docs/current/datatype-datetime.html. `WHERE date::DATE = '01/03/19'::DATE`. When you cast to `DATE`, Postgres uses ISO format which is also the recommended way to store date/times. To see an example: `SELECT '01/03/19'::DATE;` – J Spratt Sep 16 '22 at 04:54
  • So that dataset is 4 columns Date (date) | employment (real) | industry (Character varying | state (Character varying) So I tried to use: ="UPDATE blse SET employment = "&B2&" WHERE date = "&A2&" AND industry= "&C2&" AND state="&D2&" ; " But it did not work. ERROR: syntax error at or near "Nonfarm" LINE 1: ...2066.3 WHERE date=43496:: DATE AND industry=Total Nonfarm AN... – RAKANiD6 Sep 17 '22 at 07:28

2 Answers2

0

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';
paradox
  • 1
  • 1
  • You may need to cast the comparator from a string to type `DATE`. Depending on how the date field's data type. `WHERE date = '2022-09-22'::DATE` – J Spratt Sep 16 '22 at 03:31
  • ="DELETE FROM blse WHERE date='2005-01-31'::DATE AND employment_1000s="&B2&" AND industry='"&C2&"' AND state='"&D2&"' ;" it tells me that industry column does not exist. – RAKANiD6 Sep 17 '22 at 21:56
0

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

Check doc on to_date function: https://www.postgresql.org/docs/current/functions-formatting.html

Cosmin Gruian
  • 51
  • 3