How to create a condition clause using a current computer time

Question

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?

Answer 1

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).)