TRUNC (date) Function Conversion from Oracle to MySQL

The TRUNC (date) function returns date with the time portion of the day truncated to the unit specified by the format model fmt.

MySQL does not support TRUNC function. Therefore, need to create an additional object, namely a function that simulates the operation of the TRUNC function.

Let's create the following function and see how it works:

 CREATE FUNCTION SWF_TruncDate(pDate datetime, FmtStr varchar(255))
 RETURNS DATE
 DETERMINISTIC
 BEGIN
   IF (UPPER(FmtStr) = 'YEAR' or UPPER(FmtStr) = 'YY' or UPPER(FmtStr) = 'YYYY' or UPPER(FmtStr) = 'Y' or UPPER(FmtStr) = 'YYY') THEN
     RETURN makedate(year(pDate),1);
   ELSEIF (UPPER(FmtStr) = 'MONTH' or UPPER(FmtStr) = 'MM' or UPPER(FmtStr) = 'MON') THEN
     RETURN concat(makedate(year(pDate),dayofyear(pDate)-day(pDate)+1));
   ELSEIF (UPPER(FmtStr) = 'DD' or UPPER(FmtStr) = 'DDD') THEN
     RETURN date(pDate);
   ELSEIF (UPPER(FmtStr) = 'HOUR' or UPPER(FmtStr) = 'HH' or UPPER(FmtStr) = 'HH12' or UPPER(FmtStr) = 'HH24') THEN
     RETURN date(pDate);
   ELSEIF (UPPER(FmtStr) = 'MINUTE' or UPPER(FmtStr) = 'MI') THEN
     RETURN date(pDate);
   ELSEIF (UPPER(FmtStr) = 'Q') THEN
     RETURN MAKEDATE(YEAR(pDate), 1) + INTERVAL QUARTER(pDate) QUARTER - INTERVAL 1 QUARTER;
   ELSE
     RETURN date(pDate);
   END IF;
 END;

The function checks FmtStr parameter and if it is 'YEAR'/'YY'/'YYYY'/'Y'/'YYY', it will take the year specified in pDate parameter and concat it with '-01-01' (January 1). If FmtStr parameter is 'MONTH'/'MM'/'MON', the function will return the concatenation of the year, month from pDate parameter and '-01', that is, start of month. When FmtStr parameter is 'DD'/'DDD'/'D'/'DY'/'DAY', the function will return date truncated to the day. 'DD' is a default value when FmtStr parameter is not specified in source. If FmtStr parameter is 'HOUR'/'HH'/'HH12'/'HH24', the return value will be date truncated to the hour. And if FmtStr parameter is 'MINUTE'/'MI', the return value will be date truncated to the minute.

Let's look at an example of how such function would work:

Oracle	MySQL
select trunc(TO_DATE('25-AUG-22','DD-MON-YY')) from dual	select SWF_TruncDate(STR_TO_DATE('25-AUG-22','%d-%b-%y'),'DD');
select trunc(TO_DATE('25-AUG-22','DD-MON-YY'), 'YY') from dual;	select SWF_TruncDate(STR_TO_DATE('25-AUG-22','%d-%b-%y'),'YY');
select trunc(TO_DATE('25-AUG-22','DD-MON-YY'), 'MONTH') from dual;	select SWF_TruncDate(STR_TO_DATE('25-AUG-22','%d-%b-%y'),'MONTH');
select trunc(TO_DATE('25-AUG-22','DD-MON-YY'), 'DDD') from dual;	select SWF_TruncDate(STR_TO_DATE('25-AUG-22','%d-%b-%y'),'DDD');

As you can see the TRUNC function has been replaced by created SWF_TruncDate function. You can check and make sure that the queries are equivalent and return the same result set.

This solution is automatically executed by our software Ispirer Toolkit. You can learn more about other features of the conversion toolkit, as well as try out a demo license on our website.

If you have any questions or face any difficulties, please contact our support team: support@ispirer.com