SQL date of the week

I am trying to determine the number of session per weekdays. and I have the sql query that do in it . I have noticed that  this statement is not working AND datediff(day, 0, '11/6/2015') % 7 not in (5, 6). What I am trying to exclude are Saturdays and sundays. My week is Starts from Sunday till Saturday.

Please Help

Select WeekStart = convert(varchar(10),dateadd(week, datediff(week, 0, SU.sessionStart), 0),105),CountID = Count(S.sessionKey)

						FROM SessionUnit SU
						INNER JOIN Session S on S.sessionKey = SU.sessionkey
						INNER JOIN product P on p.productKey = S.productKey
						CROSS APPLY (
						    SELECT item AS SPLITLOCATIONKEY
						    from DelimitedSplit8K(S.locationKeyList, ',')
						    where Item in (34)
						)as ca
						INNER JOIN location AS L2 ON CA.SplitLocationKey = L2.LocationKey 
						AND (L2.locationKey in (34) 
						OR S.locationKey in (34))
						WHERE  su.sessionStart > DateAdd(day,46,dateadd(day, datediff(day, 0, getdate()), 0))
						AND su.sessionStart <  DateAdd(day,91,dateadd(day, datediff(day, 0, getdate()), 0))
						AND datediff(day, 0, '11/6/2015') % 7 not in (5, 6)
						AND p.productKey = 2  
						and SU.instructorKey = 3120
						 AND SU.sessionStart >= DATEADD(WEEK, DATEDIFF(WEEK, 0, '11/6/2015'), 0)
			 			AND SU.sessionStart <  DATEADD(WEEK, DATEDIFF(WEEK, 0, '11/6/2015')+1, 0)
						GROUP BY
						 dateadd(week, datediff(week, 0, SU.sessionStart), 0)

Open in new window

LVL 19
erikTsomikSystem Architect, CF programmer Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Kyle AbrahamsSenior .Net DeveloperCommented:
use
  datepart(dw, day) not in (1, 6)

Open in new window


instead
0
erikTsomikSystem Architect, CF programmer Author Commented:
Thank you. But I think i need to change it to  datepart(dw, day) not in (1, 7) because it leaves Saturdays and I only need wekdays. Correct me if I am wrong
0
Kyle AbrahamsSenior .Net DeveloperCommented:
Typo on my part . . . 1,7 is correct for Saturday & Sunday.  Note that the value datepart returns can change by setting the datefirst param.

More info can be found here.
https://msdn.microsoft.com/en-us/library/ms174420.aspx
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
HuaMin ChenProblem resolverCommented:
You should put 2 date values to Datediff like

select datediff(d,convert(datetime,'20150603',112),getdate())

Use this condition

datename(DW,Date_column) NOT IN('Saturday','Sunday')

to exclude Saturday and Sunday.
0
PortletPaulfreelancerCommented:
Erik,

Using % 7 IS a good technique as it is not affected by ANY server settings and it s NOT dependent on language either.

You simply had not used a column identifier in your where condition, instead you had used a string constant '11//6/2015' - not sure why you did that.

AND datediff(day, 0, [DateColumnRefHere] ) % 7 not in (5, 6)

e.g. this result
| 2015-06-10 | Wednesday | 2 |
| 2015-06-11 |  Thursday | 3 |
| 2015-06-12 |    Friday | 4 |
| 2015-06-15 |    Monday | 0 |
| 2015-06-16 |   Tuesday | 1 |
| 2015-06-17 | Wednesday | 2 |
| 2015-06-18 |  Thursday | 3 |

Open in new window

from this query:
select 
      format([DateTimeColumn],'yyyy-MM-dd')
    , datename(dw,[DateTimeColumn])
    , datediff(day, 0, [DateTimeColumn]) % 7 
from table1    
where datediff(day, 0, [DateTimeColumn]) % 7 NOT IN(5,6)
;

Open in new window

using this sample data:
CREATE TABLE Table1
    ([DateTimeColumn] datetime)
;
    
INSERT INTO Table1
    ([DateTimeColumn])
VALUES
    ('2015-06-10 00:00:00'),
    ('2015-06-11 00:00:00'),
    ('2015-06-12 00:00:00'),
    ('2015-06-13 00:00:00'),
    ('2015-06-14 00:00:00'),
    ('2015-06-15 00:00:00'),
    ('2015-06-16 00:00:00'),
    ('2015-06-17 00:00:00'),
    ('2015-06-18 00:00:00')
;

Open in new window

0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.