Monday, June 15, 2009

Oracle to_date and mysql STR_TO_DATE

Hi All,
If we exported tables from Oracle in insert query format then query statements contains to_date(DATE, PATTERN) for date values.
If we want to execute these query statements in MySql then this statement will not work.
There is fixed date format in MySql and that is 'yyyy-mm-dd'.
To store data from given format to MySql format STR_TO_DATE(DATE, PATTERN) can be used.
Oracle to_date is equivalent to STR_TO_DATE in mysql

STR_TO_DATE(str,format)
Ex:
In Oracle table date in dd-MMM-yy format
to_date('10-JAN-06','DD-MON-RR')
Equivalent in MySql is
STR_TO_DATE('10-JAN-06','%d-%M-%y')
will store date in yyyy-mm-dd format that is 2006-01-10

No comments:

Post a Comment