Advertisement
Help Keep Boards Alive. Support us by going ad free today. See here: https://subscriptions.boards.ie/.
If we do not hit our goal we will be forced to close the site.

Current status: https://keepboardsalive.com/

Annual subs are best for most impact. If you are still undecided on going Ad Free - you can also donate using the Paypal Donate option. All contribution helps. Thank you.
https://www.boards.ie/group/1878-subscribers-forum

Private Group for paid up members of Boards.ie. Join the club.

SQL Problem

  • 17-02-2004 05:18PM
    #1
    JohnnyBravo
    Closed Accounts Posts: 537 ✭✭✭


    SELECT DISTINCT Count(Accepts.Queue)
    FROM Accepts
    WHERE Accepts.Queue='31000 - France' And (Accepts.TimeOfAccept Between 5/4/2002 And 7/5/2002);


    Anyone can tell me where this is wrong

    It has a datatype mismatch


Comments

  • #2
    seamus
    Registered Users, Registered Users 2 Posts: 68,173 ✭✭✭✭seamus


    Originally posted by JohnnyBravo
    SELECT DISTINCT Count(Accepts.Queue)
    FROM Accepts
    WHERE Accepts.Queue='31000 - France' And (Accepts.TimeOfAccept Between 5/4/2002 And 7/5/2002);


    Anyone can tell me where this is wrong

    It has a datatype mismatch
    Edit: Actually, rethunk what I just said....

    Perhaps the part '31000 - France' is being mistaken as a mathematical function, but I doubt it.

    Maybe the dates need to be enclosed in single quotes?


  • #3
    asmith
    Registered Users, Registered Users 2 Posts: 60 ✭✭asmith


    Don't know what database you're using but if it's Oracle here's how I'd write it.

    SELECT Count(*)
    FROM Accepts
    WHERE Accepts.Queue='31000 - France' And (Accepts.TimeOfAccept > to_char('05/04/2002', 'DD/MM/YYYY') and Accepts.TimeOfAccept < to_char('07/05/2002', 'DD/MM/YYYY'));

    Since count() is a group function, having 'distinct' in front of it has no effect; there would only ever be one row returned.


  • #4
    amen
    Registered Users, Registered Users 2 Posts: 2,781 ✭✭✭amen


    if you are using MS SQL
    you could just do
    SELECT Count(*)
    FROM Accepts
    WHERE Accepts.Queue='31000 - France'
    AND
    Accepts.TimeOfAccept Between '5/4/2002' And '7/5/2002'
    If it is MS SQL then if no time is specifed sql assumes that 07/05/2002 is really
    07/05/2002 12:00am
    so your between clause would only return date between
    05/02/2002 12:00am and 06/05/2002 11:59:59PM
    and you would be missing data for 07/05/2002


  • #5
    Morpheus
    Moderators, Society & Culture Moderators Posts: 2,687 Mod ✭✭✭✭Morpheus


    It could be that Your dates need to be enclosed in quotes, depending on whether they are character strings or coming directly from a datetime field in a table

    SELECT DISTINCT Count(Accepts.Queue)
    FROM Accepts
    WHERE Accepts.Queue='31000 - France'
    And (Accepts.TimeOfAccept Between '5/4/2002' And '7/5/2002');

    just double check all the datatypes of the fieldnames your using?

    HTH


Advertisement