Exclude weekends between a date range

Question

I have the following query and I am trying to select all records between a date range but I want to exclude weekends...anyone know if this can be done?

SQL isn't my strong point!

Below is the query;

SELECT
    tblTime.*,
    tblProjects.fldProjectDesc,
    tblProducts.fldProductID,
    tblProducts.fldProductDesc
FROM (tblTime
INNER JOIN tblProjects
    ON tblTime.fldProjectID = tblProjects.fldProjectID)
INNER JOIN tblProducts
    ON tblProjects.fldProduct = tblProducts.fldProductID
WHERE fldUserCode = 'pac'
AND fldDate BETWEEN CONVERT(varchar, '13/11/2015', 103) AND CONVERT(varchar, '15/11/2015', 103)
ORDER BY fldDate

Thanks for any help!

You should be using convert(date ...) instead of convert varchar — James Z, Dec 14 '15 at 18:32
@cybermonkey not a duplicate - I was not trying to calculate the number of days...I know questions should be kept to a minimum but at times it seems people are more concerned with pointing out faults rather than providing help — Paul C, Dec 14 '15 at 18:38
@MaximillianLaumeister this was closer to what I was asking - I had seen the question but wasn't sure of how I could use any of the answers provided to it - thank you though — Paul C, Dec 14 '15 at 18:39
@PaulC It's a duplicate of the question provided by the user above you. — AStopher, Dec 14 '15 at 18:40
@cybermonkey Yes it could be seen as duplicate of that question but not the one you pointed to — Paul C, Dec 14 '15 at 18:42

score 1 · Accepted Answer · answered Dec 14 '15 at 18:30

Use Dataname Function

SELECT tbltime.*, 
       tblprojects.fldprojectdesc, 
       tblproducts.fldproductid, 
       tblproducts.fldproductdesc 
FROM   (tbltime 
        INNER JOIN tblprojects 
                ON tbltime.fldprojectid = tblprojects.fldprojectid) 
       INNER JOIN tblproducts 
               ON tblprojects.fldproduct = tblproducts.fldproductid 
WHERE  fldusercode = 'pac' 
       AND flddate BETWEEN CONVERT(VARCHAR, '13/11/2015', 103) AND 
                           CONVERT(VARCHAR, '15/11/2015', 103) 
       AND Datename(weekday, flddate) NOT IN ( 'Saturday', 'Sunday' ) 
ORDER  BY flddate

Thank you - this worked. Although it has been pointed out in another question that there could be issues should the language change...this won't affect me however so your answer works for me — Paul C, Dec 14 '15 at 18:41

Exclude weekends between a date range

1 Answers1