Results 1 to 3 of 3

Thread: a question about Date data type

  1. #1
    Join Date
    Apr 2003
    Posts
    2

    Question a question about Date data type

    I have a sql command:
    SELECT * FROM Announce WHERE Deadline>'2003/04/18 14:43:00';
    Deadline is a date data type. Now I want to compare 2 different date. However, Access pop a error message: data type mismatch in criteria expression. Can anybody teach me how i do?

  2. #2
    Join Date
    Feb 2003
    Posts
    102
    Hi Aply,

    You get the data type mismatch cause you are comparing a date to a string. You are using the string delimiter (a single quote) instead of the hash mark date delimiter (#).

    JET (the database engine Access uses) cannot implicitly coerce the date data type to a string or vice-a-versa AFAIK.

    This, I think is due to JET storing dates as number (day 1 being 30-Dec-1899).

    However comparing a date to string in VBA doesn't raise an error (beats me why not).

    Try ?clng(date) in the debug window. This should tell you how many days have passed since 30-dec-1899.

    Anyways to answer your question:

    SELECT * FROM Announce WHERE Deadline > #2003/04/18 14:43:00#;

    or

    SELECT * FROM Announce WHERE Deadline>Cdate('2003/04/10 14:43:00');

    where you are explicitly casting the string to a date.

    HTH,

    Peter

  3. #3
    Join Date
    Apr 2003
    Posts
    2
    Thx for ur help. ^_^

Posting Permissions

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