Returning Database Server Current Date And Time

Life would be easy if all database providers use the same SQL syntax for all data management tasks. Unfortunately in the real world, that's not the case. If you're not using an O/R Mapper (or sometimes because of the limitation of it), then you have to resort to write different SQL statements for each database provider for the same task.

The following list shows the SQL syntax to get the current date and time from a database server, depending on the database provider type (all datetime are returned in "yyyyMMddHHmmss" format):

MySQL:

SELECT CONVERT(DATE_FORMAT(CURRENT_TIMESTAMP, '%Y%m%d%H%i%s'), CHAR(14));

MySQL (for supported version which CONVERT is not needed):

SELECT DATE_FORMAT(CURRENT_TIMESTAMP, '%Y%m%d%H%i%s');

Microsoft SQL Server:

SELECT LTRIM(RTRIM(CONVERT(VARCHAR(8),CURRENT_TIMESTAMP,112)+REPLACE(CONVERT(VARCHAR(8),CURRENT_TIMESTAMP,108),':','')));

Oracle:

SELECT TRIM(TO_CHAR(systimestamp, 'YYYYMMDDHH24MISS')) FROM dual;

SQLite:

SELECT STRFTIME('%Y%m%d%H%M%S',CURRENT_TIMESTAMP, 'localtime');

If you find this post helpful, would you buy me a coffee?