SQL SERVER & Date function

arof
arof used Ask the Experts™
on
I need a function that returns the last day on the previous month of given date . any idea?

example:

if date = 20070530, then, the last day of previous month is 20070430
if date = 20070630, then, the last day of previous month is 20070531

and so on..

returns: datetime
parameter: datetime


any idea?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Try this.  It will return the date with no time attached:

CREATE FUNCTION dbo.fnPreviousEOM (
    @TestDate DATETIME  -- must pass date in, GETDATE illegal in functions
    )
RETURNS VARCHAR(20)
AS
BEGIN
    Declare @dt datetime

    SET @dt = Convert(varchar(8), @TestDate, 112)

    RETURN @dt - DAY(@dt)
Most Valuable Expert 2015

Commented:

create function dbo.ReturnLastDayOfPrevMonth (@theDate varchar(10))
returns datetime
as
begin
      return dateadd(d, -1, convert(datetime, left(@theDate, 6) +'01', 112))
end
go

select dbo.ReturnLastDayOfPrevMonth('20070530')
Most Valuable Expert 2015
Commented:
bhess1's suggestion looks better

Author

Commented:
thanks all..

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial