how to get current datetime in SQL?

sql database current-time
5YrsLaterDBA picture 5YrsLaterDBA · Aug 5, 2009 · Viewed 194.4k times · Source

Want to get current datetime to insert into lastModifiedTime column. I am using MySQL database. My questions are:

  1. is there a function available in SQL? or

  2. it is implementation depended so each database has its own function for this?

  3. what is the function available in MySQL?

Answer

Eduardo Molteni picture Eduardo Molteni · Aug 5, 2009

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

Related questions

Insert into ... values ( SELECT ... FROM ... )

I am trying to INSERT INTO a table using the input from another table. Although this is entirely feasible for many database engines, I always seem to struggle to remember the correct syntax for the SQL engine of the day (…

Read More
What is the difference between "INNER JOIN" and "OUTER JOIN"?

Also how do LEFT JOIN, RIGHT JOIN and FULL JOIN fit in?

Read More
'IF' in 'SELECT' statement - choose output value based on column values

SELECT id, amount FROM report I need amount to be amount if report.type='P' and -amount if report.type='N'. How do I add this to the above query?

Read More