Access to SQL Server 7 syntax help

This runs in Access, but SQL Server 7 complains that BETWEEN is unrecognized. Can anyone help me? thanks

SELECT yearId, IIf(Date() BETWEEN [qrtOneStart] AND [qrtOneEnd],1, IIf(Date() BETWEEN [qrtTwoStart] AND [qrtTwoEnd], 2, IIf(Date() BETWEEN [qrtThreeStart] AND [qrtThreeEnd], 3, 4))) AS CurrentQrt, yearName
FROM tblYearSELECT
yearId,
CASE
WHEN GETDATE() BETWEEN qrtOneStart AND qrtOneEnd THEN 1
WHEN GETDATE() BETWEEN qrtTwoStart AND qrtTwoEnd THEN 2
ELSE 0
END AS Quarter
FROM
tblYear

--SQL Server does not support IIF. That's an Access-only command.|||I'd like to address something since someone else brought it up.

I do BETWEEN @.date(s) all the time. However, if it is a datetime field, you have to do it like this:

BETWEEN '05/07/2004 0:00' AND '05/07/2004 23:59'

Right? You have to specify the times if you are doing between on a datetime field. As far as I know, there isn't just a Date datatype, correct? Unless you stored the value as varchar(10) i.e. 05/07/2004.

So, what I've resolved to doing is CONVERT'ng the datetime to just the date, and excluding the time. Does this sound proper? Just Curious...|||Using Convert() to strip the time from a DATETIME is easy, but it causes awful performance problems. Using BETWEEN is usually much better if an index exists, and slightly better even if no index exists.

The reason for this is that BETWEEN can "ride" an index, zeroing in quickly on the rows of interest. Using Convert() forces SQL Server to examine every row in the table (which it would if there were no index), and also to pass every date through the Convert() function (which is pure overhead).

-PatP|||I understand what you are saying for sure, but let's say I've got a dateteime field, and I am running a DTS package daily to get...say daily sales. I use GETDATE to figure out what the date is. So I HAVE to use CONVERT() to strip out the time because I'm not using a BETWEEN statement if I'm using CONVERT(). Right?

UNLESS! (and I'm sure you, pat, will know this one) I can concatenate(sp?) the 0:00 and 23:59 to the GETDATE() function. LIke:

TodaysDate = GETDATE()& 0:00 ??|||Something like:DECLARE @.dBegin DATETIME
, @.dEnd DATETIME
, @.dNow DATETIME

SELECT @.dNow = GetDate()

SELECT @.dBegin = Convert(CHAR(10), @.dNow, 121)
, @.dEnd = Convert(CHAR(10), @.dNow, 121) + ' 23:59:59.997'

SELECT foo, bar, dit, dash
FROM dbo.morse
WHERE date_processed BETWEEN @.dBegin AND @.eEnd...should probably do what you want.

-PatP|||but...why do you use SELECT @.Foo = Bar? I'm in the habit of using SET @.Foo = Bar. What gives?|||I'm a crusty old phart. The ability to use SET is new, and I'm not always good about using it, especially because SELECT is really more convenient in many ways (and old habits die hard).

I used a list of arbitrary column names to keep the SELECT * "police" at bay. While I have no problem with using SELECT * in an example, there are folks here on the forum that get really hyper about it. ;)

Just pretend that I used SELECT * to reduce the confusion, and you'll be just fine! I agree that you shouldn't use SELECT * in production, but I also think that it helps keep things clearer in a code sample like this.

-PatP