Time and Date Format in SQL

SQL Date and Time Data Types

MySQL comes with the following data types for storing a date or a date/time value in the database:

DATE - YYYY-MM-DD
DATETIME - YYYY-MM-DD HH:MI
TIMESTAMP - YYYY-MM-DD HH:MI
YEAR - YYYY or YY

Convert DateTime to Date Format YYYY-MM-DD

While working with database tables, we need only the date part from the DateTime column. Let us first create an Employee Table in MySQL.

Output

Now, as we can see, the DOB column has DateTime format, we can convert this DateTime to YYYY-MM-DD which is a date datatype in SQL using DATE_FORMAT.

DATE_FORMAT(date, format) - Formats the date value according to the format string. The % character is required before format specifier characters.

Now, let’s query both the original DOB column values and DOB_new values to demonstrate the difference between them:

Output

Convert String to Date Format YYYY-MM-DD

STR_TO_DATE(str,format) - This is the inverse of the DATE_FORMAT() function. This STR_TO_DATE() function takes a string str and a format string format. STR_TO_DATE() returns a DATETIME value if the format string contains both date and time parts, or a DATE or TIME value if the string contains only date or time parts. If the date, time, or datetime value extracted from str is illegal, STR_TO_DATE() returns NULL and produces a warning.

Output

The server scans the str parameter in an attempt to match the format. The format string can contain literal characters and format specifiers beginning with %. The Literal character string is a sequence of zero or more characters enclosed by single quotes, such that the format must match literally in str. Format specifiers in the format must match a date or time part in str.

Convert Date Format YYYY-MM-DD into Other Formats

Similar to how we can convert other DateTime formats to YYYY-MM-DD, we can do the reverse. To convert the YYYY-MM-DD date format to different date formats, you can use the CONVERT function in MySQL.

The CONVERT() function converts a value into the specified datatype or character set.

Syntax:

Parameter Values:

• value: the required value to convert
• type: the required datatype to convert to which can be DATE, TIME, DATETIME, etc.

Output

In the above snippet, the first query converts the date format in SQL from YYYY-MM-DD to DATETIME format which gives YYYY-MM-DD HH:MI:SS as the output. While the second SQL query converts the YYYY-MM-DD HH:MI:SS to TIME format and gives HH:MI:SS as the output.

Date and Time Functions

In the following section, we will learn about some of the most important built-in date functions in SQL.

1. ADDDATE()

The ADDDATE() function adds a time/date interval to a date and then returns the date datatype in SQL.

Syntax

Parameter Values -

• date: the date to be modified
• days: the number of days to add to date
• value: the value of the time/date interval to add. Both positive and negative values are allowed.
• addunit: the type of interval to add.

Output

The last query in the above snippet is a bit different than the other two as ADDDATE(expression, days) is a shorthand way of modifying the date by just specifying the number of days as a parameter in the ADDDATE() function, this special form differentiates ADDDATE() from DATE_ADD(). In fact, when using this syntax, the ADDDATE() function is a synonym for DATE_ADD().

2. ADDTIME()

The ADDTIME() function in SQL adds a time interval to a time/datetime and then returns the time/datetime.

Syntax

Parameter Values

• datetime: the time/datetime to be modified
• addtime: the time interval to add to datetime. Both positive and negative values are allowed

3. DATE_ADD()

The DATE_ADD() function adds a time/date interval to a date and then returns the date datatype in SQL.

Syntax

Parameter Values

• date: the date to be modified
• value: the value of the time/date interval to add. Both positive and negative values are allowed.
• addunit: the type of interval to add.

Output

The first two queries are exactly the same as the ADDDATE() syntax. In fact, when using this syntax, the ADDDATE() function in SQL is a synonym for DATE_ADD(). The last query in the above snippet shows an error because DATE_ADD() function doesn’t support the shorthand syntax.

4. FROM_DAYS()

The FROM_DAYS() function returns a date datatype in SQL from a numeric datevalue. The FROM_DAYS() function is to be used only with dates within the Gregorian calendar.

The Gregorian calendar was introduced in October 1582 as a modification of, and replacement for, the Julian calendar. The Julian calendar day Thursday, 4 October 1582 was followed by the first day of the Gregorian calendar, Friday, 15 October 1582 (the cycle of weekdays was not affected).

The main difference between Julian and Gregorian calendars is that an average year in Julian calendar is 365.25 days while an average year in Gregorian calendar is 365.2425 days.

Syntax

Parameter Values

• number the numeric day to convert to a date

Output

5. SEC_TO_TIME()

The SEC_TO_TIME() function returns a time value (in format HH:MM:SS) based on the specified seconds.

Syntax

Parameter Values

• seconds the number of seconds. Both positive and negative values are allowed

Output

6. CURRENT_TIMEZONE()

This function returns the name of the time zone observed by a server. For SQL, the return value is based on the time zone of the instance itself assigned during instance creation, not the time zone of the underlying operating system.

Syntax

Argument This function takes no arguments.

Return Value CURRENT_TIMEZONE is a non-deterministic function. Views and expressions that reference this column cannot be indexed.