filter last day of month based on sysdate

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

General SQL Server Forums

New to SQL Server Programming

filter last day of month based on sysdate

Author

Topic

phanicrn
Starting Member

42 Posts

Posted - 2008-01-25 : 09:51:31

hi

how do i write a filter to filter data only for last day of month.

for ex: we are running a report on jan 25/2008, but report should filter on last day of current month..

help me guys

thanks
phani

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-01-25 : 09:59:35

WHERE DateField <= DATEADD(d,-1*DATEPART(d,GETDATE()),DATEADD(m,1,GETDATE()))

phanicrn
Starting Member

42 Posts

Posted - 2008-01-25 : 10:15:32

i am gettting a parsing error.. when i tried

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2008-01-25 : 11:13:22

select dateadd(mm, datediff(mm, 0, '20080125') + 1, 0) - 1

Jay
to here knows when

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2008-01-25 : 11:44:52

[code]
select
*
from
MyTable
where
-- Date GE last day of current month
MyDate >= dateadd(mm,datediff(mm,-1, getdate()),-1) and
-- Date before first day of next month
MyDate < dateadd(mm,datediff(mm,0, getdate())+1,0)
[/code]

CODO ERGO SUM

phanicrn
Starting Member

42 Posts

Posted - 2008-01-25 : 12:54:54

first condition works and gets last day of month
Date GE last day of current month
MyDate >= dateadd(mm,datediff(mm,-1, getdate()),-1) and

but second condition doesnt work

-- Date before first day of next month
MyDate < dateadd(mm,datediff(mm,0, getdate())+1,0)

tmaiden
Yak Posting Veteran

86 Posts

Posted - 2008-01-25 : 13:28:26

I use this...


CREATE FUNCTION [dbo].[FirstDayOfMonth](@startDate DATETIME)
RETURNS DATETIME
AS
BEGIN
	DECLARE	@resultDate DATETIME

	SET @resultDate = CONVERT(VARCHAR(4), YEAR(@startDate)) + '-' +
					   CONVERT(VARCHAR(2), MONTH(@startDate)) + '-01'
	RETURN @resultDate
END

Then it would be...
WHERE DateField <= DATEADD(d,-1,DATEADD(mm,1,dbo.FirstDayOfMonth(GETDATE())))

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2008-01-25 : 13:34:57

quote:
Originally posted by phanicrn

first condition works and gets last day of month
Date GE last day of current month
MyDate >= dateadd(mm,datediff(mm,-1, getdate()),-1) and

but second condition doesnt work

-- Date before first day of next month
MyDate < dateadd(mm,datediff(mm,0, getdate())+1,0)

What do you mean "dosen't work"?

CODO ERGO SUM

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-01-25 : 13:35:07

quote:
Originally posted by phanicrn

but second condition doesnt work
-- Date before first day of next month
MyDate < dateadd(mm,datediff(mm,0, getdate())+1,0)

What is it about this condition that is not working?

Be One with the Optimizer
TG

phanicrn
Starting Member

42 Posts

Posted - 2008-01-25 : 13:40:12

sorry, it's my fault, it's working, thanks guys
appreciated...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources