using string values for between operator in mysql

Question

I have a table sales having attributes as salesId,salesDate,..etc. The salesId column is a varchar.I need to concat the year value of the salesDate to the salesId based on a condition of the month of the salesDate. I need to do this for a range of around 100 salesID. Eg: old salesId = 7 and corresponding salesDate = '2018-05-07' then new required salesId = '7/2018-2019' So i tried the following :

    update sales
set salesId = case
                when month(salesDate)>=4 then concat(salesId,concat("/",year(salesDate),"-",year(salesDate)+1))
                else
                    concat(salesId,concat("/",year(salesDate)-1,"-",year(salesDate)))
                end
where cast(salesId as unsigned) between "7" and "10";

However, i get the following error:

Error Code: 1292. Truncated incorrect INTEGER value: '1/17-18'

I even tried without using any Cast() as

    update sales
set salesId = case
                when month(salesDate)>=4 then concat(salesId,concat("/",year(salesDate),"-",year(salesDate)+1))
                else
                    concat(salesId,concat("/",year(salesDate)-1,"-",year(salesDate)))
                end
where salesId between "7" and "10";

but in this case the query runs fine but i get:

0 row(s) affected Rows matched: 0  Changed: 0  Warnings: 0

I can't figure out the error or how to proceed.Could someone please provide some guidance in this ? Thanks. Sample data

salesId salesDate
7          2017-05-15
8          2017-06-16
9          2017-07-18
10         2017-08-20

...

Required Result

 salesId                  salesDate
    7/2017-2018         2017-05-15
    8/2017-2018         2017-06-16
    9/2017-2018         2017-07-18
    10/2016-2017        2017-02-20

Please provide sample data and expected results. It is not that clear what you are trying to accomplish here. — GMB
– GMB, Commented Apr 4, 2020 at 18:03

pranay · Accepted Answer · 2020-04-04 19:56:23Z

0

The error which i was getting earlier was actually a warning as stated here. So after using update ignore i was able to execute the query as get expected results. However i would be waiting for a more comprehensive answer as to get it working without the need to ignore. Thanks

answered Apr 4, 2020 at 19:56

pranay

2,3799 gold badges35 silver badges61 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

nbk · Accepted Answer · 2020-04-04 21:17:04Z

0

The field salesId must be text like

You have to change your Update query to include if thecolumn where already update, so that it takes with SUBSTRING_INDEX only the number

 update sales
set salesId = case
                when month(salesDate)>=4 then concat(SUBSTRING_INDEX(salesId,'/',1),concat("/",year(salesDate),"-",year(salesDate)+1))
                else
                    concat(SUBSTRING_INDEX(salesId,'/',1),concat("/",year(salesDate)-1,"-",year(salesDate)))
                end
where cast(SUBSTRING_INDEX(salesId,'/',1) as unsigned) between "7" and "10";

so that your update function can work

CREATE TABLE sales (
  `salesId` VARCHAR(20),
  `salesDate` VARCHAR(10)
);

INSERT INTO sales
  (`salesId`, `salesDate`)
VALUES
  ('7', '2017-05-15'),
  ('8', '2017-06-16'),
  ('9', '2017-07-18'),
  ('10', '2017-08-20');

✓

✓

    update sales
set salesId = case
                when month(salesDate)>=4 then concat(salesId,concat("/",year(salesDate),"-",year(salesDate)+1))
                else
                    concat(salesId,concat("/",year(salesDate)-1,"-",year(salesDate)))
                end
where cast(salesId as unsigned) between "7" and "10";

✓

SELECT *  from sales

salesId      | salesDate 
:----------- | :---------
7/2017-2018  | 2017-05-15
8/2017-2018  | 2017-06-16
9/2017-2018  | 2017-07-18
10/2017-2018 | 2017-08-20

 update sales
set salesId = case
                when month(salesDate)>=4 then concat(SUBSTRING_INDEX(salesId,'/',1),concat("/",year(salesDate),"-",year(salesDate)+1))
                else
                    concat(SUBSTRING_INDEX(salesId,'/',1),concat("/",year(salesDate)-1,"-",year(salesDate)))
                end
where cast(SUBSTRING_INDEX(salesId,'/',1) as unsigned) between "7" and "10";

✓

SELECT *  from sales

salesId      | salesDate 
:----------- | :---------
7/2017-2018  | 2017-05-15
8/2017-2018  | 2017-06-16
9/2017-2018  | 2017-07-18
10/2017-2018 | 2017-08-20

db<>fiddle here

edited Apr 4, 2020 at 21:17

answered Apr 4, 2020 at 18:15

nbk

49.6k8 gold badges32 silver badges53 bronze badges

8 Comments

pranay Over a year ago

yes the field salesId is already a varchar. I tired your above snippet but i still get the error: Error Code: 1292. Truncated incorrect INTEGER value: '1/17-18'

nbk Over a year ago

please show me your create table or make a fiddle to demonstrate your problem, as far as i can read your error message it shows your salesId is integer not Varchar in the right size

pranay Over a year ago

the salesID is varchar(100) however the salesDate is of type date

pranay Over a year ago

however this also gives correct results dbfiddle.uk/… but not in my db

nbk Over a year ago

your error code shows 1/17-18 which would not be affected in your update query, try it with your actual table on sql fiddle and see if you can reproduce it, you could also see if your mysql version can be updated, sometimes it is simpky buggy

|

Collectives™ on Stack Overflow

using string values for between operator in mysql

2 Answers 2

Comments

8 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

8 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related