Solved

Need help with SQL query

Posted on 2007-11-28
7
157 Views
Last Modified: 2010-03-20
Hello,

I am trying to build a SQL query that only count working days, I am able to make count all 365 days, but I only need specified working days.

Thanks,
0
Comment
Question by:ametrade
7 Comments
 
LVL 8

Expert Comment

by:digital_thoughts
ID: 20367992
Try this:

SELECT
      DateVar
FROM
      TableName
WHERE
      DATEPART(DW, DateVar) BETWEEN 2 AND 6


DATEPART with the argument "DW" returns a numeric value for the day of the week, 1 being Sunday and 7 being Saturday...
0
 

Author Comment

by:ametrade
ID: 20368033
digital_thoughts I am testing it, will let you know, thx.
0
 
LVL 25

Expert Comment

by:imitchie
ID: 20370245
to be more precise

SELECT DateVar
FROM TableName
WHERE ((DATEPART(DW, DateVar) + @@datefirst) % 7)  BETWEEN 2 AND 6

check BOL for @@datefirst
0
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 
LVL 5

Expert Comment

by:ursangel
ID: 20372091
DATEPART(DW, DateVar) BETWEEN 2 AND 6
this alone will pick up the dates for all the working days, unless otherwise its a specific holiday.
0
 
LVL 25

Expert Comment

by:imitchie
ID: 20372137
ametrade: it's worth your while reading up on DatePart and @@datefirst, if your solution is for more than one small customer in one specific country
0
 

Author Comment

by:ametrade
ID: 20373376
Hi imitchie sorry for not specifying that, this is for Venezuela, thank you.
0
 
LVL 25

Accepted Solution

by:
imitchie earned 500 total points
ID: 20376882
Hi ametrade,
What I mean is that DatePart depends on the @@datefirst setting, which can change depending on the OS regional/SQL Server setting.  using

WHERE ((DATEPART(DW, DateVar) + @@datefirst) % 7)  BETWEEN 2 AND 6

will make it work correctly for any setting and country
0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

In database programming, custom sort order seems to be necessary quite often, at least in my experience and time here at EE. Within the realm of custom sorting is the sorting of numbers and text independently (i.e., treating the numbers as number…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now