Re: Oracle Select Statement to MYSQL
Hi
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
CURDATE( )
returns the current date in the form '2003-12-31'
CURTIME( )
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
DATE_FORMAT(d, form)
formats d according to formatting string f ; see below
DATE_SUB( . . . )
subtracts a time interval from the start time; see below
DAYNAME(date)
returns 'Monday', 'Tuesday', etc.
DAYOFMONTH(date)
returns the day of the month (1–31)
DAYOFWEEK(date)
returns the day of the week (1 = Sunday through 7 = Saturday)
DAYOFYEAR(date)
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 2003
FROM_DAYS(n)
returns the date n days after the year 0
FROM_DAYS(3660)
returns '0010-01-08'
FROM_UNIXTIME(t)
transforms the Unix timestamp number t into a date
FROM_UNIXTIME(0)
returns '1970-01-01 01:00:00'
FROM_UNIXTIME(t, f)
as above, but with formatting as in DATE_FORMAT
HOUR(time)
returns the hour (0–23)
MINUTE(time)
returns the minute (0–59)
MONTH(date)
returns the month (1–12)
MONTHNAME(date)
returns the name of the month ('January', etc.)
NOW( )
returns the current time in the form '2003-12-31 23:59:59'
QUARTER(date)
returns the quarter (1–4)
SECOND(time)
returns the second (0–59)
SEC_TO_TIME(n)
returns the time n seconds after midnight
SEC_TO_TIME(3603)
returns '01:00:03'
TIME_FORMAT(time, f)
like DATE_FORMAT, but for times only
TIME_TO_SEC(time)
returns the seconds since midnight
TO_DAYS(date)
returns the number of days since the year 0
UNIX_TIMESTAMP( )
returns the current time as a Unix timestamp number
UNIX_TIMESTAMP(d)
returns the timestamp number for the given date
WEEK(date)
returns week number (1 for the week beginning with the first Sunday in the year)
WEEK(date, day)
as above, but specifies with day the day on which the week should begin (0 = Sunday, 1 = Monday, etc.)
WEEKDAY(date)
returns the day of the week (0 = Monday, 1 = Tuesday, etc.)
YEAR(date)
returns the year
More Example:
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'