Date and time functions for T-SQL - SQL Server to Aurora MySQL Migration Playbook

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

Three star feature compatibility

Four star automation level

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.