Sunday, March 11, 2012

DateTime function question

Greetings all,

Is there a function that exists that returns the last day of each month?

I need to allow payments to a job number if the import date is before the cancel dates following month end.

So I can't dateadd or datediff as far as I can see with any accuracy.

Any suggestions?

Adamus

how about this Adamus

Code Snippet

CREATE function [dbo].[Last_Day_Of_Month](@.monthIn varchar(2), @.yearIn char(4))

returns datetime

as

begin

DECLARE @.wrkMonth char(2),

@.wrkDay char(2),

@.wrkDate DATETIME

select @.wrkMonth = right('00' + @.monthIn, 2)

if isdate(convert(char(02), @.wrkMonth) + '-01-' + convert(char(04), @.yearIn))=0

begin

return null

end

SET @.wrkDate = @.yearIn + '/' + @.monthIn + '/01'

return DATEADD(DD, -1, DATEADD(M, 1, @.wrkDate))

end

|||

Another way is something like:

Code Snippet

select dateadd(day, -1, dateadd(mm, 1, dateadd(mm, datediff (mm, 0, getdate()), 0)))


2007-07-31 00:00:00.000

You're welcome Adam.

|||

Both work beautifully.

Thanks guys...I just couldn't wrap my head around it this morning.

Adamus

No comments:

Post a Comment