• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 588
  • Last Modified:

How to use WHERE with DATEPART in MSSQL

Hi There,

I'd like to pull out records where the day of the week is Friday or Saturday. So I have the following query:

SELECT aId, aStatus
FROM Availability
WHERE DATEPART(weekday,aDate) = 5 OR DATEPART(weekday,aDate) = 6

Open in new window


I get an error message thus:

Microsoft OLE DB Provider for SQL Server error '80040e14'

An expression of non-boolean type specified in a context where a condition is expected

Is it possible to use Datepart in this way, and if so, how can I fix this query? If not, what is the best way to achieve the same result?

Thank you,

Dan
0
stressfreewebs
Asked:
stressfreewebs
1 Solution
 
AnujSQL Server DBACommented:
SELECT aId, aStatus
FROM Availability
WHERE DATEPART(weekday,aDate) IN (5,6)
0
 
lluddenCommented:
The query works fine for me.  You can try putting parenthesis around the conditions, or using IN as suggested above.

Keep in mind that doing a query like that, with calculations on a database field, will always result in a table scan, so performance may be pretty poor if the table is large.  Use a calendar table or an indexed computed field if you need to improve performance.
0
 
stressfreewebsAuthor Commented:
Thanks...no idea why what I had wasn't working, but as soon as I swapped to this tidier version, it worked a treat!
0
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.

Join & Write a Comment

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now