Results 1 to 6 of 6
  1. #1
    Join Date
    Oct 2003
    Location
    Brisbane, Australia
    Posts
    30

    Question Unanswered: date between criteria output error

    Hi all,

    I have a really simple sql query here that displays all records between a certain date:

    SELECT *
    FROM billing
    WHERE [date] Between #2/10/2003# And #10/10/2003#

    However, the output of this query has some weird records showing. Basically all records get displayed before the 10/10/03 but records also get outputted that are prior to the 02/10/03! Infact the whole table gets displayed up to the 10/10/03....

    Any ideas on why this is happening and how to fix this problemo?

    Regards,
    ChillyLounge.

  2. #2
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    is it a US vs ROW date format thing?
    what's the earliest date returned? >= 10th Feb 2003 ???

    izy
    currently using SS 2008R2

  3. #3
    Join Date
    Oct 2003
    Location
    Brisbane, Australia
    Posts
    30
    yep the earliest date that gets displayed is later than feb the 10th...

  4. #4
    Join Date
    Oct 2003
    Location
    Ger
    Posts
    1,969
    Provided Answers: 1

    Question

    If you use the QueryWiz do you get the same results?
    I mean without writing sql

  5. #5
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    then it looks like a dd/mm/yy vs mm/dd/yy problem.

    try switching windoze regional settings for date format.

    hope you can live with them switched - other work-arounds are a pain in the ....

    izy
    currently using SS 2008R2

  6. #6
    Join Date
    Oct 2003
    Location
    Brisbane, Australia
    Posts
    30
    Originally posted by izyrider
    then it looks like a dd/mm/yy vs mm/dd/yy problem.

    try switching windoze regional settings for date format.

    hope you can live with them switched - other work-arounds are a pain in the ....

    izy
    Hi Izy,

    I have had so many dramas with the date being in the format mm/dd/yy. I am in Oz and the client refuses to work in this format.

    I ended up fixing the problem (yes it is a pain in the... ) by declaring hte date fields as a string and splitting it up into characters and reformatting using left(), mid() and right().

    I know it's messy but it works and I'm happy with that

    Thanks for your help.

    Regards,
    Chillylounge.

Posting Permissions

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