The date and time functions in SQL

Structured query language (SQL) is used to access and manipulate relational databases. We can create, update, delete, and retrieve data from databases. With SQL, we can store various data types, one of which is the DATETIME data type. Several SQL functions can be applied to this data type as we’ll see in the coming sections.

For this Answer, we’ll play around with the datetime values in the following table:

Educative_Employees table
Educative_Employees table

Date functions

Date can be stored in an SQL database with different formats. In most cases, the YYYY-MM-DD is used. Whatever format we use, we just have to ensure that the format of the date during insertion matches the one the table can store.

Now, we’ll review some functions to fetch and manipulate the date.

Categories of the date and time functions

The following table shows the categories of the date and time functions in SQL:

Date and Time Category

Function Name

Current Date and Time


SYSDATETIME

SYSDATETIMEOFFSET

SYSUTDATETIME

CURRENT_TIMESTAMP

GETDATE

GETUTCDATE

CURDATE

SYSDATE

CURTIME

Validate

ISDATE

Parts of Date and Time

DATENAME

DATEPART

DAY

MONTH

YEAR

TIME

WEEKDAY

YEARWEEK

HOUR

MINUTE

SECOND

Difference and Modification

DATEDIFF

DATEADD

EOMONTH

SWITCHOFFSET

TODATETIMEOFFSET

Construct a Date

DATEFROMPARTS

DATETIME2FROMPARTS

DATETIMEFROMPARTS

DATETIMEOFFSETFROMPARTS

SMALLDATETIMEFROMPARTS

TIMEFROMPARTS

Fetched Stored Data from Table

DATE

TIME

Return Total Number of Days

TO_DAYS

Time Conversion

TIME_TO_SEC

Change Time Format

TIME_FORMAT

The DATENAME and DATEPART functions can return several parts of date and time, such as quarter, month, day of year, day, week, weekday, hour, minute, second, millisecond, microsecond, nanosecond, TZoffset, and ISO_WEEK.

The CURDATE and SYSDATE() functions

The CURDATE function returns the current date. Run the query below to confirm. The SYSDATE function returns both the current date and system time.

The DATE function

The DATE function takes a date or datetime field as an argument and returns the date. Before we test this function, run the following query to see how the table has been populated:

Now, press the “Run” button. Notice how only the date is returned.

The TO_DAYS, WEEKDAY, and YEARWEEK functions

The TO_DAYS function returns the number of days from year zero to the specified date, passed as argument. On the other hand, the WEEKDAY function returns the weekday number of a date. Each day of the week is numbered chronologically from zero to six, starting on Monday and ending on Sunday. The YEARWEEK function returns the concatenation of a year and the week number. The year is extracted from the date passed as an argument while the week number is calculated from zero to 53—there are a total of 53 weeks in a year—depending upon the number of the week that date lies in within the year.

Time functions

Like date, time can also be stored in different formats. Normally, it is stored as HH-MM-SS, but we can change the format to our liking.

The TIME, CURTIME, and TIME_FORMAT functions

The TIME function extracts the time from a time or datetime expression. The CURTIME function returns the present time based on the 24-hour system by default. To change the date format, we use the TIME_FORMAT method. We pass it two arguments: the time and the format we desire by giving a format specifier. The %r format specifier changes the time to a 12-hour system time value.

The TIME_TO_SEC and TIME_DIFF functions

The TIME_TO_SEC function takes a time as an argument, specified as HH:MM:SS, and returns the total number of seconds passed. In contrast, the TIME_DIFF function returns the difference between two time or two datetime expressions. For a datetime expression, it will first extract the time and then calculate the difference between them.

The HOUR, MINUTE, and SECOND functions

The HOUR function returns the hour, the MINUTE function returns the number of minutes, and the SECOND function fetches the seconds from a time or datetime expression.

SELECT employee_name, HOUR(joining_date_time) as Hours_of_Joining FROM Educative_Employees;
SELECT employee_name, MINUTE(joining_date_time) as Minutes_of_Joining FROM Educative_Employees;
SELECT employee_name, SECOND(joining_date_time) as Seconds_of_Joining FROM Educative_Employees;

Free Resources

Copyright ©2024 Educative, Inc. All rights reserved