A while back I blogged about how to round dates in SQL Server 2000+.  Here's the MySQL port:

DROP FUNCTION IF EXISTS `dbname`.`fn_RoundDate` $$
CREATE FUNCTION `dbname`.`fn_RoundDate` (dateArg DATETIME, formatArg CHAR) RETURNS DATETIME
DETERMINISTIC
BEGIN

 IF dateArg IS NULL OR formatArg IS NULL THEN
    RETURN NULL;
  ELSEIF formatArg = 'T' THEN -- Time
    RETURN CAST(dateArg AS TIME);
  ELSE
    -- Round to day
    SET dateArg = CAST(dateArg AS DATE);

   RETURN CASE formatArg
    WHEN 'D' THEN -- Day
     CAST(dateArg AS DATE)
    WHEN 'W' THEN -- Week
        ADDDATE(dateArg, INTERVAL 1 - DAYOFWEEK(dateArg) DAY)
    WHEN 'M' THEN -- Month
        ADDDATE(dateArg, INTERVAL 1 - DAYOFMONTH(dateArg) DAY)
      WHEN 'Q' THEN -- Quarter
        ADDDATE(MAKEDATE(YEAR(dateArg), 1), INTERVAL FLOOR(MONTH(dateArg) / 4) QUARTER)
    WHEN 'Y' THEN -- Month
        ADDDATE(dateArg, INTERVAL 1 - DAYOFYEAR(dateArg) DAY)
    ELSE -- Unknown format
     dateArg
    END;
  END IF;

END $$

DELIMITER ;

Why there isn't a built-in function to do something so useful--in either SQL Server or MySQL--is beyond me.

SQL Server's native support for date formatting and rounding is pathetic.  SQL Server stores dates as 2 4-byte integers:  one for the date and one for the time, and yet there's no way to extract the date or time portion--despite the datatype being set up in such a way to explicitly optimize for this operation!

Sick of reinventing the wheel, I finally broke down and wrote my own function to round dates to a time, day, week, month, quarter, or year:

With output as follows:

• Time = 1900-01-01 02:42:00.000
• Day = 2007-11-05 00:00:00.000
• Week = 2007-11-05 00:00:00.000
• Month = 2007-11-01 00:00:00.000
• Quarter = 2007-10-01 00:00:00.000
• Year = 2007-01-01 00:00:00.000