sql query problem

romeiovasu
romeiovasu used Ask the Experts™
on
how to calculate number of days left in a month using sql query

i have written a function to calculate number of days in a month

select db.functionname(month(getdate()), year(getdate())

now i want to calculate how many days left in a month how can i do that

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Billing Engineer
Most Valuable Expert 2014
Top Expert 2009
Commented:
in short, for the current month:
select datepart(day, dateadd(day,-1,dateadd(month, 1, dateadd(day, 1-datepart(day, getdate()), getdate()))) ) - datepart(day, getdate()) + 1

or
select datediff(day, getdate(), dateadd(month, 1, dateadd(day, 1-datepart(day, getdate()), getdate()))) )

Most Valuable Expert 2015

Commented:
declare @Today datetime
declare @LastOfMonth datetime
set @Today = cast(convert(varchar(10), getdate(), 121) as datetime)
set @LastOfMonth  = cast(convert(varchar(8), dateadd(m, 1, @Today), 121) +'01' as datetime) - 1
select datediff(d, @Today, @LastOfMonth) AS DaysRemaining
Sean McIlvennaSr. Software Architect

Commented:
In your function:

Declare @begin DateTime, @end DateTime
select @begin = convert( varchar( 2 ), @month ) + '/1/' + convert( varchar( 4 ), @year )
select @end = dateadd( mm, 1, @begin )

return DateDiff( dd, @begin, @end )
Sean McIlvennaSr. Software Architect

Commented:
WOW! turn my head and there are two other responses.... DAMN!
Most Valuable Expert 2015

Commented:
Ditto!  And once again angelII wins the fastest typer contest ;-) Darn

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