Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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?

share|improve this question
    
You have more than one question here. Please modify the question for the first part, and post the second part as a separate question, if the answer below does not satisfy your query. – user3463 Aug 14 '12 at 1:09
up vote 2 down vote accepted

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

share|improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .