Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Need help with SQL query

Posted on 2007-11-28
7
Medium Priority
?
210 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
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 2000 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

[Webinar] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
The Relationships Diagram is a good way to get an overall view of what a database is keeping track of. It is also where relationships are defined. A relationship specifies how two tables connect to each other. As you build tables in Microsoft Ac…
Suggested Courses

581 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