Results 1 to 8 of 8
  1. #1
    Join Date
    Mar 2003
    Posts
    21

    Unanswered: 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

    Code:
    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 tblYear

  2. #2
    Join Date
    Apr 2004
    Location
    Kansas City, MO
    Posts
    734
    SELECT
    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.
    MeanOldDBA
    derrickleggett@hotmail.com
    When life gives you a lemon, fire the DBA.

  3. #3
    Join Date
    Sep 2003
    Location
    Memphis
    Posts
    62

    Perfect Opportunity

    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...

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    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

  5. #5
    Join Date
    Sep 2003
    Location
    Memphis
    Posts
    62
    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 ??

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Something like:
    Code:
    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

  7. #7
    Join Date
    Sep 2003
    Location
    Memphis
    Posts
    62

    maybe a noob question

    but...why do you use SELECT @Foo = Bar? I'm in the habit of using SET @Foo = Bar. What gives?

  8. #8
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •