CODEWITHSUNDEEP
mysqlsql
nyc3_gy
nyc3_gy

Reputation: 23

Invoice year format YYYY to YY

I'm not able to change second year format (REPPL/20/2021/00004) ==> (REPPL/20/21/00004)

DATE_FORMAT(@dt, '%y'),(@dt+1)

when i add DATE_FORMAT(@dt, '%y'),(@dt+1,'%y') its throwing error

PLEASE HELP THANKS

INSERT INTO sales_invoice(
invoice_no,
date_invoiced,
order_num
) SELECT
CONCAT_WS('/','REPPL',DATE_FORMAT(@dt, '%y'),(@dt +1),
    COALESCE(
        LPAD(
            CASE WHEN @dt > DATE_FORMAT(@dt, '%Y-04-01') THEN SUM(date_invoiced > 
 DATE_FORMAT(@dt,'%Y-04-01')) ELSE SUM(date_invoiced BETWEEN DATE_FORMAT(@dt, '%Y-04-01') - INTERVAL 
 1 YEAR AND DATE_FORMAT(@dt, '%Y-04-01'))END +1,5,0),LPAD(1, 5, 0))),@dt,"132132"FROM sales_invoice;

Upvotes: 0

Views: 96

Answers (1)

ikiK
ikiK

Reputation: 6530

Well you are just passing @dt +1 You are not formatting it at all. You need to add DATE_FORMAT for each argument in CONCAT, not just first, first will format first one only, the first ()...

Try this, add one year interval and then format:

DATE_FORMAT(DATE_ADD(@dt, INTERVAL 1 YEAR), '%y')

Example:

CONCAT_WS('/','REPPL',DATE_FORMAT(@dt, '%y'),DATE_FORMAT(DATE_ADD(@dt, INTERVAL 1 YEAR), '%y')),

FIDDLE:

**Schema (MySQL v5.7)**
    
---

**Query #1**

    select CONCAT_WS('/','REPPL',DATE_FORMAT('2020/08/16', '%y'),DATE_FORMAT(DATE_ADD('2020/08/16', INTERVAL 1 YEAR), '%y')) AS ConcatenatedString;

| ConcatenatedString |
| ------------------ |
| REPPL/20/21        |

---

View on DB Fiddle

Upvotes: 1

Related Questions