How do I exclude Weekend days in a SQL Server query

Question

How do I exclude values in a DateTime column that are Saturdays or Sundays   For example  given the following data   date created  2009-11-26 09 00 00   -- Thursday  2009-11-27 09 00 00   -- Friday  2009-11-28 09 00 00   -- Saturday  2009-11-29 09 00 00   -- Sunday  2009-11-30 09 00 00   -- Monday   this is the result I m looking for   date created  2009-11-26 09 00 00   -- Thursday  2009-11-27 09 00 00   -- Friday  2009-11-30 09 00 00   -- Monday   Thanks

User · Answer

Calculate Leave working days in a table column as a default value--updated  If you are using SQL here is the query which can help you  http   gallery technet microsoft com Calculate

User · Answer

The answer depends on your server s week-start set up  so it s either  SELECT  date created  FROM table WHERE DATEPART w  date created   NOT IN  7 1    if Sunday is the first day of the week for your server  or  SELECT  date created  FROM table WHERE DATEPART w  date created   NOT IN  6 7    if Monday is the first day of the week for your server  Comment if you ve got any questions  -

User · Answer

Try the DATENAME   function   select  date created  from table where DATENAME WEEKDAY   date created    lt  gt   Saturday    and DATENAME WEEKDAY   date created    lt  gt   Sunday

User · Answer

When dealing with day-of-week calculations  it s important to take account of the current DATEFIRST settings  This query will always correctly exclude weekend days  using   DATEFIRST to account for any possible setting for the first day of the week   SELECT   FROM your table WHERE   DATEPART dw  date created      DATEFIRST    7  NOT IN  0  1

User · Answer

Assuming you re using SQL Server  use DATEPART with dw   SELECT date created FROM your table WHERE DATEPART dw  date created  NOT IN  1  7     EDIT  I should point out that the actual numeric value returned by DATEPART dw  is determined by the value set by using SET DATEFIRST  http   msdn microsoft com en-us library ms181598 aspx

User · Answer

SELECT date created FROM your table WHERE DATENAME dw  date created  NOT IN   Saturday    Sunday

User · Answer

Try this code  select  DATEDIFF DD  2014-08-01   2014-08-14   1 -  DATEDIFF WK  2014-08-01   2014-08-14    2

[sql-server] How do I exclude Weekend days in a SQL Server query?

Examples related to sql-server

Examples related to tsql

Examples related to date

Examples related to weekend