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:
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.
SELECT CURDATE();SELECT SYSDATE();
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:
SELECT * FROM Educative_Employees;
Now, press the “Run” button. Notice how only the date is returned.
SELECT DATE(joining_date_time) as Date_of_Joining FROM Educative_Employees;
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.
SELECT TO_DAYS("2014-06-11");SELECT WEEKDAY("1969-11-06");SELECT YEARWEEK(CURDATE());
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.
SELECT TIME(joining_date_time) as Time_of_Joining FROM Educative_Employees;SELECT CURTIME();SELECT TIME_FORMAT("23:15:3", "%r");
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.
SELECT TIME_TO_SEC("69:3:3");SELECT TIMEDIFF("2023-12-13 2:45:13","2023-06-13 1:59:18");
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
