Results 1 to 2 of 2
  1. #1
    Join Date
    Oct 2003

    Unanswered: Date stored as Text

    For update purposes I have had to store a date in a text field format. I want a query which searches all the dates and brings up only one which later than todays date. this is how far i have got but i get the wrong answer i think it is because it is stored as text.

    Select top 1 * from nextmatch where date > now();

    date matchdesc
    23/11/2003 Factory Bank, Ramsey

    This is what i get, and as you can see this is not right.

    date matchdesc
    05/10/2003 Farm Shop Lake
    02/11/2003 Old Nene March
    23/11/2003 Factory Bank, Ramsey
    14/12/2003 Old Nene Benwick
    04/01/2004 Ramsey, St Mary's
    01/02/2004 Horseway Sluice, Chatteris
    29/02/2004 Ramsey, St Mary's

    Thanks for the help

  2. #2
    Join Date
    Oct 2003

    try this

    in your where clause

    where cast( substring([date], 4,2)
    + '/' + substring([date], 1,2)
    + '/' + substring([date], 7,4)as datetime) > getdate()

    just looks like you need to put it back in the proper format and convert it back to date time.

    if that does not work let me know.

Posting Permissions

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