Here are the SQL Date Formats List. You can try yourself and look for the output.
These SQL date formats list are useful in your interview point of view and also can be used in your professional SQL work environment depends on your requirements.
- Select TO_CHAR(date_column, ‘DD-MON-YYYY’) from emp21;
Formats:
d,dd,ddd
mm,mon,month
y,yy,yyy,yyyy,year - select to_char(sysdate,’dd-mm-yyyy hh:mi:ss’) from dual;
- Select TO_CHAR(number_column, ’99,999.00’) from emp21; – If there is decimal value display as it is and if it is not there display .00
- Select to_char(salary, ‘99999.9999’) from emp21;
- SQL> Select to_char(sysdate, ‘yyyy/mm/dd’) FROM dual;
‘2010/12/24’ - SQL> Select to_char(sysdate, ‘FMMonth DD, YYYY’) FROM dual;
‘June 9, 2005’ - SQL> select to_char(sysdate,’HH24:MI:SS’) “Time Now” from dual;
’14:35:56′ - SQL> Select to_char(1.234, ‘9999.9’) FROM dual;
‘1.2’ - SQL> Select to_char(1000.25, ‘9,999.99’) FROM dual;
‘1,000.25’ - SQL> Select to_char(1000.25, ‘$9,999.00’) FROM dual;
‘$1,000.25’ - SQL> Select to_char(emp_date, ‘DD-MM-YYYY HH24:MI’) FROM emp21;
31-12-2005 23.30
For more SQL date format list may also check this post How to create a table for all reporting objects using system date