How To Determine which days are weekends

• niteshrajgopal

  SSCommitted

  Points: 1877

  More actions

  June 3, 2011 at 3:17 am

  #251112

  Hi Guys,

  I have written a view that consists of data from a table called Tasks. Basically each task has a start date, end date, estimated end date, estimated hours, actual hours complete.

  Now I calculate the estimated end date like this:

  I divide the number of estimated hours by 2. Then add the result to the Start Date using the DateAdd function.(my boss came up with that algorithm)

  However, I need to determine which of the days between the Start Date and Estimated End Date are weekends. If there is a weekend I need to add two days more to the estimated end date.

  Please help me achieve this.

• spaghettidba

  SSC Guru

  Points: 105732

  More actions

  June 3, 2011 at 4:35 am

  #1333572

  You can use DATEPART to check the weekday:

  select datepart(weekday , getdate())

  Depending on your @@DATEFIRST setting, you will have to check for days 1 and 7 (default for US English) or 6 and 7 (default for Italian).

  Hope this helps

  Gianluca

  -- Gianluca Sartori

• Vedran Kesegic

  SSCrazy Eights

  Points: 9302

  More actions

  June 3, 2011 at 5:04 pm

  #1333982

  This expression is independent of any language and datefirst settings:

  -- gives 1 for monday..., 7 for sunday

  datepart( weekday, @datetime + @@datefirst - 1 )

  _____________________________________________________
  Microsoft Certified Master: SQL Server 2008
  XDetails Addin - for SQL Developers
  blog.sqlxdetails.com - Transaction log myths
  

• spaghettidba

  SSC Guru

  Points: 105732

  More actions

  June 6, 2011 at 1:20 am

  #1334203

  Vedran Kesegic (6/3/2011)

  ---

  This expression is independent of any language and datefirst settings:

  -- gives 1 for monday..., 7 for sunday

  datepart( weekday, @datetime + @@datefirst - 1 )

  This is a really smart solution! Saved it in my code snippets. 🙂

  Thanks a lot.

  -- Gianluca Sartori

• Ashok Teotia

  SSCrazy

  Points: 2407

  More actions

  June 7, 2011 at 2:49 am

  #1334838

  Hi,

  There is no harm in usung this

  select datename(dw,getdate())

  and can check for Sat or Sun accordingly.

  Regards

  Ashok

• spaghettidba

  SSC Guru

  Points: 105732

  More actions

  June 7, 2011 at 2:56 am

  #1334839

  ashok.faridabad1984 (6/7/2011)

  ---

  Hi,

  There is no harm in usung this

  select datename(dw,getdate())

  and can check for Sat or Sun accordingly.

  Regards

  Ashok

  It still depends on the client's language:

  set language italian

  select datename(dw,getdate())

  Saturday is called "Sabato" here.

  -- Gianluca Sartori