Introduction
When working with MSSQL, you may need to retrieve data from a specific time period. One common task is to get data from the previous day. In this article, we will explore how to write a SQL query to retrieve the date of the previous day and use it to retrieve data.
Using GETDATE()
The first step in getting the previous day's date is to use the GETDATE() function to retrieve the current date and time. This function returns the current system date and time in the default format for the system.
SELECT GETDATE()
This will return a value similar to: "2022-01-11 12:25:59.983".
Using DATEADD()
Once we have the current date and time, we can use the DATEADD() function to subtract a day from the date and get the previous day's date. The syntax for DATEADD() is as follows:
DATEADD(interval, number, date)
The "interval" parameter specifies the type of interval to be added or subtracted from the "date" parameter. In our case, we will use the "day" interval to subtract one day from the date.
The "number" parameter specifies the number of intervals to add or subtract. In our case, we will use -1 to subtract one day from the date.
Here's an example of using DATEADD() to get the previous day's date:
SELECT DATEADD(day, -1, GETDATE())
This will return a value similar to: "2022-01-10 12:25:59.983".
Formatting the Date
Now that we have the date of the previous day, we can use it to retrieve data from that day. However, the date returned by DATEADD() is in a datetime format, which may not be useful for all applications. To format the date, we can use the CONVERT() function.
The syntax for CONVERT() is as follows:
CONVERT(data_type, expression, style)
We will use the style "112" to convert the date to the format "YYYYMMDD", which is the most commonly used date format in SQL.
Here's an example of using CONVERT() to format the previous day's date:
SELECT CONVERT(varchar(8), DATEADD(day, -1, GETDATE()), 112)
This will return a value of "20220110", which is the date of the previous day in the format "YYYYMMDD".
Using the Date in a Query
Now that we have the previous day's date in the correct format, we can use it in a query to retrieve data from that day. Here is an example of a query that retrieves data from a table called "my_table" where the date column is equal to the previous day's date:
SELECT * FROM my_table WHERE date_column = CONVERT(varchar(8), DATEADD(day, -1, GETDATE()), 112)
This query will return all rows from "my_table" where the "date_column" is equal to the previous day's date.
Conclusion
Retrieving the previous day's date in MSSQL is a common task that can be accomplished using the GETDATE() function and the DATEADD() function. By using these functions, you can easily retrieve data from a specific time period in your tables. Remember that when using the date in a query, it's important to format it correctly using the CONVERT() function.