1

Let's say that I have an input date of "2017-01-31" or "2017-02-28". I want to take this input date and make SQL change it to "2017-01-01" or "2017-02-01".

Is there a way to do this via MySQL functions in a query?

Zeusftw
  • 47
  • 8

2 Answers2

1

Several ways to do that. My preference is to use DATE_FORMAT to replace the day portion with constant 01.

 SELECT DATE_FORMAT('2017-01-31','%Y-%m-01') + INTERVAL 0 DAY AS dt

There are lots of ways to skin that cat.

For example, we could subtract the integer number of days minus 1 ...

 SELECT '2017-01-31' + INTERVAL 1-DAY('2017-01-31') DAY

(With the second form, the date value needs to be supplied twice. With the first, we only need to supply the value one time. I think the first form is easier for a future reader to understand... pretty clear what the author is intending.)

spencer7593
  • 106,611
  • 15
  • 112
  • 140
  • Thanks man, amazing. – Zeusftw Feb 10 '17 at 20:22
  • @Zeusftw: if you want the last day of the month, MySQL provides a `LAST_DAY()` function. Unfortunately, there is no `FIRST_DAY()` function. In the first query, the `+ INTERVAL 0 DAY` isn't strictly necessary. But it does cause the string returned from `DATE_FORMAT` to be converted back into a `DATE`, which is what we would expect to be returned from a function that got us the "first day" of the month. – spencer7593 Feb 10 '17 at 20:31
  • @Zeusftw , if you are happy with Spencer7593 's answer, please mark it as solved – Mojtaba Feb 10 '17 at 20:33
0

Use your date instead of my example:

SELECT CONCAT_WS('-',YEAR('2017-01-28'),MONTH('2017-01-28'),'01')
Mojtaba
  • 4,852
  • 5
  • 21
  • 38