Getting all data before the 1st day of current month

Question

Post reply

Getting all data before the 1st day of current month

Janice Wilcox

SSC Rookie

Points: 42
More actions
August 19, 2004 at 6:45 am

#164713

Hi,
I am trying to pull in all data from a startdate field but I only want to see the data up to the previous month. For an example today is 8/19/2004 I want to see all the records before 8/01/2004. I have tried several different things and nothing is quite working correctly. Thanks for any help you can give.
JW

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Aaron Myers Ten Centuries Points: 1139 More actions · Answer 1

Will this work?

WHERE MONTH(mydate) < MONTH(GETDATE())

Janice Wilcox SSC Rookie Points: 42 More actions · Answer 2

This gives me this years data <08/01/2004 but for the data in 2003 it doesn't give me any months past 07/31/2003 so I don't get 8/2003, 9/2003, 10/2003, 11/2003, 12/2003 data and the same for the year 2002.

Aaron Myers Ten Centuries Points: 1139 More actions · Answer 3

I guess that is true. What about this?

WHERE mydate < cast(cast(month(Getdate())as char) + '/01/' + cast(year(getdate())as char) AS SMALLDATETIME)

There might be a better way of doing this.

Janice Wilcox SSC Rookie Points: 42 More actions · Answer 4

Thank You, Thank You, Thank You......

That worked perfectly!!!!!!

Stewart Joslyn SSCertifiable Points: 6131 More actions · Answer 5

Neater, and probably faster, would be :

WHERE StartDate <= DATEADD(d, -DAY(GETDATE()), GETDATE())

Razvan Socol SSCarpal Tunnel Points: 4811 More actions · Answer 6

Another way (more clear, but probably slower), could be:

WHERE MONTH(MyDate)<MONTH(GetDate()) AND YEAR(MyDate)=YEAR(GetDate()) OR YEAR(MyDate)<YEAR(GetDate())

I think Stewart's solution is the best, but it works correctly only if there is no time portion in the column (indeed, usually, there is only the date portion).

Razvan

Stewart Joslyn SSCertifiable Points: 6131 More actions · Answer 7

Good point about time - careless of me. If this is a concern, you need :

WHERE StartDate < DATEADD(d, DATEDIFF(d, 0, DATEADD(d, 1 - DAY(GETDATE()), GETDATE())), 0)

This calculates the number of days from SQL Server's base date and then adds that number of days to the base date. Note that we are now looking for dates LESS THAN the 1st of the current month.

This construction is a general way of stripping the time from a datetime value.

Aaron Myers Ten Centuries Points: 1139 More actions · Answer 8

Thanks for the variations! I will have to remember the negative datediff usage.

-A

Razvan Socol SSCarpal Tunnel Points: 4811 More actions · Answer 9

Stewart, here is a better (easier) way:

WHERE StartDate < DATEADD(m, DATEDIFF(m, 0, GETDATE()), 0)

Razvan

Stewart Joslyn SSCertifiable Points: 6131 More actions · Answer 10

Stewart Joslyn

SSCertifiable

Points: 6131

August 20, 2004 at 9:18 am

#520009

Good thinking. You're quite right. Many thanks.

Janice Wilcox SSC Rookie Points: 42 More actions · Answer 11

Thank you all very much for all the code examples. I was struggling with this for a few weeks. Now I can have a relaxing weekend. Hopefully you will do the same.