IF question

fizzerchris · October 15th, 2007, 03:15 PM

Using SQL Server 2005: I have a variable, @InDate smalldatetime

I want to see if @InDate is a weekend.
I'm using DATEPART(dw, @InDate) being equal to 1 or 7... Do I have to write it as two separate comparisons, IF datepart(...) = 1 OR datepart(...) = 7?

I know a normal SELECT can use something like SELECT...FROM...WHERE column IN (1,7)...

I'd like to make it set based using something like
IF (SELECT DATEPART(dw, @InDate) as date) IN (1,7)

ciderpunx · October 16th, 2007, 10:02 AM

I haven't looked at SQL server since the days of NT4. But, assuming what you're trying to get is all the rows in which the date is on a weekend I think you can do something like:

Code:

SELECT x,y,z FROM myTable WHERE DATEPART(dw, @InDate) IN (1,7)

This is not tested!

I do know /for a fact/ that MySQL does the same thing thus

Code:

SELECT x,y,z FROM myTable WHERE WEEKDAY(inDate) IN (5,6);

--
Charlie Harvey's website - linux, perl, java, anarchism and punk rock: http://charlieharvey.org.uk

fizzerchris · October 17th, 2007, 12:50 AM

I know the SELECT part works, I've done tons of things like that before... I'm asking more of a programming question evaluating IF something is true based on two options. This will be in a function that returns True or False... as in
IF @InDate = 1 or @InDate = 7
RETURN True

(This is obviously not the actual syntax, but I hope you get the idea)

I can get it to work by asking in two separate checks IF @InDate is 1 or IF @InDate is 7, but I'm just looking for something "prettier"... asking, in one statement, If @InDate is 1 or 7.

fizzerchris · October 17th, 2007, 10:41 PM

Well, it works... here's a simplistic example:

IF 0 IN (0,2,3,4)
PRINT 'Yup'

I was just typoing something to begin with.