I have an SQL View which contains a column with dates of the '2012-06-21 19:44:50.543' format. I need to entre the condition to show only lines with dates starting from the beginning of the previous month. This procedure requires to use the current (inbuilt) time. Also I need such a View to be updated automatically each month. How can I do that?
1 Answer
To get dates starting from the previous month based on current system time, you will use DATEADD()
and GETDATE()
.
DATEADD
returns a specified date with the specified number interval (signed integer) added to a specified datepart of that date.
DATEADD (datepart , number , date )
GETDATE
returns the current database system timestamp as a datetime value without the database time zone offset. This value is derived from the operating system of the computer on which the instance of SQL Server is running.
GETDATE ( )
To calculate the dates that match from the start of last month (remember, this is an example and may need modification with your code):
SELECT [col1], [col2], [dt] FROM [table]
WHERE [dt] >= convert(varchar,DATEADD(MONTH,-1,
DATEADD(MONTH,DATEDIFF(MONTH,0,
getdate()),0)),101)
Please do some reading about DATEDIFF()
as well, and CONVERT()
to understand how the formatting represented by 101
works. You may prefer 121
instead.
(Additional source: Terry Apodaca’s article, “SQL Dates – First, Current, Last Day of Month”, which moved from this old address to http://v1.terryapodaca.com/2008/01/28/sql-dates-first-current-last-day-of-month (or you can read an archived copy at the Wayback Machine).)