Page 1 of 2 12 LastLast
Results 1 to 15 of 22
  1. #1
    Join Date
    Mar 2007
    Location
    010101010110100
    Posts
    803

    Unanswered: How to get current date

    Hi all,

    Can someone tell me if it is possible to select records having a timestamp by using the CURDATE() function?

    I need to compare the current date with a timestamp record. Where I am having a problem is with the minutes and seconds on the timestamp. I don't need these as I only am concerned with the year, month and day.

    Should I use in addition to the timestamp, a date field as well?

    Thanks.. Frank

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    could you please be a bit more specific

    what is the datatype of the column in question? DATETIME? TIMESTAMP? DATE? or perhaps INTEGER with a unix timestamp?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Mar 2007
    Location
    010101010110100
    Posts
    803
    It is of datatype timestamp. Sorry, I should have indicated that. It is minutes and seconds attached to the timestamp that make it impossible for me to get the right results. Or results that I need.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    where daColumn >= current_date
      and daColumn < dateadd(current_date, interval 1 day)
    this is the "best practice" approach, as it allows the optimizer to use the index (if any) on the column
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Mar 2007
    Location
    010101010110100
    Posts
    803
    Code:
    SELECT *
    FROM
    `employee_UI_Error_Log`
    WHERE
    `employee_UI_Error_Log`.`stamp` =  'CURDATE()'
    This is basically what I need. I need to limit my result set to those records of the day. Unfortunately, TIMESTAMP has minutes and seconds attached. I need the timestamp because I need the time for reports. I was hoping I could use timestamp for both of my needs.

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    would you at least do me the courtesy of trying my query
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Mar 2007
    Location
    010101010110100
    Posts
    803
    Quote Originally Posted by r937
    would you at least do me the courtesy of trying my query
    Yes of course Rudy. doing it now. I think we posted at the same time.

  8. #8
    Join Date
    Mar 2007
    Location
    010101010110100
    Posts
    803
    Code:
    SELECT *
    FROM
    `employee_UI_Error_Log`
    WHERE
    `employee_UI_Error_Log`.`stamp` >=  'current_date'
    AND `employee_UI_Error_Log`.`stamp` < dateadd('current_date, interval 1 day')
    Ok, this yields the following error message
    "FUNCTION dateadd does not exist"

  9. #9
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    You need to adjust the MySQL "crabby factor", or use date_add() with an underscore.

    -PatP

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    try it after removing all the quotes

    you don't need the backticks, either
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  11. #11
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    ah, yes, thank you pat, in mysql it's date_add, not dateadd

    but the quotes are defo wrong
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  12. #12
    Join Date
    Mar 2007
    Location
    010101010110100
    Posts
    803
    Thanks guys. When I added the underscore, the error went away however, shouldn't it be returning what is in the db? I have 2 test rows in there now and they will not return. Here is the DDL
    Code:
    SELECT *
    FROM
    employee_UI_Error_Log
    WHERE
    employee_UI_Error_Log.stamp >=  'current_date'
    AND employee_UI_Error_Log.stamp < date_add(current_date, interval 1 day)

  13. #13
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    okay, you removed the backticks but left a couple of the quotes

    please remove all the quotes
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  14. #14
    Join Date
    Mar 2007
    Location
    010101010110100
    Posts
    803
    Quote Originally Posted by r937
    okay, you removed the backticks but left a couple of the quotes

    please remove all the quotes
    Rudy, that worked. Thank you..

    I have never experienced backticks that has ever stopped a query from working before. Is it good practice to write sql without them or is there a rule as to when to use them and not to?

  15. #15
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    backticks are required whenever the table or column name (a) contains a special character, like a space, or (b) is a reserved word

    since "best practice" says you should never do either, thus you will never need to use backticks at all

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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