add 1 day to date format YYYYMMDD when field name is greater or equal to 1200

Question

I have 2 fields dateUS and HourMins (data type is varchar)

dateUS     | HourMins

20160928   | 0108

20160927  | 2144

20160902 |  0319

20160919  | 1215

output will be

run to mysql query on my database

Possible duplicate of [adding one day to a date](http://stackoverflow.com/questions/1394791/adding-one-day-to-a-date) — Epodax, Oct 05 '16 at 07:37
SELECT CASE WHEN HourMins >= 1200 THEN add (1 day) ELSE (remain) END as dateloc, — odong, Oct 05 '16 at 07:43

1000111 · Accepted Answer · 2016-10-05T08:01:59.657

With the help of STR_TO_DATE and DATE_FORMAT function you can achieve this:

SELECT 
 DATE_FORMAT(STR_TO_DATE(dateUs,'%Y%m%d') + INTERVAL HourMins+0 >= 1200 DAY ,'%Y%m%d') 
  AS dateLoc
FROM your_table

Demonstration:

SET @str := '20160919';


SET @HOUR := '1215';

SELECT
    (
        STR_TO_DATE(@str, '%Y%m%d') + INTERVAL (@HOUR + 0) >= 1200 DAY
    ) AS date,
    DATE_FORMAT(
        STR_TO_DATE(@str, '%Y%m%d') + INTERVAL (@HOUR + 0) >= 1200 DAY,
        '%Y%m%d'
    ) AS expectedFormat;

Output:

date         expectedFormat
2016-09-20     20160920

add 1 day to date format YYYYMMDD when field name is greater or equal to 1200

1 Answers1