Re: Oracle Select Statement to MYSQL
None of the "To_" functions will work in Mysql.
please go throught the Mysql functions in the "MySQL 5.0 Reference Manual" in the Mysql site or If you installed the Mysql then you can find this in your local dirve.(MySQL 5.0 Reference Manual.chm)
For example if you want to conver to number then you can use CAST in Mysql.
for to_date you can use DATE_FORMAT in MYsql.
I am pasting all the DATETIME functions in Mysql.
Using Dates and Times
returns the current date in the form '2003-12-31'
returns the current time either as a string or number, depending on context; e.g., '23:59:59' or 235959 (integer)
DATE_ADD( . . . )
adds a time interval to a starting time; see below
formats d according to formatting string f ; see below
DATE_SUB( . . . )
subtracts a time interval from the start time; see below
returns 'Monday', 'Tuesday', etc.
returns the day of the month (1–31)
returns the day of the week (1 = Sunday through 7 = Saturday)
returns the day in the year (1–366)
EXTRACT(i FROM date)
returns a number for the desired interval
EXTRACT(YEAR FROM '2003- 12-31')
returns the date n days after the year 0
transforms the Unix timestamp number t into a date
returns '1970-01-01 01:00:00'
as above, but with formatting as in DATE_FORMAT
returns the hour (0–23)
returns the minute (0–59)
returns the month (1–12)
returns the name of the month ('January', etc.)
returns the current time in the form '2003-12-31 23:59:59'
returns the quarter (1–4)
returns the second (0–59)
returns the time n seconds after midnight
like DATE_FORMAT, but for times only
returns the seconds since midnight
returns the number of days since the year 0
returns the current time as a Unix timestamp number
returns the timestamp number for the given date
returns week number (1 for the week beginning with the first Sunday in the year)
as above, but specifies with day the day on which the week should begin (0 = Sunday, 1 = Monday, etc.)
returns the day of the week (0 = Monday, 1 = Tuesday, etc.)
returns the year
Calculating with Dates
DATE_ADD(date, INTERVAL n i) adds n times the interval i to the starting date date. Our first example shows how intelligently the function deals with ends of months (31.12 or 28.2):
DATE_ADD('2003-12-31', INTERVAL 2 month) returns '2004-02-28' DATE_ADD('2003-12-31', INTERVAL '3:30' HOUR_MINUTE) returns '2003-12-31 03:30:00'
Formatting Dates and Times
DATE_FORMAT(date, format) helps in representing dates and times in other formats than the usual MySQL format. Two examples illustrate the syntax:
DATE_FORMAT('2003-12-31', '%M %d %Y') returns 'December 31 2003' DATE_FORMAT('2003-12-31', '%D of %M') returns '31st of December'