how to get current datetime in SQL?

NOW() returns 2009-08-05 15:13:00

CURDATE() returns 2009-08-05

CURTIME() returns 15:13:00

I always just use NOW():

INSERT INTO table (lastModifiedTime) VALUES (NOW())

http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_now

Complete answer:

1. Is there a function available in SQL?
Yes, the SQL 92 spec, Oct 97, pg. 171, section 6.16 specifies this functions:

CURRENT_TIME       Time of day at moment of evaluation
CURRENT_DATE       Date at moment of evaluation
CURRENT_TIMESTAMP  Date & Time at moment of evaluation

2. It is implementation depended so each database has its own function for this?
Each database has its own implementations, but they have to implement the three function above if they comply with the SQL 92 specification (but depends on the version of the spec)

3. What is the function available in MySQL?

NOW() returns 2009-08-05 15:13:00  
CURDATE() returns 2009-08-05  
CURTIME() returns 15:13:00