first day of last month condition

# first day of last month condition

• Wednesday, May 30, 2012 11:03 AM

Hi, how can I adjust this so that on the first day of the month only, it checks that Completed is after the first day of last month ( currently, it checks that Completed is after the first day of this month)

WHERE   Completed >= DATEADD(mm, DATEDIFF(m, 0, GETDATE()), 0)

### All Replies

• Wednesday, May 30, 2012 11:14 AM

You can try the below (if I get your question right)

```IF (SELECT DAY(GETDATE())) = 1
BEGIN
END
ELSE
BEGIN
SELECT DATEADD(mm, DATEDIFF(m, 0, GETDATE()), 0)
END```

• Wednesday, May 30, 2012 11:18 AM

try below.

ESHANI. Please click "Mark As Answer" if a post solves your problem or "Vote As Helpful" if a post has been useful to you

• Wednesday, May 30, 2012 11:58 AM

Hello,

Try this(i hope i understood your question correctly)

`WHERE   Completed >= DATEADD(mm, DATEDIFF(m, 0, GETDATE())-1,0)`
Regards
satheesh

• Wednesday, May 30, 2012 4:35 PM

You can try the below (if I get your question right)

```IF (SELECT DAY(GETDATE())) = 1
BEGIN
END
ELSE
BEGIN
SELECT DATEADD(mm, DATEDIFF(m, 0, GETDATE()), 0)
END```

cool, how do i use it in the where?

• Wednesday, May 30, 2012 4:55 PM

to your condition this will work

• Wednesday, May 30, 2012 5:02 PM

to your condition this will work

sorry, gives me wrong data
• Wednesday, May 30, 2012 5:04 PM
Moderator

declare @TestDate datetime

```set @TestDate = case when datepart(day, CURRENT_TIMESTAMP) = 1 then dateadd(month, -1 + datediff(month, '19000101', CURRENT_TIMESTAMP), '19000101')
else dateadd(month, datediff(month, '19000101', CURRENT_TIMESTAMP), '19000101') end
select * ... where Completed >= @TestDate```

For every expert, there is an equal and opposite expert. - Becker's Law

My blog

• Saturday, June 02, 2012 7:00 PM

Hello,

I think the condition you are expecting is anything greater than firstday of the last month.

If that is the case than try the below condition:

• Saturday, June 02, 2012 7:50 PM

declare @TestDate datetime

```set @TestDate = case when datepart(day, CURRENT_TIMESTAMP) = 1 then dateadd(month, -1 + datediff(month, '19000101', CURRENT_TIMESTAMP), '19000101')
else dateadd(month, datediff(month, '19000101', CURRENT_TIMESTAMP), '19000101') end
select * ... where Completed >= @TestDate```

For every expert, there is an equal and opposite expert. - Becker's Law

My blog

muchos gratias:)
• Thursday, July 19, 2012 10:16 AM

declare @TestDate datetime

```set @TestDate = case when datepart(day, CURRENT_TIMESTAMP) = 1 then dateadd(month, -1 + datediff(month, '19000101', CURRENT_TIMESTAMP), '19000101')
else dateadd(month, datediff(month, '19000101', CURRENT_TIMESTAMP), '19000101') end
select * ... where Completed >= @TestDate```

For every expert, there is an equal and opposite expert. - Becker's Law

My blog

I need a multi valued table function for that right?
• Thursday, July 19, 2012 10:23 AM

that depends on your requirement. This is just a logic to find the End of month date. Can you elaborate on your requirement?

Regards
satheesh

• Thursday, July 19, 2012 10:48 AM

I wish to have it in a function
• Thursday, July 19, 2012 1:24 PM
Moderator

You can make it a scalar function if you'd like.

For every expert, there is an equal and opposite expert. - Becker's Law

My blog

• Thursday, July 19, 2012 2:25 PM

ah thanks!