CODEWITHSUNDEEP
mysqlsqlstringdatetimedate-arithmetic
Rya
Rya

Reputation: 329

convert quarter-year to month-end date in SQL

I have the time column, the value is 1Q1993 (QuarterYear),and I would like to get month-end-date of the quarter (which is 1993-03-31). Please could someone help with the SQL. I need to add to a select statement.

Upvotes: 0

Views: 1157

Answers (4)

ysth
ysth

Reputation: 98398

Another approach:

date(concat(substring_index(qtr,'Q',-1),'-01-01')) + interval substring_index(qtr,'Q',1)*3 month - interval 1 day

https://dbfiddle.uk/?rdbms=mysql_5.5&fiddle=a0a4a86900d9d6537418602ae6153a66

Upvotes: 1

GMB
GMB

Reputation: 222632

The simplest approach might be a case expression:

select 
    concat(
        right(mycol, 4),
        '-'
        case left(mycol, 1)
            when '1' then '03-31'
            when '2' then '06-30'
            when '3' then '09-30'
            when '4' then '12-30'
        end
    ) as mydate
from mytable

This rebuilds a string in format YYYY-MM-DD, which MySQL will happily understand as a date.

Upvotes: 1

nbk
nbk

Reputation: 49393

You can use this. As the dates are fixed and don't move

SET @date := "1Q1993"

SELECT CONCAT(SUBSTRING_INDEX(@date,'Q',-1),
CASE SUBSTRING_INDEX(@date,'Q',1) 
       WHEN 1 THEN '-03-31'
       WHEN 2 THEN '-06-30'
       WHEN 3 THEN '-09-30'
       ELSE  '-12-31' END)

| CONCAT(SUBSTRING_INDEX(@date,'Q',-1),
CASE SUBSTRING_INDEX(@date,'Q',1) 
      WHEN 1 THEN '-03-31'
      WHEN 2 THEN '-06-30'
      WHEN 3 THEN '-09-30'
      ELSE  '-12-31' END) |
| >:--------------------------------------------------------------------------------------->---------------------------------------------------------------------------------------->------- |
| 1993-03-31                                                                                                                                                                              |

db<>fiddle here

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270733

You can use extract the year, convert to a date and then add some months and subtract one day:

select (date(concat(right(col, 4), '-01-01') +
        interval (left(col, 1) * 3 + 1) month -
        interval 1 day 
       )

Upvotes: 0

Related Questions