Day and time are essential concepts in SQL. Let’s see below
Day:
In SQL, a day typically refers to a specific date or the day component of a datetime value. SQL provides various functions and operators to work with dates and extract information such as day, month, and year from datetime values.
Functions for Working with Days:
DATEPART(): This function extracts a specific part of a datetime value, such as the day of the month.
DAY(): This function returns the day component of a datetime value.
GETDATE(): This function retrieves the current date and time from the system.
Time:
In SQL, time represents a specific point in a day, typically in hours, minutes, and seconds. SQL also provides functions and operators to work with time components of datetime values.
Functions for Working with Time:
DATEPART(): This function can also be used to extract time components such as hour, minute, and second from a datetime value.
GETDATE(): This function retrieves the current date and time, including the time component.
CONVERT(): This function can be used to convert datetime values to different formats, including time-only formats.
Example 1: Retrieve the current date and time
SELECT GETDATE();
This query returns the current date and time from the system.
Example 2: Extract the day of the month from a datetime value
SELECT DAY(GETDATE());
This query returns the day component of the current date and time.
Example 3: Extract the hour of the day from a datetime value
SELECT DATEPART(HOUR, GETDATE());
This query extracts the hour component of the current date and time.
Example 4: Filter data for a specific day
SELECT *
FROM table_name
WHERE DATEPART(DAY, date_column) = 15;
This query retrieves rows from a table where the day component of the date_column is equal to 15.
Example 5: Filter data for a specific time range
SELECT *
FROM table_name
WHERE DATEPART(HOUR, datetime_column) BETWEEN 9 AND 17;
This query selects rows from a table where the hour component of the datetime_column falls between 9 AM and 5 PM.