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 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.
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.
CURDATE
and SYSDATE()
functionsThe CURDATE
function returns the current date. Run the query below to confirm. The SYSDATE
function returns both the current date and system time.
DATE
functionThe 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.
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.
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.
TIME
, CURTIME
, and TIME_FORMAT
functionsThe 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.
TIME_TO_SEC
and TIME_DIFF
functionsThe 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.
HOUR
, MINUTE
, and SECOND
functionsThe 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