Date and time functions for T-SQL

This topic provides reference information about date and time functions in Microsoft SQL Server and Amazon Aurora MySQL, which is valuable for database administrators and developers migrating from SQL Server to Aurora MySQL. You can understand the similarities and differences in how these two database systems handle temporal operations, including system date and time values, time zone considerations, and specific function equivalents.

Feature compatibility	AWS SCT / AWS DMS automation level	AWS SCT action code index	Key differences
		Date and Time Functions	Time zone handling. Syntax differences.

SQL Server Usage

Date and time functions are scalar functions that perform operations on temporal or numeric input and return temporal or numeric values.

System date and time values are derived from the operating system of the server where SQL Server is running.

Note

This section doesn’t address time zone considerations and time zone aware functions. For more information, see Data Types.

Syntax and Examples

The following table lists the most commonly used date and time functions.

Function	Purpose	Example	Result	Comments
GETDATE and GETUTCDATE	Return a datetime value that contains the current local or UTC date and time.	SELECT GETDATE()	2018-04-05 15:53:01.380
DATEPART, DAY, MONTH, and YEAR	Return an integer value representing the specified date part of a specified date.	SELECT MONTH(GETDATE()), YEAR(GETDATE())	4, 2018
DATEDIFF	Returns an integer value of date part boundaries that are crossed between two dates.	SELECT DATEDIFF(DAY, GETDATE(), EOMONTH(GETDATE()))	25	How many days are left until the end of the month.
DATEADD	Returns a datetime value that is calculated with an offset interval to the specified date part of a date.	SELECT DATEADD(DAY, 25, GETDATE())	2018-04-30 15:55:52.147
CAST and CONVERT	Converts datetime values to and from string literals and to and from other datetime formats.	SELECT CAST(GETDATE() AS DATE) SELECT CONVERT(VARCHAR(20), GETDATE(), 112)	2018-04-05 20180405	Default date format. Style 112 (ISO) with no separators.

For more information, see Date and Time functions in the SQL Server documentation.

MySQL Usage

Amazon Aurora MySQL-Compatible Edition (Aurora MySQL) provides a very rich set of scalar date and time functions; more than SQL Server.

Note

While some of the functions such as DATEDIFF seem to be similar to those in SQL Server, the functionality can be significantly different. Take extra care when migrating temporal logic to Aurora MySQL paradigms.

Syntax and Examples

Function	Purpose	Example	Result	Comments
NOW, LOCALTIME, CURRENT_TIMESTAMP, and SYSDATE	Returns a datetime value that contains the current local date and time.	SELECT NOW()	2018-04-06 18:57:54	SYSDATE returns the time at which it runs, compared to NOW, which returns a constant time when the statement started running. Also, SET TIMESTAMP doesn’t affect SYSDATE.
UTC_TIMESTAMP	Returns a datetime value that contains the current UTC date and time.	SELECT UTC_TIMESTAMP()	2018-04-07 04:57:54
SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, and YEAR	Returns an integer value representing the specified date part of a specified date function.	SELECT MONTH(NOW()), YEAR(NOW())	4, 2018
DATEDIFF	Returns an integer value of the difference in days between two dates.	SELECT DATEDIFF(NOW(),'2018-05-01')	-25	DATEDIFF in Aurora MySQL is only for calculating difference in days. Use TIMESTAMPDIFF instead.
TIMESTAMPDIFF	Returns an integer value of the difference in date part between two dates.	SELECT TIMESTAMPDIFF(DAY, NOW(),'2018-05-01')	24
DATE_ADD, DATE_SUB	Returns a datetime value that is calculated with an offset interval to the specified date part of a date.	SELECT DATE_ADD(NOW(),INTERVAL 1 DAY);	2018-04-07 19:35:32
CAST and CONVERT	Converts datetime values to and from string literals and to and from other datetime formats.	SELECT CAST(GETDATE() AS DATE) SELECT CONVERT(VARCHAR(20), GETDATE(), 112)	2018-04-05 20180405	Default date format. Style 112 (ISO) with no separators.

Migration Considerations

The date and time handling paradigm in Aurora MySQL differs from SQL Server.

Be aware of the differences in data types, time zone awareness, and locale handling. For more information, see Data Types.

Summary

The following table identifies similarities, differences, and key migration considerations.

SQL Server function	Aurora MySQL function	Comments
GETDATE, CURRENT_TIMESTAMP	NOW, LOCALTIME, CURRENT_TIMESTAMP, and SYSDATE	CURRENT_TIMESTAMP is the ANSI standard and it is compatible. SYSDATE returns the time at which it runs, unlike NOW which returns a constant time when the statement started running. Also, SET TIMESTAMP doesn’t affect SYSDATE.
GETUTCDATE	UTC_TIMESTAMP
DAY, MONTH, and YEAR	DAY, MONTH, YEAR	Compatible syntax.
DATEPART	EXTRACT, or one of: MICROSECOND, SECOND, MINUTE, HOUR, DAY, DAYNAME, DAYOFWEEK, DAYOFYEAR, WEEK, MONTH, MONTHNAME, QUARTER, YEAR	Aurora MySQL supports EXTRACT as a generic DATEPART function. For example, EXTRACT (YEAR FROM NOW()). It also supports individual functions for each day part.
DATEDIFF	TIMESTAMPDIFF	DATEDIFF in Aurora MySQL only calculates differences in days.
DATEADD	DATE_ADD, DATE_SUB, TIMESTAMPADD	DATEADD in Aurora MySQL only adds full days to a datetime value. Aurora MySQL also supports DATE_SUB for subtracting date parts from a date time expression. The argument order and syntax is also different and requires a rewrite.
CAST and CONVERT	DATE_FORMAT, TIME_FORMAT	Although Aurora MySQL supports both CAST and CONVERT, they aren’t used for style conversion as in SQL Server. Use DATE_FORMAT and TIME_FORMAT.

For more information, see Date and Time Functions in the MySQL documentation.