Previous Months Begin and End Dates? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Previous Months Begin and End Dates?

Author

Topic

lloydsj
Starting Member

7 Posts

Posted - 2003-06-19 : 10:46:21

I am trying to filter a table on the previous months dates...

So, for example if I ran the report today (19/6/03), I would want it to return all dates for May.

eg. SELECT * FROM mytable WHERE mydate BETWEEN 1/6/03 AND 31/6/03

However I want to create these dates using SQL. How do I do this?

Cheers.

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2003-06-19 : 10:59:31


select
    dateadd(month,datediff(month,0,getdate()),0) as 'firstdayofcurrentmonth',
    dateadd(month,datediff(month,0,getdate())-1,0) as 'firstdayofpreviousmonth',
    dateadd(month,datediff(month,0,getdate()),0)-1 as 'lastdayofpreviousmonth'

Jay White
{0}

macka
Posting Yak Master

162 Posts

Posted - 2003-06-19 : 11:00:37

SELECT *
FROM myTable
WHERE DATEPART(M,mydate) = DATEPART(M,DATEADD(M,-1,getdate()))
AND DATEPART(YYYY,mydate) = DATEPART(YYYY,DATEADD(M,-1,getdate()))

macka.

--
There are only 10 types of people in the world - Those who understand binary, and those who don't.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-06-19 : 11:07:54

Nice one, Jay ... I hadn't thought of that one. very easy technique.

Macka -- the problem with WHERE clauses that like is that no indexes can be used.

if you want to return all data in the last 30 days, don't use this:

WHERE DateDiff(d,TransDate, getdate()) < 30

use

WHERE TransDate >= DateAdd(d,-30, getdate())

because the second can make use of an index if there is one on the transdate field.

- Jeff

macka
Posting Yak Master

162 Posts

Posted - 2003-06-19 : 11:14:46

Good point well made - guess thats what I get for posting with a hangover.

--
There are only 10 types of people in the world - Those who understand binary, and those who don't.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources