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’.

Returns the current date as string in the format according to the current DateTime format of the database.

SELECT CURDATE()
=> '02/27/1999'

Returns the current time as a value in the format ‘HH:MM:SS:sss‘

SELECT CURTIME()
=> '10:40:23:478'

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( Date ) DAYOFMONTH( Date )

Returns the day of the month for Date.

SELECT CURDATE(), DAY(CURDATE()), DAYOFMONTH( CURDATE() )
=> '02/27/1999', 27, 27

DAYOFWEEK( Date )
WEEKDAY( Date )

Returns the weekday index for Date (1 = Sunday, … 7 Saturday).

SELECT DAYOFWEEK( CURDATE() )	
=> 5

DAYOFYEAR( Date )

Returns the day of the year for Date. Range is 1 to 366.

SELECT DAYOFYEAR( CURDATE() )		
=> 34

DAYNAME( Date )

Returns the name of the weekday for Date.

SELECT DAYNAME( CURDATE() )
=> 'Tuesday'

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( 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( 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( 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( 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( 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( [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( 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( 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'

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'

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( Time )

Returns the hour for a given Time in the range 0 to 23.

SELECT HOUR( CURRENT_TIME() )	
=>16
SELECT HOUR( NOW() )		
=>16

MINUTE( Time )

Returns the minute for a given Time, in the range 0 to 59.

SELECT MINUTE( CURTIME() )	
=> 10
SELECT MINUTE( NOW() )	
=> 10

Month( Date )

Returns the month for Date, in the range 1 to 12.

SELECT MONTH(NOW()) AS 'Month Number'
=> 2

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( Date )

Returns the quarter of the year for Date, in the range 1 to 4.

SELECT QUARTER( NOW() )	
=> 3

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( 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 )

Returns the number of seconds in the Time.

SELECT TIME_TO_SEC('22:23:11')	
=> 80591

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

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( 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( Date )

Returns the year for Date.

SELECT YEAR( NOW() )
=> 2008

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