WIXLIB

TO CHAR Function with DatesTO CHAR(date, 'fmt’)The format model: Must be enclosed in single quotation marks and is case sensitive Can include any valid date format element Has an fm element to remove padded blanks or suppressleading zeros Is separated from the date value by a commaDisplaying a Date in a Specific FormatPreviously, all Oracle date values were displayed in the DD-MON-YYformat. The TO CHAR function allows you to convert a date from thisdefault fomat to one specified by you.Guidelines The format model must be enclosed in single quotation marks and is casesensitıve; The format model can include any valid date format element. Be sure toseparate the date value from the format model by a comma. The names of days and months in the output are automaticallypadded with blanks. To remove padded blanks or to suppress leading zeros, use the fillmode fm element. Yon can resize the display width of the resulting character fıeld with theSQL*Plus COLUMN command. The resultant column width is 80 characters by default.1

ExamplesSELECT empno, TO CHAR(hiredate, 'MM/YY') "ĐŞE GĐRĐŞ TARĐHĐ"FROM empWHERE ename 'BLAKE' ;EMP OE AMEĐŞE GĐRĐŞ7698BLAKE05/81SELECT ename, empnoTO CHAR( hiredate , 'fmMonth DD, YYYY' ) HiredateFROM empWHERE deptno 30E AMEEMP OHIREDATEALLEN7499 Şubat 20, 1981WARD7521 Şubat 22, 1981MARTIN7654 Eylül 28, 1981BLAKE7698 Mayıs 1, 1981TURNER7844 Eylül 8, 1981JAMES7900 Aralık 3, 19816 rows selected.SELECT ename, hiredate, TO CHAR( hiredate , 'fmDY.Mon.YYY' )FROM EMP;E AME HIREDATETO CHAR(HIREDATE,'FMDY.MO M.Şub.981.14 rows selected.2

Using TO CHAR Function with DatesSELECT ename, hiredate,TO CHAR(hiredate, 'DD Month YYYY' ) "İşeGiriş Tarihi"FROM EMP;E AMESMITHHIREDATE17/12/1980Đşe Giriş Tarihi17 Aralık 1980ALLEN20/02/198120 Şubat 1981WARD22/02/198122 Şubat 1981JONES02/04/198102 Nisan 1981MARTIN28/09/198128 Eylül 1981BLAKE01/05/198101 Mayıs 1981CLARK09/06/198109 Haziran 1981SCOTT09/12/198209 Aralık 1982KING17/11/198117 Kasım 1981TURNER08/09/198108 Eylül 1981ADAMS12/01/198312 Ocak 1983JAMES03/12/198103 Aralık 1981FORD03/12/198103 Aralık 1981MILLER23/01/198223 Ocak 198214 rows selected.3

ExampleSELECT sysdate,TO CHAR ( sysdate, 'fmDD.MM.YYYY fmHH:MI:SS PM' ) ZamanFROM dual ;SYSDATE18/03/2007ZAMA18.3.2007 06:23:02 ÖSSELECT sysdate,TO CHAR ( sysdate, 'fmDD.MM.YYYY fmHH:MI:SS AM' ) ZamanFROM dual ;SYSDATE18/03/2007ZAMA18.3.2007 06:23:02 ÖSSELECT sysdate,TO CHAR ( sysdate, 'fmDD.MM.YYYY fmHH12:MI:SS PM' ) ZamanFROMdual ;SYSDATE18/03/2007ZAMA18.3.2007 06:23:02 ÖS4

SELECT sysdate,TO CHAR ( sysdate, 'fmDD.MM.YYYY fmHH24:MI:SS' ) ZamanFROM dual ;SYSDATE18/03/2007ZAMA18.3.2007 18:23:02SELECT sysdate,TO CHAR ( sysdate, 'fmDD.MM.YYYY fmHH24:MI:SS AM' ) ZamanFROM dual ;SYSDATE18/03/2007ZAMA18.3.2007 18: 23:02 ÖS5

SELECT ename,TO CHAR(hiredate, 'fmDdspth "of" Month YYYY fmHH:MI:SS PM' )HIREDATEFROMemp;E AMESMITHHIREDATESeventeenth of Aralık 1980 12:00:00 ÖÖALLENTwentieth of Şubat 1981 12:00:00 ÖÖ.14 rows selected.6

Examples : YY , CC , SCCSELECT TO CHAR( TO DATE('28-11-1942') , 'YY') YüzyılFROM dual;YIL42SELECT TO CHAR( TO DATE('28-11-1942') , 'CC') YüzyılFROM dual;YÜZYIL20SELECT TO CHAR( TO DATE('28-11-2942') , 'SCC') BinyılFROM dual;BI YIL307

Examples : D , DD , DDDSELECT TO CHAR( TO DATE('28-11-1942') , 'fmd' ) "Day Of Week"FROM dual ;Day Of Week6SELECT TO CHAR( TO DATE('28-11-1942') , 'fmdd' ) "Day Of Month"FROM dual ;Day Of Month28SELECT TO CHAR( TO DATE('28-11-2942') , 'fmddd' ) "Day Of Year"FROM dual ;Day of Year3328

The next example outputs the Julian Day; the number of days since 31 December4713 BC of the given date.SELECT TO CHAR( TO DATE('28-11-2942') , 'fmJ' ) "Julian Day"FROM dual ;Julian Day2430692SELECT TO CHAR( TO DATE('28-11-1942') , 'fmBC' ) "BC Indicator"FROM dual ;BC IndicatorMSSELECT TO CHAR( TO DATE('28-11-1942') , 'fmBC' ) "AD Indicator"FROM dual ;AD IndicatorMS9

SELECT ename, hiredate, TO CHAR(hiredate, 'fmCC')FROM EMP ;E AMEHIREDATETO CHASMITH17/12/198020ALLEN20/02/198120.14 rows selected.SELECT TO CHAR( TO DATE('18-09-1972') , 'w' ) "Week of Month"FROM dual ;Week of Month3SELECT TO CHAR( TO DATE('18-09-1972') , 'ww' ) "Week of Year"FROM dual ;Week of Month3810

TO CHAR (datetime)TO CHAR (datetime) converts date of DATE, TIMESTAMP, TIMESTAMPWITH TIME ZONE, or TIMESTAMP WITH LOCAL TIME ZONE datatypeto a value of VARCHAR2 datatype in the format specified by the date formatfmt. If you omit fmt, then date is converted to a VARCHAR2 value as follows: DATE is converted to a value in the default date format. TIMESTAMP and TIMESTAMP WITH LOCAL TIME ZONE areconverted to values in the default timestamp format. TIMESTAMP WITH TIME ZONE is converted to a value in the defaulttimestamp with time zone format.The 'nlsparams' specifies the language in which month and day names andabbreviations are returned. This argument can have this form:'NLS DATE LANGUAGE language'CREATE TABLE date tab (ts colTIMESTAMP,tsltz colTIMESTAMP WITH LOCAL TIME ZONE,tstz colTIMESTAMP WITH TIME ZONE);11

Oracle date formatFormatmaskDescriptionCCCenturySCCCentury BC prefixed with -YYYYYear with 4 numbersSYYYYear BC prefixed with -IYYYISO Year with 4 numbersYYYear with 2 numbersRRYear with 2 numbers with Y2k compatibilityYEARYear in charactersSYEARYear in characters, BC prefixed with -BC / ADBC/AD Indicator *QQuarter in numbers (1,2,3,4)MMMonth of year 01, 02.12MONTHMonth in characters (i.e. January)MONJAN, FEBWWWeeknumber (i.e. 2)WWeeknumber of the month (i.e. 3)IWWeeknumber of the year in ISO standard.DDDDay of year in numbers (i.e. 234)DDDay of the month in numbers (i.e. 28)DDay of week in numbers(i.e. 7)DAYDay of the week in characters (i.e. Monday)FMDAYDay of the week in characters (i.e. Monday)DYDay of the week in short character description (i.e. SUN)JJulian Day (number of days since January 1 4713 BC, where January 1 4713BC is 1 in Oracle)HHHour number of the day (1-12)HH12Hour number of the day (1-12)HH24Hour number of the day with 24Hours notation (1-24)AMAM or PMPMAM or PMMINumber of minutes (i.e. 59)SSNumber of seconds (i.e. 59)SSSSNumber of seconds this day.12

TO DATE FunctionConverts a character string to a date format.TO DATE (char[,'fmt'])TO NUMBER and TO DATE FunctionsYou may want to convert a character string to either a number or a date. Toaccomplish this task, you use the TO NUMBER or TO DATE functions. Theformat model you choose will be based on the previously demonstrated formatelements.ExampleDisplay the names and hire dates of all the employees who joined on February22. 1981.SELECT ename, hiredateFROMempWHERE hiredate TO DATE ( 'Şubat 22, 1981', 'Month dd, YYYY');E AMEWARDHIREDATE22/02/198113

Elements of Date Format ModelTime efements format the time portion of the date.HH24:MI:SSAM15:45:32 PMAdd character strings by enclosing them in double quotationmarks.DD "Of " MONTH12 of OCTOBERNumber suffixes spell out numbers.DdspthfourteenthTime FormatsElementDescriptionAM or PMMeridian indicatorA.M. or P.M.Meridian indicator with periodsHH or HH12 or HH24Hour of day or hour (1 - 12) or hour (0-23)MIMinute (0-59)SSSecond (0-59)SSSSSSeconds past midnight (0-86399)14

Oracle to date functionThe oracle TO DATE function converts a string in a specified format to anOracle date format.Syntaxto date('formatted string');returns a date using the default oracle dateformatto date('formattedreturns a date using the format stringstring','format string');specifiedto date('formattedreturns a date using the format stringstring','format string','nlsspecified and using the specified NLSdescription');settingsExamples:SELECT to date( '01-OCA-2007 ')FROM dual;TARIH01/01/2007SELECT to date( '01-01-2007' , 'DD-MM-YYYY') TarihFROM dual;TARIH01/01/2007SELECT to date( '31-12-2006 23:34:59' , 'DD-MM-YYYY HH24:MI:SS ' ) Tarih15

FROM dual;TARIH31/12/2006SELECT to date('01-JAN-99' , 'DD-MON-YY' , 'nls date language American') TarihFROM dual;TARIH01/01/2099SELECT to date('01-OCA-99' , 'DD-MON-YY' , 'nls date language Turkish') TarihFROM dual;TARIH01/01/2099SELECTto date ('2003/07/09' ,FROM dual ;'yyyy/dd/mm'TO DATE('207/09/2003to date (string1, [format mask], [nls language])to date ('2003/07/09', 'yyyy/mm/dd');/*July 9, 2003*/to date ('070903', 'MMDDYY');/*July 9, 2003*/to date ('20020315', 'yyyymmdd');/*Mar 15, 2002*/16)

SELECTto date ('93/07/09' , 'yy/dd/mm' )FROM dual ;TARĐH07/09/209317

Oracle date formatWith 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: 24/03/2006 14:36:4318

Oracle date formatSELECT TO DATE('Aralık 15, 1998' , 'Month DD, YYYY') TarihFROM dual ;TARĐH15/12/1998SELECT TO DATE('Aralık 15, 98' , 'Month DD, YY') TarihFROM dual ;TARĐH15/12/2098SELECT TO DATE('Aralık 15, 98' , 'Month DD, RR') TarihFROM dual ;TARĐH15/12/1998SELECT TO DATE('Aralık 15, 07' , 'Month DD, RR') TarihFROM dual ;TARĐH15/12/200719

RR Date FormatCurrent YearSpecified DateRR FormatYY Format1995 19952001 200127-OCT-95 27-OCT-1727-OCT-17 27-OCT-951995 20172017 19951995 19172017 2095If the specified two-digit year is:0-49If two digitsof thecurrentyear are:550-99The return date is in thecurrent centuryThe return date is in thecentury before the current öne0-99 The return date is in thecentury after the current öneThe return date is in the currentcentury3-49The RR Date Format ElementThe RR date format is similar to the YY element, but it allows you to specifydiffereut centuries You can use the RR date format element instead of YY, sothat the century of the return value according to the specified two-digits of thecurrent year. The table on the slide summarizes the behavior of the RR element.Current yearGiven DateInterpreted (RR)Interpreted 27-OCT-172017201720

SYSDATE CURRENT DATEand DUALSYSDATE is a date function that returns the current date and time. It iscustomary to select SYSDATE from a dummy table called DUAL.select to char(sysdate, 'DD-Mon-YYYY HH24:MI:SS') as "Current Time"from dual;Current Time18-Mar-2007 17:37:10select to char(sysdate, 'Dy DD-Mon-YYYY HH24:MI:SS') as "Current Time"from dual;Current TimePAZ 18-MAR-2007 05:46:41SELECT TO CHAR(CURRENT DATE, 'Dy DD-MON-YYYY HH:MI:SS')FROM dual;TO CHAR(CURRE T DATE,'DD-MO -YYYYHH:MI:SS')PAZ 18-MAR-2007 05:46:4121

ExampleDisplay the date of the next Friday that is six months from the hiredate. Theresultant date should appearas Friday, March 12th 1982. Order the results byhiredate.SELECTTO CHAR (NEXT DAY (ADD MONTHS (hiredate, 6), 'CUMA'), 'fmDay, Month ddth, YYYY' )"Next 6 Months Review"FROM empORDER BY hiredate ;ext 6 Months ReviewCuma, Haziran 19th, 1981Cuma, Ağustos 21st, 1981Cuma, Ağustos 28th, 1981Cuma, Ekim 9th, 1981Cuma, Kasım 6th, 1981Cuma, Aralık 11th, 1981Cuma, Mart 12th, 1982Cuma, Nisan 2nd, 1982Cuma, Mayıs 21st, 1982Cuma, Haziran 4th, 1982Cuma, Haziran 4th, 1982Cuma, Temmuz 30th, 1982Cuma, Haziran 10th, 1983Cuma, Temmuz 15th, 198314 rows selected.22

Practice 31.Write a query to display the current date. Label the column Date.2.2 Display the employee number, name, salary, and salary increase by15% expressed as a whole number. Label the column New Salary. Saveyour SQL statement to a file named p3q2.sql.3.Run your query in the file p3q2.sql.23