Functions for inserting dates into mysql?

[WA]

Hi:
Just wondering, besides now(), what are some other functions in mysql for inserting today's date into a database? For example, one of the field's I have is of type "date" that will hold today's date (but not time).

Thanks,

[raf]

For the date only, something like:
info from MySQL manual
-----------------------------------------
CURDATE()
CURRENT_DATE
Returns today's date as a value in 'YYYY-MM-DD' or YYYYMMDD format, depending on whether the function is used in a string or numeric context:
mysql> SELECT CURDATE();
-> '1997-12-15'
mysql> SELECT CURDATE() + 0;
-> 19971215
-------------------------------------------

Or for some other functions, check here (time, sysdate, timestamps)
http://www.mysql.com/doc/en/Date_and_time_functions.html

[WA]

Thanks raf! Just tried curdate() in an insert statement, and it added:

2003-05-26 00:00:00

Perhaps the function includes the time as well? No matter, I'll check the mysql site more closely.

One thing that keeps confusing me with the date/time functions in mysql is which ones are for insertion and which ones are simply format functions while recalling a date/time.

[raf]

I assume you insert the value (aka 2003-05-26 ) into a datetime column (with a default like 0000-00-00 00:00:00). If you only need the date, it should be a column of type date. More info here http://www.mysql.com/doc/en/Column_types.html

One thing that keeps confusing me with the date/time functions in mysql is which ones are for insertion and which ones are simply format functions while recalling a date/time.
Not me. I'm just to stupid for that. I practically never use these MySQL functions --> not portable if you switch db-format. But if they introduce stored procedures, i'll have to.

Anyway, i'm not sure if there is a difference. I mean, if there are 'insertion' and 'formatting' functions. They just all produce a value. Some functions need an explicit value, some use a system value. I mean, you can possibly use a 'formatfunction' to manipulate a value and insert the result into the db.

[ConfusedOfLife]

I was searching for a way to calculate the difference between 2 dates, and the dumb search engine gave me this page! Now that I'm here, I wana tell ya about another function (I'm sure you know it raf!) called RIGHT, or even LEFT! So, for getting the year-month and day only (without the time part), you can write:


"select LEFT("1993-12-23", 10)"


10 in here declares that it should pull off 10 characters from the left of the string. :o

[raf]

Did you just hijack WA's post ? You're gonna get so banned :D.

Hmm, that LEFT()
Could be useful when you're extracting from a datetime column (i personnally always used EXTRACT() since this return a date and LEFT() returns a string.

I don't quite see the relevance here though. If you want to insert a value, you can't use left. Unless you'd use it like
LEFT(Now() 10)
and that this would work and return the value as yyyy-mm-dd (never tryed it), but that would be quite pointless since curdate() is much easier.

(the only problem in WA's case was the column-type --> if you only insert part of the value (in this case, the date), MySQL will add the defaultvalue for the other part the time). But with LEFT(Now() 10) or by inserting '2003-01-01', that would also be the case)