Results 1 to 6 of 6
  1. #1
    Join Date
    Jan 2003
    Posts
    7

    Exclamation Unanswered: How can I fix this ?

    I have a table called Tickets with a field called Time, which is a timestamp field. The format of the timestamp field is ("year-month-day: hour:minutes:seconds")

    Now I want to be able to match records for certain dates. I am unable to do it, and I think its not matching it because of the hours:minutes:seconds). So I tried using DATE_Format to display the date without the time but still no luck.

    Here is the query


    mysql> select DATE_FORMAT(Time, '%m/%d/%Y') AS day from Tickets WHERE TicketNum=6500;
    +--------------+
    | day |
    +--------------+
    | 01/17/2003 |
    +--------------+
    1 row in set (0.00 sec)

    mysql> select TicketNum, DATE_FORMAT(Time, '%m/%d/%Y') AS day from Tickets WHERE day=01/17/2003;
    ERROR 1054: Unknown column 'day' in 'where clause'


    Here is how the Time field looks for that day without the Date_Format

    mysql> select TicketNum, Time from Tickets WHERE TicketNum=6500;
    +----------+-------------------------+
    | TicketNum | Time |
    +--------------+---------------------+
    | 6500 | 2003-01-17 10:33:33 |
    +--------------+---------------------+
    1 row in set (0.00 sec)


    Any help will be appreciated.

    Thanks !

  2. #2
    Join Date
    Sep 2002
    Location
    Kyiv, Ukraine
    Posts
    77
    I guess date format in WHERE should be different. Try this one:

    Code:
    SELECT TicketNum, DATE_FORMAT(Time, '%m/%d/%Y') AS day
       FROM Tickets
       WHERE day='01172003';
    Yours faithfully,
    Yaroslav Zaremba

  3. #3
    Join Date
    Jan 2003
    Posts
    7

    Red face

    Originally posted by aZa
    I guess date format in WHERE should be different. Try this one:

    Code:
    SELECT TicketNum, DATE_FORMAT(Time, '%m/%d/%Y') AS day
       FROM Tickets
       WHERE day='01172003';
    Still didn't work

    mysql> SELECT TicketNum, DATE_FORMAT(Time, '%m/%d/%Y') AS day
    -> FROM Tickets
    -> WHERE day='01172003';
    ERROR 1054: Unknown column 'day' in 'where clause'


    The problem seems to be that its not recognizing "day" in the WHERE clause.

    Any other ideas ?

  4. #4
    Join Date
    Sep 2002
    Location
    Kyiv, Ukraine
    Posts
    77
    Forgot to remove slashes ...

    Code:
    SELECT TicketNum, DATE_FORMAT(Time, '%m%d%Y') AS day
       FROM Tickets
       WHERE day='01172003';
    And if using your query, try this:

    Code:
    SELECT TicketNum, DATE_FORMAT(Time, '%m/%d/%Y') AS day
       FROM Tickets
       WHERE day='01/17/2003';
    I think the absence of quotes is the possible reason for mistake, because DATE_FORMAT gives away string type (doesn't it?). But anyway I'm not very long in MySQL/SQL so not sure about that one.
    Yours faithfully,
    Yaroslav Zaremba

  5. #5
    Join Date
    Jan 2003
    Posts
    7
    Well, I figured out the solution to this problem from some help from the newsgroups.

    The answer for the range of dates lies in using between in the WHERE Clause.

    select Time from Tickets where Time between "2003-01-16"
    and "2003-01-18";

    for one particular day.. i can use Like

    select Time from Tickets where Time LIKE "2003-01-16%"

    that solves the problem.

    thanks to everyone who responded.

  6. #6
    Join Date
    Sep 2002
    Location
    Kyiv, Ukraine
    Posts
    77
    Thanks for the info on problem solution. Much appreciated.
    Yours faithfully,
    Yaroslav Zaremba

Posting Permissions

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