calculating the num of days - SQL Server Forums

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

calculating the num of days

Author

Topic

marcusstar10
Starting Member

14 Posts

Posted - 2011-03-11 : 09:37:45

if someone gets paid at the beginning of the month how would you, calculate the number of days till they get paid for this month and next month.

i guess id have to use sysdate along with the adddate and maybe the month_between funtion.

Can someonee help me to produce this syntax

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-03-11 : 10:51:36

You can do something along these lines:

dateadd(mm,datediff(mm,0,getdate())0,0) -- gives you beginning of this month
dateadd(mm,datediff(mm,0,getdate())+1,0) -- gives you beginning of next month
dateadd(mm,datediff(mm,0,getdate())+2,0) -- gives you beginning of the month after that

Then you can use datediff function like this:

datediff( dd,  getdate(), dateadd(mm,datediff(mm,0,getdate())+1,0) )

That gets you days till beginning of next month.

ajthepoolman
Constraint Violating Yak Guru

384 Posts

Posted - 2011-03-11 : 10:54:17

There are probably a number of ways to do it. Here is one example:

DECLARE @dtiStartDate datetime
DECLARE @Month varchar(2)
DECLARE @Year varchar(4)
DECLARE @FirstOfMonth datetime

SET @dtiStartDate = '3/11/2008'

SET @Month = DATEPART(M, @dtiStartDate)
SET @Year = DATEPART(YYYY, @dtiStartDate)

SET @FirstOfMonth = @Month + '/01/' + @Year

select DATEDIFF(D, @FirstOfMonth, DATEADD(M, 1, @FirstOfMonth) )

Hey, it compiles.

marcusstar10
Starting Member

14 Posts

Posted - 2011-03-14 : 08:58:11

I found the solution, thanks

select last_day(sysdate)-sysdate as days_left from dual;

lionofdezert
Aged Yak Warrior

885 Posts

Posted - 2011-03-14 : 10:59:21

Everyone here thought you are asking for TSQL. But you are probably using ORACLE.

--------------------------
http://connectsql.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources