15

For "2012-07-12", how can I get the start of the week, i.e., "2012-07-08", and start of the month, i.e., "2012-07-01"?

DrXCheng
  • 3,992
  • 11
  • 49
  • 72
  • how are you inserting the data? from a stored proc? from a program? are you wanting to insert the data "rounded" or "round" the data when you return it from the DB? – Borophyll Jul 12 '12 at 22:07
  • The data are already there with the full date. For some queries, I want to round the date. – DrXCheng Jul 12 '12 at 22:11
  • Duplicate (day of week): http://stackoverflow.com/questions/6944055/how-do-i-get-the-first-day-of-the-week-of-a-date-in-mysql – pilcrow Jul 12 '12 at 22:58
  • Duplicate (day of month): http://stackoverflow.com/questions/3298288/how-to-get-first-day-of-every-corresponding-month-in-mysql – pilcrow Jul 12 '12 at 23:00

5 Answers5

35

First day of the month:

SELECT DATE_FORMAT('2007-07-12', '%Y-%m-01');

output: 2007-07-01

First day of the week:

SELECT DATE_SUB('2007-07-12', INTERVAL DAYOFWEEK('2007-07-12')-1 DAY);

output: 2007-07-08

MySQL reference: http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_date-add

Borophyll
  • 1,099
  • 2
  • 15
  • 24
11

Same answer as Borophyll's, but I have changed the behavior of the first day of the month to return a date, not just a string which avoids date formatting/parsing mentioned in user151220's answer.

First day of the month:

SELECT DATE_SUB('2007-07-12', INTERVAL DAYOFMONTH('2007-07-12') - 1 DAY);

output: 2007-07-01

First day of the week:

SELECT DATE_SUB('2007-07-12', INTERVAL DAYOFWEEK('2007-07-12') - 1 DAY);

output: 2007-07-08

MySQL reference: http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_date-add

Community
  • 1
  • 1
myroch
  • 153
  • 1
  • 7
4

For those who need Monday as the first day of the week:

SELECT DATE_SUB('2007-07-12', INTERVAL WEEKDAY('2007-07-12') DAY);

output: 2007-07-09

This relies on the WEEKDAY function, which starts with Monday instead of DAYOFWEEK, which starts with Sunday.

Craig Waddington
  • 448
  • 3
  • 9
1

The DATE_FORMAT reply from Borophyll is very good, but gives a string rather than a date. So can't be compared easily.

If you need to use this as a comparison to a date field, use str_to_date to reverse it back to date rather than string.

select x from y where date >= str_to_date( DATE_FORMAT(now()-interval 12 month,'Y-%m-01'), '%Y-%m-%d')

If you are (say) looking at 12 months sales figures, but you want to always start off from the 1st of a month.

Tim Bray
  • 1,373
  • 10
  • 7
0

This will work if you want to just code it and forget about it, it will use datetime now and always return MTD results-

where date_completed between date_sub(date(now()), INTERVAL dayofmonth(now()) -1 day) and now()
Dane Thomas
  • 51
  • 2