How to get current date

Frunkie · #1 (**permalink**) 08-17-07, 19:36

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

r937 · #2 (**permalink**) 08-17-07, 21:26

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?

Frunkie · #3 (**permalink**) 08-17-07, 21:38

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.

r937 · #4 (**permalink**) 08-17-07, 21:52

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

Frunkie · #5 (**permalink**) 08-17-07, 21:54

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.

r937 · #6 (**permalink**) 08-17-07, 21:55

would you at least do me the courtesy of trying my query

Frunkie · #7 (**permalink**) 08-17-07, 22:02

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.

Frunkie · #8 (**permalink**) 08-17-07, 22:05

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"

Pat Phelan · #9 (**permalink**) 08-17-07, 22:14

You need to adjust the MySQL "crabby factor", or use date_add() with an underscore.

-PatP

r937 · #10 (**permalink**) 08-17-07, 22:14

try it after removing all the quotes

you don't need the backticks, either

r937 · #11 (**permalink**) 08-17-07, 22:15

ah, yes, thank you pat, in mysql it's date_add, not dateadd

but the quotes are defo wrong

Frunkie · #12 (**permalink**) 08-17-07, 22:21

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)

r937 · #13 (**permalink**) 08-17-07, 22:28

okay, you removed the backticks but left a couple of the quotes

please remove all the quotes

Frunkie · #14 (**permalink**) 08-17-07, 22:38

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?

r937 · #15 (**permalink**) 08-17-07, 23:00

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

Internet Services

Web Development

Digital Marketing

Consumer Tech