Results 1 to 8 of 8

Thread: [RESOLVED] < doesn't seem to be working

  1. #1

    Thread Starter
    Lively Member eatmycode's Avatar
    Join Date
    Mar 2010
    Location
    Kingston upon Hull
    Posts
    74

    Resolved [RESOLVED] < doesn't seem to be working

    Bear with me, but this is driving me nuts. I'm learning MS Distributed Application Development and there is this sql query:
    Code:
                    SELECT SUM(SubTotal) AS SumSubTotal,
                    SUM(TaxAmt) AS SumTaxAmt,
                    SUM(Freight) AS SumFreight,
                    SUM(TotalDue) AS SumTotalDue
                    FROM [Purchasing].[PurchaseOrderHeader]
                    WHERE [VendorID] = 1
                    AND [OrderDate] > 01-09-2003 AND [OrderDate] < 30-09-2003
    When the application runs it returns no results, even though there are entries for that period. I'm using the AdventureWorks database.

    When I remove the last part of the query "OrderDate < 30-09-2003" or just implement it without the first OrderDate clause, it returns the results. Can anybody see where I'm going wrong?
    Technik ... Kniff, die Welt so einzurichten, dass wir sie nicht erleben mussen

    Max Frisch

  2. #2
    Junior Member
    Join Date
    Sep 2008
    Posts
    18

    Re: < doesn't seem to be working

    Whose particular SQL are you using? Depending on that you may need to qualify the dates with a # on each side (Access), or you may be able to use the BETWEEN keyword. Also you're often better off expressing dates in international format to avoid confusion with US dates.

    AND [OrderDate] > #2003/09/01# AND [OrderDate] < #2003/09/30#

    or maybe something like

    AND [OrderDate] BETWEEN #2003/09/01# AND #2003/09/30#

  3. #3
    A SQL Server fool GaryMazzone's Avatar
    Join Date
    Aug 2005
    Location
    Dover,NH
    Posts
    7,495

    Re: < doesn't seem to be working

    Change the # to ' if you are using SQL Server
    Sometimes the Programmer
    Sometimes the DBA

    Mazz1

  4. #4
    Junior Member
    Join Date
    Sep 2008
    Posts
    18

    Re: < doesn't seem to be working

    Quote Originally Posted by GaryMazzone View Post
    Change the # to ' if you are using SQL Server
    LOL yes, so many different SQL syntaxes. You'd think everyone would stick to the ANSI standard.

  5. #5

    Thread Starter
    Lively Member eatmycode's Avatar
    Join Date
    Mar 2010
    Location
    Kingston upon Hull
    Posts
    74

    Re: < doesn't seem to be working

    I am testing the query in SQL Server 2005.

    The query runs without turning it into string format. Like I said previously, if I remove OrderDate > 01-09-2003, a result set is returned. If I remove OrderDate < 30-09-2003, a result set is returned. When I apply both clauses, no result set is returned.

    I would imagine if this query is in Microsoft .NET Frameword 2.0 Distributed Application Development, then in the mind of the author, the query should work.
    Technik ... Kniff, die Welt so einzurichten, dass wir sie nicht erleben mussen

    Max Frisch

  6. #6
    Junior Member
    Join Date
    Sep 2008
    Posts
    18

    Re: < doesn't seem to be working

    I am testing the query in SQL Server 2005
    In that case I'd try:
    AND ( [OrderDate] BETWEEN '2003-09-01' AND '2003-09-30' )

    or if you prefer
    AND ( [OrderDate] > '2003/09/01' AND [OrderDate] < '2003/09/30' )

  7. #7

    Thread Starter
    Lively Member eatmycode's Avatar
    Join Date
    Mar 2010
    Location
    Kingston upon Hull
    Posts
    74

    Re: < doesn't seem to be working

    Thanks mono. That was a night's headache, I can tell you!
    Technik ... Kniff, die Welt so einzurichten, dass wir sie nicht erleben mussen

    Max Frisch

  8. #8
    Junior Member
    Join Date
    Sep 2008
    Posts
    18

    Re: [RESOLVED] < doesn't seem to be working

    np.. now solve my problem

Posting Permissions

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



Click Here to Expand Forum to Full Width