Question about date - 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

Question about date

Author

Topic

norty911
Starting Member

41 Posts

Posted - 2007-05-29 : 05:37:17

Hey guys, how would you go about determining the number of working days left within a given timeframe (e.g. month)? Say for instance we took today's date: From Tuesday the 29th there are 2 more working days till the end of the month. How could you do that in SQL?

Thanks for any help

pbguy
Constraint Violating Yak Guru

319 Posts

Posted - 2007-05-29 : 06:05:22

select 'Days Left' = datediff(day, getdate(), dateadd(day, -1, dateadd(month, datediff(month, 0, getdate()) + 1, 0)))

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-05-29 : 06:11:28

[code]
declare @today datetime,
@end_of_mth datetime

select @today = getdate(),
@end_of_mth = dateadd(month, datediff(month, 0, getdate()) + 1, -1)

select working_days = count(*)
from F_TABLE_DATE(@today, @end_of_mth)
where WEEKDAY_NAME not in ('Sun', 'Sat')
[/code]

F_TABLE_DATE is from http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=61519&SearchTerms=F_TABLE_DATE

norty911
Starting Member

41 Posts

Posted - 2007-05-29 : 06:31:05

perfect! thanks to the both of you

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-05-30 : 00:11:45

quote:
Originally posted by norty911

perfect! thanks to the both of you

Did you get same result on both replies?

Madhivanan

Failing to plan is Planning to fail

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-05-30 : 00:14:29

quote:
Originally posted by pbguy

select 'Days Left' = datediff(day, getdate(), dateadd(day, -1, dateadd(month, datediff(month, 0, getdate()) + 1, 0)))

This can be simplified to

select 'Days Left' = datediff(day, getdate(), dateadd(month,datediff(month,-1,getdate()),-1))

Madhivanan

Failing to plan is Planning to fail

VBScab
Starting Member

5 Posts

Posted - 2007-05-30 : 06:51:37

I have a similar problem, in that I need to tack on this kind of 'query' onto an existing query.

The scenario...

I'm building an ASP-driven reservation system and want to display a series of rows from the current month to 14 months from the current month. The ASP, I can do.

As a start (I can probably figure out the rest from your answer(s)), I want to query for all bookings in which the start date is in the current month. I found a query that will tell me the first day of the current month:

select dateadd(dd, -day(getdate())+1,getdate()), 'dd/mm/yyyy'

and I have my booking query:

SELECT * FROM T_Booking WHERE StartDate = ....

In my efforts to cobble these two together, what I've tried (but get syntax errors for) is various combinations of:

SELECT * FROM T_Booking WHERE StartDate
BETWEEN (select dateadd(dd, -day(getdate())+1,getdate()), 'dd/mm/yyyy') AND [another date which will be in an ASP variable]

What would be the correct syntax to use?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-05-30 : 06:54:16

quote:
Originally posted by madhivanan

quote:
Originally posted by norty911

perfect! thanks to the both of you

Did you get same result on both replies?

Madhivanan

Failing to plan is Planning to fail

Yes i wonder also

norty911 wanted working days left.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-05-30 : 06:56:46

the first day of the current month :

select dateadd(month, datediff(month, 0, getdate()), 0)

SELECT * 
FROM   T_Booking 
WHERE  StartDate >= dateadd(month, datediff(month, 0, getdate()), 0)
and    StartDate <= @another_date

VBScab
Starting Member

5 Posts

Posted - 2007-05-30 : 07:20:00

Bingo! Ditching the second 'SELECT' did it.

Using the suggested syntax for the current month:

dateadd(month, datediff(month, 0, getdate()), 0)

meant that the records in the current month were missed. Altering it to use my original query for 'first day of the month' got the data I wanted:

SELECT *
FROM T_Booking
WHERE StartDate >= DATEADD("d", -DAY(GetDate())+1,GetDate())
AND EndDate <= DATEADD("M",intNbrOfMonthsToList,GetDate())

Thanks!

Fantastic response times, BTW. I'll come here in future, instead of waiting four days for Tek-Tips simply to remind me of my password...

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-05-30 : 11:32:00

More on dates
http://www.sql-server-performance.com/fk_datetime.asp

Madhivanan

Failing to plan is Planning to fail

Jeff Moden
Aged Yak Warrior

652 Posts

Posted - 2007-05-31 : 21:13:58

The "DateDiff" solution is gonna mess you up because it just counts the number of days... not the number of "WORKING" days as you asked for...

--Jeff Moden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources