Communities
A collaborative platform to connect and grow with like-minded Informaticans across the globe
Product Communities
Connect and collaborate with Informatica experts and champions
Discussions
Have a question? Start a Discussion and get immediate answers you are looking for
User Groups
Customer-organized groups that meet online and in-person. Join today to network, share ideas, and get tips on how to get the most out of Informatica
Get Started
Community Guidelines
Knowledge Center
Troubleshooting documents, product guides, how to videos, best practices, and more
Knowledge Base
One-stop self-service portal for solutions, FAQs, Whitepapers, How Tos, Videos, and more
Support TV
Video channel for step-by-step instructions to use our products, best practices, troubleshooting tips, and much more
Documentation
Information library of the latest product documents
Velocity (Best Practices)
Best practices and use cases from the Implementation team
Learn
Rich resources to help you leverage full capabilities of our products
Trainings
Role-based training programs for the best ROI
Certifications
Get certified on Informatica products. Free, Foundation, or Professional
Product Learning Paths
Free and unlimited modules based on your expertise level and journey
Resources
Library of content to help you leverage the best of Informatica products
Tech Tuesdays Webinars
Most popular webinars on product architecture, best practices, and more
Product Availability Matrix
Product Availability Matrix statements of Informatica products
SupportFlash
Monthly support newsletter
Support Documents
Informatica Support Guide and Statements, Quick Start Guides, and Cloud Product Description Schedule
Product Lifecycle
End of Life statements of Informatica products
Ideas
Events
Change Request Tracking
Marketplace
English
  • Data Integration
  • All Products

Table of Contents

Search

  1. Preface
  2. Function reference
  3. Constants
  4. Operators
  5. Dates
  6. Functions
  7. System variables
  8. Datatype reference

Function Reference

Function Reference

Back Next

TO_CHAR (Dates)

TO_CHAR (Dates)

Converts dates to character strings. TO_CHAR also converts numeric values to strings. You can convert the date into any format using the TO_CHAR format strings.

Syntax

TO_CHAR( 
date
 [,
format
] )
Argument
Required/
Optional
Description
date
Required
Date/Time datatype. Passes the date values you want to convert to character strings. You can enter any valid expression.
format
Optional
Enter a valid TO_CHAR format string. The format string defines the format of the return value, not the format for the values in the date argument. If you omit the format string, the function returns a string based on the default date format of MM/DD/YYYY HH24:MI:SS.

Return Value

String.
NULL if a value passed to the function is NULL.

Example

The following expression converts the dates in the DATE_PROMISED column to text in the format MON DD YYYY: 
TO_CHAR( DATE_PROMISED, 'MON DD YYYY' )
DATE_PROMISED
RETURN VALUE
 
Apr 1 1998 12:00:10AM
'Apr 01 1998'
Feb 22 1998 01:31:10PM
'Feb 22 1998'
Oct 24 1998 02:12:30PM
'Oct 24 1998'
NULL
NULL
If you omit the
format_string
 argument, TO_CHAR returns a string in the default date format:
TO_CHAR( DATE_PROMISED )
DATE_PROMISED
RETURN VALUE
 
Apr 1 1998 12:00:10AM
'04/01/1997 00:00:01'
Feb 22 1998 01:31:10PM
'02/22/1997 13:31:10'
Oct 24 1998 02:12:30PM
'10/24/1997 14:12:30'
NULL
NULL
The following expressions return the day of the week for each date in a column:
TO_CHAR( DATE_PROMISED, 'D' )
DATE_PROMISED
RETURN VALUE
 
04-01-1997 12:00:10AM
'3'
02-22-1997 01:31:10PM
'7'
10-24-1997 02:12:30PM
'6'
NULL
NULL
 
TO_CHAR( DATE_PROMISED, 'DAY' )
DATE_PROMISED
RETURN VALUE
 
04-01-1997 12:00:10AM
'Tuesday'
02-22-1997 01:31:10PM
'Saturday'
10-24-1997 02:12:30PM
'Friday'
NULL
NULL
The following expression returns the day of the month for each date in a column:
TO_CHAR( DATE_PROMISED, 'DD' )
DATE_PROMISED
RETURN VALUE
 
04-01-1997 12:00:10AM
'01'
02-22-1997 01:31:10PM
'22'
10-24-1997 02:12:30PM
'24'
NULL
NULL
The following expression returns the day of the year for each date in a column:
TO_CHAR( DATE_PROMISED, 'DDD' )
DATE_PROMISED
RETURN VALUE
 
04-01-1997 12:00:10AM
'091'
02-22-1997 01:31:10PM
'053'
10-24-1997 02:12:30PM
'297'
NULL
NULL
The following expressions return the hour of the day for each date in a column:
TO_CHAR( DATE_PROMISED, 'HH' )
TO_CHAR( DATE_PROMISED, 'HH12' )
DATE_PROMISED
RETURN VALUE
 
04-01-1997 12:00:10AM
'12'
02-22-1997 01:31:10PM
'01'
10-24-1997 02:12:30PM
'02'
NULL
NULL
 
TO_CHAR( DATE_PROMISED, 'HH24' )
DATE_PROMISED
RETURN VALUE
 
04-01-1997 12:00:10AM
'00'
02-22-1997 01:31:10PM
'13'
10-24-1997 11:12:30PM
'23'
NULL
NULL
The following expression converts date values to MJD values expressed as strings:
TO_CHAR( SHIP_DATE, 'J')
SHIP_DATE
RETURN_VALUE
 
Dec 31 1999 03:59:59PM
2451544
Jan 1 1900 01:02:03AM
2415021
The following expression converts dates to strings in the format MM/DD/YY: 
TO_CHAR( SHIP_DATE, 'MM/DD/RR')
SHIP_DATE
RETURN_VALUE
 
12/31/1999 01:02:03AM
12/31/99
09/15/1996 03:59:59PM
09/15/96
05/17/2003 12:13:14AM
05/17/03
You can also use the format string SSSSS in a TO_CHAR expression. For example, the following expression converts the dates in the SHIP_DATE column to strings representing the total seconds since midnight: 
TO_CHAR( SHIP_DATE, 'SSSSS')
SHIP_DATE
RETURN_VALUE
 
12/31/1999 01:02:03AM
3783
09/15/1996 03:59:59PM
86399
In TO_CHAR expressions, the YY format string produces the same results as the RR format string.
The following expression converts dates to strings in the format MM/DD/YY:
TO_CHAR( SHIP_DATE, 'MM/DD/YY')
SHIP_DATE
RETURN_VALUE
 
12/31/1999 01:02:03AM
12/31/99
09/15/1996 03:59:59PM
09/15/96
05/17/2003 12:13:14AM
05/17/03
The following expression returns the week of the month for each date in a column:
TO_CHAR( DATE_PROMISED, 'W' )
DATE_PROMISED
RETURN VALUE
 
04-01-1997 12:00:10AM
'01'
02-22-1997 01:31:10AM
'04'
10-24-1997 02:12:30PM
'04'
NULL
NULL
The following expression returns the week of the year for each date in a column:
TO_CHAR( DATE_PROMISED, 'WW' )
DATE_PROMISED
RETURN VALUE
 
04-01-1997 12:00:10PM
'18'
02-22-1997 01:31:10AM
'08'
10-24-1997 02:12:30AM
'43'
NULL
NULL

Tip

You can combine TO_CHAR and TO_DATE to convert a numeric value for a month into the text value for a month using a function such as:
TO_CHAR( TO_DATE( numeric_month, 'MM' ), 'MONTH' )
0 COMMENTS
We’d like to hear from you!