Results 1 to 9 of 9
  1. #1
    Join Date
    Nov 2007
    Posts
    43

    Question Unanswered: Select records with particular month/day/year

    Hello guys,

    i have a c_cards table with CardName, IssueDate and Expire date.

    For example, i want to view all records now, which Expire on 11's month.. i do the following:

    Code:
    SELECT c_cards.CardName, c_cards.IssueDate, c_cards.ExpireDate
    FROM c_cards
    WHERE month(ExpireDate) = 11;
    but it doesnt return any record...

    could u please correct my SQL statement?

    thanks

  2. #2
    Join Date
    Nov 2003
    Posts
    1,487
    That's odd... Looks like it should work. For the Heck of it try this....

    Code:
    SELECT *
    FROM c_cards
    WHERE Month(ExpireDate)=11;
    Month should work whether ExpireDate is a Date/Time DataType or a Text DataType, as long as the data resembles a Date. The only other thing I can think of off hand is that....there is nothing Expiring in the month of November (11)
    Environment:
    Self Taught In ALL Environments.....And It Shows!


  3. #3
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Here's a quick check
    Code:
    SELECT Month(ExpireDate)
         , Count(*)
    FROM   c_cards
    GROUP
        BY ExpireDate
    Dates should be stored as dates, not as text.
    Why do you think we have that datatype
    George
    Home | Blog

  4. #4
    Join Date
    Nov 2003
    Posts
    1,487
    Dates should be stored as dates, not as text.
    Why do you think we have that datatype
    Indeed. It's unfortunate that not everyone follows that particular standard.

    .
    Environment:
    Self Taught In ALL Environments.....And It Shows!


  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    be fair some newbies know an awful lot more than people who have worked on system development for years

    its totally unreasonable to expect these knowledgeable newbies to bother with minor details like storing dates as the date datatype, because after all they know better, and isn't it down to the computer to sort to all those trifling details.

    but it is expected that hoary old practioners shall jump at the first cry of pain form the recently disabused knowledgable newbie

    must have got out of bed on the wrong side this morning.. mind you at least I went to bed.. how come you are up at 04:00ish CyberLynx?

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by georgev
    Here's a quick check
    Code:
    SELECT Month(ExpireDate)
         , Count(*)
    FROM   c_cards
    GROUP
        BY ExpireDate
    george, slow down, you're doing the old Fire-Ready-Aim thing again

    the above query will return one row for every different ExpireDate in the table
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Thats correct Rudy,to prove which months have a result.
    Which would explain why a perfectly acceptable query

    Quote Originally Posted by TeslaShock
    Code:
    SELECT c_cards.CardName, c_cards.IssueDate, c_cards.ExpireDate
    FROM c_cards
    WHERE month(ExpireDate) = 11;
    is not returning results for the specified month
    George
    Home | Blog

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by georgev
    Thats correct Rudy,to prove which months have a result.
    that's correct, george

    take a closer look at your query

    hint: the GROUP BY clause
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    Access is reasonably competent at this stuff - here's an immediate window transcript:

    ? month(#23/11/2007#)
    11
    ? month(#11/23/2007#)
    11
    ? month(#11/2007#)
    11
    ? month(#11/07#)
    11
    ? month(#1/07#)
    1


    and here's another showing some limitations:

    ? month("23/11/2007")
    11
    ? month("11/23/2007")
    11
    ? month("11/2007")
    11
    ? month("11/07")
    7
    ? month("1/07")
    7


    and here is one showing total collapse:

    ? month(23/11/2007)
    12
    ? month(11/23/2007)
    12
    ? month(11/2007)
    12
    ? month(11/07)
    12
    ? month(1/07)
    12


    the last results being explained by:
    ? ((11/23)/2007)
    2.38296397391738E-04
    ? month(2.38296397391738E-04)
    12

    etc... because of Access internal datetime format
    ? format$(2.38296397391738E-04 , "YYYY-MM-DD HH:NNS")
    1899-12-30 00:00:21


    my credit cards have expiry dates in the format MM/YY or MM/YYYY

    izy
    currently using SS 2008R2

Posting Permissions

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