first day of last month condition

Question

 

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)

       

Answer 1

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

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

---

Steen Schlüter Persson (DK)

Answer 2

try below.

select dateadd(month,datediff(month,0,getdate())-1,0)

---

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

Answer 3

Hello,

Try this(i hope i understood your question correctly) 

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

Regards
satheesh

Answer 4

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

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

---

Steen Schlüter Persson (DK)

cool, how do i use it in the where?

Answer 5

Please try this

DATEADD(mm, DATEDIFF(M,0,GETDATE())-1,0)

to your condition this will work

Answer 6

Please try this

DATEADD(mm, DATEDIFF(M,0,GETDATE())-1,0)

to your condition this will work

sorry, gives me wrong data

Answer 7

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

Answer 8

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: 

Completed >= DATEADD(mm, DATEDIFF(M,0,GETDATE())-1,1)

Answer 9

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:)

Answer 10

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?

Answer 11

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

Answer 12

I wish to have it in a function

Answer 13

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

Answer 14

ah thanks!