CODEWITHSUNDEEP
mysqlsqldatabase
Rocky
Rocky

Reputation: 527

Using INSERT ... SELECT with Date format

I am using an INSERT ... SELECT query in my MySQL. Now, in my database is a column named "medDate" which I use for my medicine Inventories app. This has a type Varchar and is formatted in this way, "July 2014". Now I want to use the insert...select query to copy the previous month's records. But as I test my query to MySQL, there's an error which says incorrect datetime value. Can you help me with this? This is my query.

INSERT INTO medicinesinventory (itemName, compCode, classID, 
 medDate, price, beginningIn, newIn, 
 outMed, sales) SELECT DISTINCT(itemName), 
 compCode, classID, CURDATE(), 
 price, 0.0, 0, 0.0, 0.0 
 FROM medicinesinventory 
 WHERE YEAR(medDate) = DATE_FORMAT(CURRENT_DATE - INTERVAL 1 MONTH, '%M %Y') 
 AND MONTH(medDate) = DATE_FORMAT(CURRENT_DATE  - INTERVAL 1 MONTH,  '%M %Y');

SAMPLE DATA

compCode medID classID medDate   itemname  price beginningIn newIn outMed sales
GOLDEN     148   20    July 2014 sample    0.00   0          0.00   0.00    6.00

Upvotes: 0

Views: 676

Answers (3)

VMai
VMai

Reputation: 10336

As medDate is a string and not a DATETIME you can't use YEAR on this column, but you could convert the right side with DATE_FORMAT and to be consistent you should store the values in the same format.

INSERT INTO medicinesinventory (itemName, compCode, classID, 
 medDate, price, beginningIn, newIn, 
 outMed, sales) SELECT DISTINCT(itemName), 
 compCode, classID, 
 DATE_FORMAT(CURDATE(), '%M %Y'),  -- formatted this date
 price, 0.0, 0, 0.0, 0.0 
 FROM medicinesinventory 
 -- and converted those in the where clause
 WHERE medDate = DATE_FORMAT(CURRENT_DATE - INTERVAL 1 MONTH, '%M %Y')

Note

It would be better, if you could change the data type of the medDate column to DATE.

Upvotes: 0

spencer7593
spencer7593

Reputation: 108400

The YEAR() and MONTH() functions operate on expressions of datatype DATE, DATETIME and TIMESTAMP.

If those functions are used on a VARCHAR expression, I believe MySQL will perform an implicit conversion of the VARCHAR to DATE (or DATETIME), expecting format to be 'YYYY-MM-DD' (or 'YYYY-MM-DD %h:%i:%s')

I believe that's where the "incorrect datetime value" error is being thrown.

Just use an expression that generates the VARCHAR value you want to match 'July 2014', and compare the VARCHAR values:

WHERE medDate = DATE_FORMAT(CURRENT_DATE - INTERVAL 1 MONTH, '%M %Y')

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269753

The functions year() and month() require dates. You can get the dates using str_to_date() because MySQL supports "partial" dates (i.e. those without days. So, try this:

WHERE YEAR(str_to_date(meddate, '%M %Y')) = YEAR(CURRENT_DATE - INTERVAL 1 MONTH) AND
      MONTH(str_to_date(meddate, '%M %Y')) = MONTH(CURRENT_DATE  - INTERVAL 1 MONTH)

Alternatively, you seem to want to format the previous month in the same format and do the comparison. That can also work:

WHERE medDate = DATE_FORMAT(CURRENT_DATE - INTERVAL 1 MONTH, '%M %Y')

Upvotes: 2

Related Questions