Play with dates

• luckysql.kinda

  SSCertifiable

  Points: 7816

  More actions

  December 10, 2009 at 1:57 am

  #214661

  I have from date and to date. I want to find the number of days between these two dates removing the weekends. Can anyone help?

  Planning to write something like as follows:

  DECLARE @TotalDays INT

  DECLARE @FromDate VARCHAR(15)

  DECLARE @ToDate VARCHAR(15)

  SET @FromDate = '12/01/2009'

  SET @ToDate = '12/12/2009'

  IF(datediff(dd,@FromDate,@ToDate)<6)

  BEGIN

  IF(DAtepart(DW,@FromDate)=1)

  BEGIN

  PS: This is not complete code.

• Dave Ballantyne

  SSC-Dedicated

  Points: 33667

  More actions

  December 10, 2009 at 2:18 am

  #1090411

  Use a calendar table.

  http://sqlserver2000.databases.aspfaq.com/why-should-i-consider-using-an-auxiliary-calendar-table.html

  ---

  
  Clear Sky SQL
  My Blog[/url]

• ss-457805

  SSCertifiable

  Points: 5872

  More actions

  December 10, 2009 at 2:36 am

  #1090418

  I had to do this recently..

  This is what i found:

  DECLARE @DateOld datetime, @DateNew datetime SET @DateOld = '23-Nov-2009' SET @DateNew = GETDATE()

  SET DATEFIRST 1

  SELECT DATEDIFF (day, @DateOld, @DateNew) -

  (2 * DATEDIFF(week, @DateOld, @DateNew)) -

  CASE WHEN DATEPART(weekday, @DateOld + @@DATEFIRST) = 1 THEN 1 ELSE 0 END -

  CASE WHEN DATEPART(weekday, @DateNew + @@DATEFIRST) = 1 THEN 1 ELSE 0 END

  blog: http://sarveshsingh.comTwitter: @sarveshsing