Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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

1 Answer 1

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

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.