Oracle date format

Oracle date format With the functions to_char and to_date, a date format can be used. Example: select to_char(sysdate,'DD/MM/YYYY HH24:MI:SS') from dual; will return something like: 27/12/2011 14:36:43 Here is a list of all the formats that can be used: Format mask Description CC Century SCC Century BC prefixed with - YYYY Year with 4 numbers SYYY Year BC prefixed with - IYYY ISO Year with 4 numbers YY Year with 2 numbers RR Year with 2 numbers with Y2k compatibility YEAR Year in characters SYEAR Year in characters, BC prefixed with - BC BC/AD Indicator * Q Quarter in numbers (1,2,3,4) MM Month of year 01, 02...12 MONTH Month in characters (i.e. January) MON JAN, FEB WW Weeknumber (i.e. 1) W Weeknumber of the month (i.e. 5) IW Weeknumber of the year in ISO standard. DDD Day of year in numbers (i.e. 365) DD Day of the month in numbers (i.e. 28) D Day of week in numbers(i.e. 7) DAY Day of the week in characters (i.e. Monday) FMDAY Day of the week in characters (i.e. Monday) DY Day of the week in short character description (i.e. SUN) J Julian Day (number of days since January 1 4713 BC, where January 1 4713 BC is 1 in Oracle) HH Hournumber of the day (1-12) HH12 Hournumber of the day (1-12) HH24 Hournumber of the day with 24Hours notation (0-23) AM AM or PM PM AM or PM MI Number of minutes (i.e. 59) SS Number of seconds (i.e. 59) SSSSS Number of seconds this day. DS Short date format. Depends on NLS-settings. Use only with timestamp. DL Long date format. Depends on NLS-settings. Use only with timestamp. E Abbreviated era name. Valid only for calendars: Japanese Imperial, ROC Official and Thai Buddha.. (Input-only) EE The full era name FF The fractional seconds. Use with timestamp. FF1..FF9 The fractional seconds. Use with timestamp. The digit controls the number of decimal digits used for fractional seconds. FM Fill Mode: suppresses blianks in output from conversion FX Format Exact: requires exact pattern matching between data and format model. IYY or IY or I the last 3,2,1 digits of the ISO standard year. Output only RM The Roman numeral representation of the month (I .. XII) RR The last 2 digits of the year. RRRR The last 2 digits of the year when used for output. Accepts fout-digit years when used for input. SCC Century. BC dates are prefixed with a minus. CC Century SP Spelled format. Can appear of the end of a number element. The result is always in english. For example month 10 in format MMSP returns "ten" SPTH Spelled and ordinal format; 1 results in first. TH Converts a number to it's ordinal format. For example 1 becoms 1st. TS Short time format. Depends on NLS-settings. Use only with timestamp. TZD Abbreviated time zone name. ie PST. TZH Time zone hour displacement. TZM Time zone minute displacement. TZR Time zone region X Local radix character. In america this is a period (.)

Some of the vital characteristics of SQL

exec InsertOrder;

drop procedure InsertOrder;

CREATE OR REPLACE PACKAGE BODY OrdManagement AS
  FUNCTION MyFunction(Param1 IN NUMBER) RETURN NUMBER IS
  BEGIN
    RETURN Param1;
  END;
  PROCEDURE MyProcedure(Param1 IN NUMBER) IS
    TmpVar NUMBER;
  BEGIN
    TmpVar := Param1;
  END;
  PROCEDURE InsertOrder IS
  BEGIN
  insert into phtOrder(OrderDID,OrderID,ISSACTORDID,OrderCategoryDID)
  values (seq_mLogistics_default.NEXTVAL,'Test127',40883766,217);
  commit;
  END;
END OrdManagement;
-------------------------------------------------------------------------------------------------

exec OrdManagement.InsertOrder;

drop Package OrdManagement;


EXCEPTION
          WHEN OTHERS THEN
              RAISE_APPLICATION_ERROR(-20001,'An error was encountered in addProduct - '||SQLCODE||' -ERROR- '||SQLERRM||'-TRACE-'||DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);

--------------------------------------------------------------------------------------------------


select datediff(TO_DATE('2010/05/04','YYYY/MM/DD'),TO_DATE('2010/05/2','YYYY/MM/DD'))  from dual;

select datediff(TO_DATE('2010/05/04','YYYY/MM/DD'),NULL)  from dual;


CREATE OR REPLACE PROCEDURE InsertOrder IS
BEGIN
   insert into phtOrder(OrderDID,OrderID,ISSACTORDID,OrderCategoryDID)
   values (seq_mLogistics_default.NEXTVAL,'Test126',40883766,217);
 
   commit;
END InsertOrder;