Getting last month records

[mevasquez]

I am having troubled trying to figure out how to get the records of a table. As an example, I want to get all the records for the month of February if the current date is March 14, 2007. I am not too sure on how to use the datepart, dateadd, convert, getdate(). I have tried several ways, one of which was,

  select column1, column2
  from table1
  where column1 >= dateadd(mm, -1, getdate())
  and column1 < getdate()

This just returned every record from one month ago from today's date. I need to get the records from 2/1/07 to 2/28/07

TIA

Mike

 

[SQLDenis]

This works on SQL Server

Run this

[COLOR=blue]SELECT[/color] [COLOR=#FF00FF]DATEADD[/color](mm, [COLOR=#FF00FF]DATEDIFF[/color](mm, 0, [COLOR=#FF00FF]GETDATE[/color]())-1, 0) ,
 [COLOR=#FF00FF]DATEADD[/color](mm, [COLOR=#FF00FF]DATEDIFF[/color](mm, 0, [COLOR=#FF00FF]GETDATE[/color]())+0, 0)

does this give you
2007-02-01 00:00:00.000, 2007-03-01 00:00:00.000

is so then your query become this

[COLOR=blue]select[/color] column1, column2
  [COLOR=blue]from[/color] table1
  [COLOR=blue]where[/color] column1 >= [COLOR=#FF00FF]DATEADD[/color](mm, [COLOR=#FF00FF]DATEDIFF[/color](mm, 0, [COLOR=#FF00FF]GETDATE[/color]())-1, 0)
  and column1  < [COLOR=#FF00FF]DATEADD[/color](mm, [COLOR=#FF00FF]DATEDIFF[/color](mm, 0, [COLOR=#FF00FF]GETDATE[/color]())+0, 0)

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
Google Interview Questions

 

[gmmastros]

Try this...

  select column1, column2
  from table1
  where column1 >= DateAdd(Month, -1 + DateDiff(Month, 0, GetDate()), 0)
  and column1 < DateAdd(Month, DateDiff(Month, 0, GetDate()), 0)

The trick here lies within all that DateAdd/DateDiff mumbo jumbo. So, allow me to explain how this works.

Let's start here:
DateDiff(Month, 0, GetDate())

Date diff will tell you the difference in dates based on the given units. So, this line is actually telling you how many months has elapsed since the 0 date (this is Jan 1, 1900) and Today's date. The other important point to realize is that DateDiff returns an integer.

If you run this: DateDiff(Month, 0, GetDate()) you will get 1286. This represents that number of months that have elapsed since Jan 1, 1900. Now, by adding that number of months to Jan 1, 1900, you will get March 1, 2007.

I hope the query works for you, and I hope you understand the logic. If not, post back and I will try to explain it better.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[mevasquez]

I found an easier way. Don't know why it took me so long. Here is what I did since column1 is a datetime field and formated as 2/2/07, I just asked for all the data that the month is a 2 (Feb).

SELECT column1, column2
FROM table1
WHERE datepart(month,column1) = datepart(month, getdate())-1
AND depart(year, column1) = 2007

This gave me everything is Febrary 2007. I will have to change the sql statement in 2008.

I tried the sample above; however, the front end to the database did not like the "0" in the datediff and dateadd functions. The database that the front end app connects to is Sybase. I even tried to put in 1/1/1900 and it did not like that either. Thanks for the lesson. I understand how you arrived to your end result.

Thanks for everyone's help.

Mike

 

[gmmastros]

If you are satisfied with that query (from a performance perspective), then I suggest you also try this...

[COLOR=blue]SELECT[/color] column1, 
       column2
[COLOR=blue]FROM[/color]   table1
[COLOR=blue]WHERE[/color]  [COLOR=#FF00FF]datepart[/color]([COLOR=#FF00FF]month[/color],column1) = [COLOR=#FF00FF]datepart[/color]([COLOR=#FF00FF]month[/color], [COLOR=#FF00FF]getdate[/color]())-1
       AND [COLOR=#FF00FF]datepart[/color]([COLOR=#FF00FF]year[/color], column1) = [COLOR=#FF00FF]DatePart[/color]([COLOR=#FF00FF]year[/color], [COLOR=#FF00FF]DateAdd[/color]([COLOR=#FF00FF]Month[/color], -1, [COLOR=#FF00FF]GetDate[/color]()))

Notice the last part of the where clause. Essentially, we subtract 1 month from GetDate() and then use the year part to compare with. So, if the month is march, subtract 1 month, you get February and the year is the same. Now consider January 2008. Subtract 1 month and you get Dec 2007. Then you get the year to compare against.

Hope this helps.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[mevasquez]

Great that worked.

Thanks,

 

[SQLDenis]

Don't do this

WHERE datepart(month,column1) = datepart(month, getdate())-1
AND depart(year, column1) = 2007

it will cause an index scan instead of a seek

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
Google Interview Questions