Using the TO_CHAR Function with Dates

TO_CHAR(date, 'format_model')

The format model:
– Must be enclosed by single quotation marks
– Is case-sensitive
– Can include any valid date format element
– Has an fm element to remove padded blanks or suppress leading zeros
– Is separated from the date value by a comma

Example:
SELECT empno, TO_CHAR(hiredate, 'MM/YY') as Month_Hired FROM emp

Elements of the Date Format Model :
Time elements format the time portion of the date:
HH24:MI:SS AM 15:45:32 PM

Add character strings by enclosing them in double quotation marks:
DD "of" MONTH 12 of OCTOBER

Number suffixes spell out numbers: (Not Yet Implemented)
ddspth fourteenth

Example: SELECT empno, TO_CHAR(hiredate, 'ddspth') Month_Hired FROM emp;

Elements of the Date Format Model :

• Use the formats that are listed in the following tables to display time information and literals and to change numerals to spelled numbers
• SELECT empno, TO_CHAR(hiredate, 'fmDD Month YYYY') As Month_Hired FROM emp;
• The SQL statement in the slide displays the last names and hire dates for all the employees. The hire date appears as 17 December 1980.
  

Conversion Functions

Conversion Functions :

• In some cases, the PostgreSQL server uses data of one data type where it expects data of a different data type. When this happens, the PostgreSQL server can automatically convert the data to the expected data type. This data type conversion can be done implicitly by the PostgreSQL server or explicitly by the user
• Conversion functions convert a value from one data type to another

• Implicit data type conversions work according to the rules that are explained in the next two slides

Implicit Data Type Conversion :

• The assignment succeeds if the PostgreSQL server can convert the data type of the value used in the assignment to that of the assignment target

• example, the expression hiredate > '01-JAN-90' results in the implicit conversion from the string '01-JAN-90' to a date

Explicit Data Type Conversion :

• Explicit data type conversions are done by using the conversion functions