Database administration entails retrieving useful information at the appropriate moment. SQL, the database management language, includes some useful tools for this purpose. It lets you to collect data over specific time periods such as days, weeks, months, and years. In this post, we'll look at simple ways to use SQL to retrieve the data you need and enhance your SQL skills.
What are dates and times in SQL?
Before we begin, let's learn a little about dates and timings in SQL. There are several unique utilities (such as 'DATEPART', 'DATEADD', and 'DATEDIFF') that make working with time simpler. Getting comfortable with these tools will allow you to use SQL more successfully. DATEPART, DATEADD, and DATEDIFF are SQL functions that deal with date and time information. Here is a brief explanation for each.
1. DATEPART() Function
The DATEPART() function is used to extract a specific part (such as year, month, day, hour, minute, etc.) from a given date or time value.
Syntax
DATEPART(datepart, date) or DATEPART(datepart, expression)
Example
DATEPART(YEAR, '2024-01-24') returns 2024, extracting the year from the given date.
2. DATEADD() Function
The DATEADD function() is used to add or subtract a specified time interval (such as days, months, years, etc.) to a given date or time value.
Syntax
DATEADD(datepart, number, date) or DATEADD(datepart, number, expression)
Example
DATEADD(MONTH, 3, '2024-01-24')` adds 3 months to the given date, resulting in '2024-04-24'
3. DATEDIFF() Function
The DATEDIFF function() calculates the difference between two date or time values, returning the result in terms of a specified time unit (such as days, months, years, etc.).
Syntax
DATEDIFF(datepart, startdate, enddate) or DATEDIFF(datepart, startexpression, endexpression)
Example
DATEDIFF(DAY, '2024-01-01', '2024-01-24')`
It returns the number of days between the two dates.
How can we fetch data according to our specific requirements?
Let's write the query to understand how we can fetch data according to our specific requirements, be it on a DateTime basis
First, you need to create a table in SQL and insert some data on this so write this query to create and insert the data in a Registration table with column name Id, Name, Email, CreatedDate.
CREATE TABLE Registration (
Id INT PRIMARY KEY,
Name VARCHAR(255),
Email VARCHAR(255),
CreatedDate DATETIME NOT NULL
);
To insert the data in a Registration table, write this query.
INSERT INTO Registration (Id, Name, Email, CreatedDate)
VALUES
(1, 'John Doe', '[email protected]', '2024-12-24 10:00:00'),
(2, 'Jane Smith', '[email protected]', '2023-01-24 10:15:00'),
(3, 'Alice Johnson', '[email protected]', '2023-12-20 10:30:00'),
(4, 'Bob Brown', '[email protected]', '2024-01-24 10:45:00'),
(5, 'Eva Davis', '[email protected]', '2024-11-10 11:00:00'),
(6, 'Charlie Brown', '[email protected]', '2023-05-24 12:00:00'),
(7, 'David Miller', '[email protected]', '2023-01-24 12:15:00'),
(8, 'Fiona Johnson', '[email protected]', '2024-12-24 12:30:00'),
(9, 'George Taylor', '[email protected]', '2023-01-24 12:45:00'),
(10, 'Helen Carter', '[email protected]', '2024-01-12 13:00:00'),
(11, 'Ian Williams', '[email protected]', '2022-05-24 13:15:00'),
(12, 'Jack Davis', '[email protected]', '2024-01-24 13:30:00'),
(13, 'Karen White', '[email protected]', '2024-01-24 13:45:00'),
(14, 'Liam Robinson', '[email protected]', '2023-01-24 14:00:00'),
(15, 'Mia Harris', '[email protected]', '2024-06-24 14:15:00'),
(16, 'Noah Martin', '[email protected]', '2023-12-24 14:30:00'),
(17, 'Olivia Jackson', '[email protected]', '2023-10-24 14:45:00'),
(18, 'Peter Thomas', '[email protected]', '2024-01-24 15:00:00'),
(19, 'Quinn Clark', '[email protected]', '2024-08-10 15:15:00'),
(20, 'Ryan Lee', '[email protected]', '2023-12-10 15:30:00');
Instead of manually specifying the date, you can use the GETDATE() function to retrieve the current datetime. You can write like this.
INSERT INTO Registration (id, name, email, createddate)
VALUES
(1, 'John Doe', '[email protected]', GETDATE()),
(2, 'Jane Smith', '[email protected]', GETDATE()),
--you can insert all 20 values like this using GETDATE()function--
Fetching Daywise Data
To retrieve data daily, we can utilize the `WHERE` clause with appropriate date conditions. Learn how to structure queries to fetch data for a specific day or a range of days. Examples will demonstrate how to filter records based on exact dates or using relative date conditions.
DECLARE @Date DATETIME = '2024-01-24 13:43:35.007';
SELECT * FROM Registration WHERE CAST(CreatedDate AS DATE) = CAST(@Date AS DATE);
Retrieving Weekly Data
Understanding how to retrieve data weekly. In this query, I am fetching data from the previous week, covering the period from last Monday to Sunday.
DECLARE @Date DATETIME = '2024-01-24 13:43:35.007';
SELECT * FROM Registration
WHERE CAST(CreatedDate AS DATE) BETWEEN DATEADD(DAY, -7, DATEADD(WEEK, DATEDIFF(WEEK, 0, @Date), 0)) AND DATEADD(DAY, 6, DATEADD(DAY, -7, DATEADD(WEEK, DATEDIFF(WEEK, 0, @Date), 0)))
Fetching Monthly Data
To retrieve data monthly, this query fetches data from the previous month, covering the period from the 1st day of the last month to the 30th or 31st day.
DECLARE @Date DATETIME = '2024-01-18 13:43:35.007';
SELECT * FROM Registration
WHERE CAST(CreatedDate AS DATE) BETWEEN DATEADD(MONTH, DATEDIFF(MONTH, 0, @Date) - 1, 0) AND DATEADD(DAY, -1, DATEADD(MONTH, DATEDIFF(MONTH, 0, @Date), 0))
Retrieving Yearly Data
To retrieve data yearly, this query fetches data from the previous year, covering the period from the first month and 1st day of the last year to the last month and 30th or 31st day of the year.
DECLARE @Date DATETIME = '2024-01-22 13:43:35.007';
SELECT * FROM Registration
WHERE CAST(CreatedDate AS DATE) BETWEEN DATEFROMPARTS(YEAR(@Date) - 1, 1, 1) AND DATEFROMPARTS(YEAR(@Date), 1, 1)
Conclusion
In This article, we learned about SQL's date and time functions open up powerful capabilities for fetching data with precision and efficiency. By understanding how to manipulate dates and times, you can tailor queries to extract relevant information for specific periods, enhancing your database management skills significantly.
FAQ's
Q 1. What is the purpose of the DATEPART() function in SQL?
Ans. The DATEPART() function is used to extract specific components, such as year, month, day, hour, minute, etc., from a given date or time value in SQL.
Q 2. How does the DATEADD() function work in SQL?
Ans. The DATEADD() function adds or subtracts a specified time interval (days, months, years, etc.) to a given date or time value, allowing for easy manipulation of dates.
Q 3. What does the DATEDIFF() function do in SQL?
Ans. The DATEDIFF() function calculates the difference between two date or time values and returns the result in terms of a specified time unit (days, months, years, etc.).
Q 4. How can I retrieve data for a specific day using SQL?
Ans. To fetch data for a specific day, you can use the WHERE clause with conditions comparing the date portion of the datetime column to the desired date.
DECLARE @Date DATETIME = '2024-01-24 13:43:35.007';
SELECT * FROM Registration WHERE CAST(CreatedDate AS DATE) = CAST(@Date AS DATE);
Q 5. How can I retrieve weekly data in SQL?
Ans. You can retrieve weekly data by specifying a date range covering the desired week using the DATEADD() and DATEDIFF() functions within the WHERE clause.
DECLARE @Date DATETIME = '2024-01-24 13:43:35.007';
SELECT * FROM Registration
WHERE CAST(CreatedDate AS DATE) BETWEEN DATEADD(DAY, -7, DATEADD(WEEK, DATEDIFF(WEEK, 0, @Date), 0)) AND DATEADD(DAY, 6, DATEADD(DAY, -7, DATEADD(WEEK, DATEDIFF(WEEK, 0, @Date), 0)))
Q 6. How do I fetch monthly data in SQL?
Ans. Monthly data can be retrieved by setting a date range spanning the desired month using the DATEADD() and DATEDIFF() functions to calculate the start and end dates.
DECLARE @Date DATETIME = '2024-01-18 13:43:35.007';
SELECT * FROM Registration
WHERE CAST(CreatedDate AS DATE) BETWEEN DATEADD(MONTH, DATEDIFF(MONTH, 0, @Date) - 1, 0) AND DATEADD(DAY, -1, DATEADD(MONTH, DATEDIFF(MONTH, 0, @Date), 0))
HostForLIFE.eu SQL Server 2022 Hosting
HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes.
