Table of Contents
Date Time Functions
Date Time functions accept parameters of 4 types: DATE, TIME, DATETIME, STRING.
In the case of adding a date part to the parameter, 1 Jan. 1900 is added. In the case of adding a time part to the parameter, 00:00:00 is added.
If the parameteris a STRING, then the format of the string value must correspond to the current DateTime format of the queried database.
DatePart parameter
The DatePart parameter is a string. Allowed values are: ‘day’, ‘month’, ‘year’, ‘quarter’, ‘dayofyear’, ‘week’, ‘weekday’, ‘hour’, ‘minute’, ‘second’.
• Value ‘dayofyear’, ‘weekday’ is the same as ‘day’.
CURDATE() / CURRENT_DATE()
Returns the current date as string in the format according to the current DateTime format of the database.
SELECT CURDATE() => '02/27/1999'
CURTTIME() / CURRENT_TIME()
Returns the current time as a value in the format ‘HH:MM:SS:sss‘
SELECT CURTIME() => '10:40:23:478'
DATEPART()
DATEPART( Date, DatePart )
Returns the integer value that is part of a given Date as specified by the parameter DatePart. The parameter DatePart is described above.
SELECT CURDATE(), DATEPART(CURDATE(), 'day') => '02/27/1999', 27
DAY() / DAYOFMONTH()
DAY( Date ) DAYOFMONTH( Date )
Returns the day of the month for Date.
SELECT CURDATE(), DAY(CURDATE()), DAYOFMONTH( CURDATE() ) => '02/27/1999', 27, 27
DAYOFWEEK() / WEEKDAY()
DAYOFWEEK( Date )
WEEKDAY( Date )
Returns the weekday index for Date (1 = Sunday, … 7 Saturday).
SELECT DAYOFWEEK( CURDATE() ) => 5
DAYOFYEAR()
DAYOFYEAR( Date )
Returns the day of the year for Date. Range is 1 to 366.
SELECT DAYOFYEAR( CURDATE() ) => 34
DAYNAME()
DAYNAME( Date )
Returns the name of the weekday for Date.
SELECT DAYNAME( CURDATE() ) => 'Tuesday'
DATEADD()
DATEADD( Date, DatePart, Delta )
These functions perform date arithmetic. Date is a value specifing the starting date. DatePart is a value specifying the interval to be added or subtracted from the starting date. The parameter DatePart is described above.
SELECT DATEADD( CURDATE(), 'day', 5 ) => '14.05.2008 00:00:00:0000'
DATEDIFF()
DateDiff( StartDate, EndDate, DatePart )
Returns the difference in the DatePart of the dates StartDate and EndDate. The parameter DatePart is described above.
SELECT datediff( CURDATE(), DATEADD( CURDATE(), 'day', 5 ), 'day') => -5
DATENAME()
DateName( Date, DatePart )
Returns the name of the date part of the given Date. If the value does not have a string name, then an integer value is returned. The parameter DatePart is described above.
SELECT DATENAME( CURDATE(), 'month' ) => 'February'
DATEROUND()
DATEROUND( Date, DatePart )
Rounds the Date to the nearest following DatePart. The parameter DatePart is described above.
SELECT DATEROUND( CURDATE(), 'weekday') => '11.05.2008 00:00:00'
DATETRUNC()
DATETRUNC( Date, DatePart )
Truncates the date to the nearest leading DatePart. Returns DateTime type. The parameter DatePart is described above.
SET PROPERTY DateTimeFormat OF DATABASE TO 'kYMD'; SET PROPERTY DateSeparator OF DATABASE TO '-'; SELECT DATETRUNC( '1997-10-23 15:16:10', 'day' ) => '1997-10-01 00:00:00:000'
SELECT DATETRUNC('1997-10-21 19:31:59', 'year'); -- 1900-01-01 00:00:00:000 SELECT DATETRUNC('1997-10-21 19:31:59', 'month'); -- 1997-01-01 00:00:00:000 SELECT DATETRUNC('1997-10-21 19:31:59', 'day'); -- 1997-10-01 00:00:00:000 SELECT DATETRUNC('1997-10-21 19:31:59', 'hour'); -- 1997-10-21 00:00:00:000 SELECT DATETRUNC('1997-10-21 19:31:59', 'minute'); -- 1997-10-21 19:00:00:000 SELECT DATETRUNC('1997-10-21 19:31:59', 'second'); -- 1997-10-21 19:31:00:000
DATETRUNC2()
DATETRUNC2( Date, DatePart )
Truncates the date to the nearest DatePart. Returns DateTime type. The parameter DatePart is described above.
SET PROPERTY DateTimeFormat OF DATABASE TO 'kYMD'; SET PROPERTY DateSeparator OF DATABASE TO '-'; SELECT DATETRUNC2( '1997-10-23 15:16:10', 'day' ) => '1997-10-23 00:00:00:000'
SELECT DATETRUNC2('1997-10-21 19:31:59', 'year'); -- 1997-01-01 00:00:00:000 SELECT DATETRUNC2('1997-10-21 19:31:59', 'month'); -- 1997-10-01 00:00:00:000 SELECT DATETRUNC2('1997-10-21 19:31:59', 'day'); -- 1997-10-21 00:00:00:000 SELECT DATETRUNC2('1997-10-21 19:31:59', 'hour'); -- 1997-10-21 19:00:00:000 SELECT DATETRUNC2('1997-10-21 19:31:59', 'minute'); -- 1997-10-21 19:31:00:000 SELECT DATETRUNC2('1997-10-21 19:31:59', 'second'); -- 1997-10-21 19:31:59:000
GETDATE() / NOW()
GETDATE( [String | Date | Time | DateTime] )
Returns the current date and time if no param. passed. And return “date” part from passed param. otherwise.
SET PROPERY DateTimeFormat OF DATABASE TO 'kYMD'; SET PROPERTY DateSeparator OF DATABASE TO '-'; SELECT GETDATE(), NOW() => '2008-05-09 12:20:34:834', '2008-05-09 12:20:34:834'
SET PROPERY DateTimeFormat OF DATABASE TO 'kYMD'; SET PROPERTY DateSeparator OF DATABASE TO '-'; SELECT GETDATE( datetime'2009-06-01 10:20:00' ) => '2009-06-01'
GETTIME()
GETTIME( String | Date | Time | DateTime )
Return “time” part from passed param.
SET PROPERY DateTimeFormat OF DATABASE TO 'kYMD'; SET PROPERTY DateSeparator OF DATABASE TO '-'; SELECT GETTIME( datetime'2009-06-01 10:20:00' ) => '10:20:00'
FROM_DAYS()
FROM_DAYS( ullong )
Converts the number of days in the Date type.
SET PROPERTY DateTimeFormat OF DATABASE TO 'kYMD'; SET PROPERTY DateSeparator OF DATABASE TO '-'; SELECT FROM_DAYS(729669) => '1997-10-07'
FROM_UNIX_TIMESTAMP()
NEW for v. 5.0
FROM_UNIX_TIMESTAMP( Integer )
Returns DateTime from the argument as seconds since 1970-01-01.
SET PROPERY DateTimeFormat OF DATABASE TO 'kYMD'; SET PROPERTY DateSeparator OF DATABASE TO '-'; SELECT FROM_UNIX_TIMESTAMP( 1196418619 ) => '2007-11-30 10:30:19'
FROM_UTC()
NEW for v. 5.0
FROM_UTC( String | Date | Time | DateTime )
Returns DateTime converted to the local time zone.
SET PROPERY DateTimeFormat OF DATABASE TO 'kYMD'; SET PROPERTY DateSeparator OF DATABASE TO '-'; -- Assume local time zone is GMT+2 SELECT FROM_UTC( '2007-11-30 10:30:19' ) => '2007-11-30 12:30:19'
HOUR()
HOUR( Time )
Returns the hour for a given Time in the range 0 to 23.
SELECT HOUR( CURRENT_TIME() ) =>16 SELECT HOUR( NOW() ) =>16
MINUTE()
MINUTE( Time )
Returns the minute for a given Time, in the range 0 to 59.
SELECT MINUTE( CURTIME() ) => 10 SELECT MINUTE( NOW() ) => 10
Month()
Month( Date )
Returns the month for Date, in the range 1 to 12.
SELECT MONTH(NOW()) AS 'Month Number' => 2
MONTHNAME()
MONTHNAME( Date )
Returns the full name of the month for Date.
SET PROPERTY DateTimeFormat OF DATABASE TO 'kYMD'; SET PROPERTY DateSeparator OF DATABASE TO '-'; SELECT MONTHNAME( '2004-02-24' ); => 'February'
QUARTER()
QUARTER( Date )
Returns the quarter of the year for Date, in the range 1 to 4.
SELECT QUARTER( NOW() ) => 3
SECOND()
SECOND( Time )
Returns the second for Time, in the range 1 to 59.
SELECT SECOND('16:10:20') => 20 SET PROPERTY DateTimeFormat OF DATABASE TO 'kYMD'; SET PROPERTY DateSeparator OF DATABASE TO '-'; SELECT SECOND('1998-02-03 16:10:20'); => 20
SEC_TO_TIME()
SEC_TO_TIME( Seconds )
Returns the Seconds argument converted to the Time format ‘HH:MM:SS‘.
SELECT SEC_TO_TIME( 2378 ) => '00:39:38:000'
TIME_TO_SEC()
TIME_TO_SEC( Time )
Returns the number of seconds in the Time.
SELECT TIME_TO_SEC('22:23:11') => 80591
TO_DAYS()
TO_DAYS( Date )
For a given Date, returns a daynumber (the number of days since year 0).
SET PROPERTY DateTimeFormat OF DATABASE TO 'kYMD'; SET PROPERTY DateSeparator OF DATABASE TO '-'; SELECT TO_DAYS('1997-10-07') => 729669
TO_UTC()
NEW for v. 5.0
TO_UTC( String | Date | Time | DateTime )
Returns DateTime converted to the UTC time zone.
SET PROPERY DateTimeFormat OF DATABASE TO 'kYMD'; SET PROPERTY DateSeparator OF DATABASE TO '-'; -- Assume local time zone is GMT+2 SELECT TO_UTC( '2007-11-30 10:30:19' ) => '2007-11-30 08:30:19'
WEEK()
Week( Date, [FirstWeekDay=0], [MinDaysInFirstWeek= 1] )
Returns the week number for Date in the range 1 - 52 (53), depends on MinDaysInFirstWeek parameter.
Parameter FirstWeekDay can be in range 0-6. Where 0 - Sunday, 1 - Monday, …
Parameter MinDaysInFirstWeek should be in the range 1 to 7.
SET PROPERTY DateTimeFormat OF DATABASE TO 'kYMD'; SET PROPERTY DateSeparator OF DATABASE TO '-'; SELECT WEEK('1997-12-03') => 49
YEAR()
YEAR( Date )
Returns the year for Date.
SELECT YEAR( NOW() ) => 2008
UNIX_TIMESTAMP()
NEW for v. 5.0
UNIX_TIMESTAMP( String | Date | Time | DateTime )
If called with an argument, it returns the value of the argument as seconds since '1970-01-01 00:00:00'.
Note: If you use UNIX_TIMESTAMP() and FROM_UNIX_TIMESTAMP() to convert between TIMESTAMP values and Unix timestamp values, the conversion is lossy because the mapping is not one-to-one in both directions.
SET PROPERY DateTimeFormat OF DATABASE TO 'kYMD'; SET PROPERTY DateSeparator OF DATABASE TO '-'; SELECT UNIX_TIMESTAMP( '2007-11-30 10:30:19' ) => 1196418619
SELECT UNIX_TIMESTAMP( now() ) =>1340233821 // !! RESULT will be different each TIME