SQL file date and time format

Question

I'm just looking to see how I can call a current date and time stamp in an SQL file OR SQL ORACLE DEVELOPER.

I need it to look like:

DBMS_OUTPUT.PUT_LINE( '---- Run Completed on' || ' ' || 'Jan, ' || '6, ' || '2021' || ' at ' || ' 12:34 ');

or when you actually run it (Example date and time, but the format I need):

---- Run Completed on Jan 6, 2021 at 12:34

What are the proper commands to extract the Month, day, year and time correctly inside my DBMS_OUTPUT.PUT_LINE statement so I can display the information like above.

Thanks in advance.

Answer 1

You can use TO_CHAR() conversion as

DECLARE
  dt DATE := sysdate; -- this might be replaced by your real date value
BEGIN 
  DBMS_OUTPUT.PUT_LINE( 'Run Completed on '||TO_CHAR(dt,'Mon DD,YYYY','NLS_DATE_LANGUAGE=English')||' at '||TO_CHAR(dt,'HH24:MI') );
END;  
/

You can save the above content into a file such as mySQLfile.sql, then call

SQL> SET SERVEROUTPUT ON
SQL> @C:\path\to\file\mySQLfile.sql