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
  • PowerCenter 10.4.0
  • All Products

Table of Contents

Search

  1. Preface
  2. The Transformation Language
  3. Constants
  4. Operators
  5. Variables
  6. Dates
  7. Functions
  8. Creating Custom Functions
  9. Custom Function API Reference

Transformation Language Reference

Transformation Language Reference

Back Next

TRUNC (Dates)

TRUNC (Dates)

Truncates dates to a specific year, month, day, hour, minute, second, millisecond, or microsecond. You can also use TRUNC to truncate numbers.
You can truncate the following date parts:
  • Year.
     If you truncate the year portion of the date, the function returns Jan 1 of the input year with the time set to 00:00:00.000000000. For example, the following expression returns 1/1/1997 00:00:00.000000000: 
    TRUNC(12/1/1997 3:10:15, 'YY')
  • Month.
     If you truncate the month portion of a date, the function returns the first day of the month with the time set to 00:00:00.000000000. For example, the following expression returns 4/1/1997 00:00:00.000000000: 
    TRUNC(4/15/1997 12:15:00, 'MM')
  • Day.
     If you truncate the day portion of a date, the function returns the date with the time set to 00:00:00.000000000. For example, the following expression returns 6/13/1997 00:00:00.000000000: 
    TRUNC(6/13/1997 2:30:45, 'DD')
  • Hour.
     If you truncate the hour portion of a date, the function returns the date with the minutes, seconds, and subseconds set to 0. For example, the following expression returns 4/1/1997 11:00:00.000000000: 
    TRUNC(4/1/1997 11:29:35, 'HH')
  • Minute.
     If you truncate the minute portion of a date, the function returns the date with the seconds and subseconds set to 0. For example, the following expression returns 5/22/1997 10:15:00.000000000: 
    TRUNC(5/22/1997 10:15:29, 'MI')
  • Second.
     If you truncate the second portion of a date, the function returns the date with the milliseconds set to 0. For example, the following expression returns 5/22/1997 10:15:29.000000000: 
    TRUNC(5/22/1997 10:15:29.135, 'SS')
  • Millisecond.
     If you truncate the millisecond portion of a date, the function returns the date with the microseconds set to 0. For example, the following expression returns 5/22/1997 10:15:30.135000000: 
    TRUNC(5/22/1997 10:15:30.135235, 'MS')
  • Microsecond.
     If you truncate the microsecond portion of a date, the function returns the date with the nanoseconds set to 0. For example, the following expression returns 5/22/1997 10:15:30.135235000: 
    TRUNC(5/22/1997 10:15:29.135235478, 'US')

Syntax

TRUNC( 
date
 [,
format
] )
The following table describes the arguments for this command:
Argument
Required/
Optional
Description
date
Required
Date/Time datatype. The date values you want to truncate. You can enter any valid transformation expression that evaluates to a date.
format
Optional
Enter a valid format string. The format string is not case sensitive. If you omit the format string, the function truncates the time portion of the date, setting it to 00:00:00.000000000.

Return Value

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

Examples

The following expressions truncate the year portion of dates in the DATE_SHIPPED port: 
TRUNC( DATE_SHIPPED, 'Y' )
TRUNC( DATE_SHIPPED, 'YY' )
TRUNC( DATE_SHIPPED, 'YYY' )
TRUNC( DATE_SHIPPED, 'YYYY' )
DATE_SHIPPED
RETURN VALUE
 
Jan 15 1998 2:10:30AM
Jan 1 1998 12:00:00.000000000
Apr 19 1998 1:31:20PM
Jan 1 1998 12:00:00.000000000
Jun 20 1998 3:50:04AM
Jan 1 1998 12:00:00.000000000
Dec 20 1998 3:29:55PM
Jan 1 1998 12:00:00.000000000
NULL
NULL
The following expressions truncate the month portion of each date in the DATE_SHIPPED port: 
TRUNC( DATE_SHIPPED, 'MM' )
TRUNC( DATE_SHIPPED, 'MON' )
TRUNC( DATE_SHIPPED, 'MONTH' )
DATE_SHIPPED
RETURN VALUE
 
Jan 15 1998 2:10:30AM
Jan 1 1998 12:00:00.000000000AM
Apr 19 1998 1:31:20PM
Apr 1 1998 12:00:00.000000000AM
Jun 20 1998 3:50:04AM
Jun 1 1998 12:00:00.000000000AM
Dec 20 1998 3:29:55PM
Dec 1 1998 12:00:00.000000000AM
NULL
NULL
The following expressions truncate the day portion of each date in the DATE_SHIPPED port: 
TRUNC( DATE_SHIPPED, 'D' )
TRUNC( DATE_SHIPPED, 'DD' )
TRUNC( DATE_SHIPPED, 'DDD' )
TRUNC( DATE_SHIPPED, 'DY' )
TRUNC( DATE_SHIPPED, 'DAY' )
DATE_SHIPPED
RETURN VALUE
 
Jan 15 1998 2:10:30AM
Jan 15 1998 12:00:00.000000000AM
Apr 19 1998 1:31:20PM
Apr 19 1998 12:00:00.000000000AM
Jun 20 1998 3:50:04AM
Jun 20 1998 12:00:00.000000000AM
Dec 20 1998 3:29:55PM
Dec 20 1998 12:00:00.000000000AM
Dec 31 1998 11:59:59PM
Dec 31 1998 12:00:00.000000000AM
NULL
NULL
The following expressions truncate the hour portion of each date in the DATE_SHIPPED port: 
TRUNC( DATE_SHIPPED, 'HH' )
TRUNC( DATE_SHIPPED, 'HH12' )
TRUNC( DATE_SHIPPED, 'HH24' )
DATE_SHIPPED
RETURN VALUE
 
Jan 15 1998 2:10:31AM
Jan 15 1998 2:00:00.000000000AM
Apr 19 1998 1:31:20PM
Apr 19 1998 1:00:00.000000000PM
Jun 20 1998 3:50:04AM
Jun 20 1998 3:00:00.000000000AM
Dec 20 1998 3:29:55PM
Dec 20 1998 3:00:00.000000000PM
Dec 31 1998 11:59:59PM
Dec 31 1998 11:00:00.000000000AM
NULL
NULL
The following expression truncates the minute portion of each date in the DATE_SHIPPED port: 
TRUNC( DATE_SHIPPED, 'MI' )
DATE_SHIPPED
RETURN VALUE
 
Jan 15 1998 2:10:30AM
Jan 15 1998 2:10:00.000000000AM
Apr 19 1998 1:31:20PM
Apr 19 1998 1:31:00.000000000PM
Jun 20 1998 3:50:04AM
Jun 20 1998 3:50:00.000000000AM
Dec 20 1998 3:29:55PM
Dec 20 1998 3:29:00.000000000PM
Dec 31 1998 11:59:59PM
Dec 31 1998 11:59:00.000000000PM
NULL
NULL
0 COMMENTS
We’d like to hear from you!