[MDEV-20520] Adding +1 to a date give error Created: 2019-09-07  Updated: 2019-09-16  Resolved: 2019-09-12

Status: Closed
Project: MariaDB Server
Component/s: Temporal Types
Affects Version/s: 10.3.15
Fix Version/s: N/A

Type: Bug Priority: Major
Reporter: Akshat Assignee: Alexander Barkov
Resolution: Not a Bug Votes: 0
Labels: None
Environment:

Windows 10 Home

Attachments: PNG File sql error 2.png     PNG File sql error 3.png     PNG File sql error.png    

 Description   

I have a table with 2 date fields.
I used the following query to update one

update day19 set bdate = ddate+1 where narration like "th card%"

I received the errors attached.
I altered the date of the row giving error using sql manager but the error shifted to 31st May.

It seems the date calculator gets stuck at month ends.

Since there are no specific data files or error logs required I have not attached them.

If required I can send them

 Comments   
Comment by Alexander Barkov [ 2019-09-12 ]

ddate+1 treats ddate as a mumber, and the result of this expression is a number. It can produce values like 20190431 or 20190532 which cannot be translated back to DATE, hence the error.

In order to do DATE arithmetic, please use this syntax with INTERVAL:

SELECT CAST(DATE'2001-05-31'+INTERVAL 1 DAY AS DATE);


+-----------------------------------------------+
 
| CAST(DATE'2001-05-31'+INTERVAL 1 DAY AS DATE) |
 
+-----------------------------------------------+
 
| 2001-06-01                                    |
 
+-----------------------------------------------+

Notice, it correctly shifts to the next month.

The ALTER query should be:

update day19 set bdate = ddate+interval 1 day where narration like "th card%"

Comment by Alexander Barkov [ 2019-09-16 ]

Here's the relevant documentation:
https://mariadb.com/kb/en/library/type-conversion/#rules-for-conversion-on-dyadic-arithmetic-operations

Comment by Akshat [ 2019-09-16 ]

Thank You for the conversion Links.

It seems to be an error at first but now I see my error.

Regards
Akshat

Generated at Thu Feb 08 09:00:06 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.