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
  • Informatica Data Quality 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

Transformation Language Reference

Transformation Language Reference

Back Next

ADD_TO_DATE

ADD_TO_DATE

Adds a specified amount to one part of a datetime value, and returns a date in the same format as the date you pass to the function. ADD_TO_DATE accepts positive and negative integer values. Use ADD_TO_DATE to change the following parts of a date:
  • Year.
     Enter a positive or negative integer in the
    amount
     argument. Use any of the year format strings: Y, YY, YYY, or YYYY. The following expression adds 10 years to all dates in the SHIP_DATE port: 
    ADD_TO_DATE ( SHIP_DATE, 'YY', 10 )
  • Month.
     Enter a positive or negative integer in the
    amount
     argument. Use any of the month format strings: MM, MON, MONTH. The following expression subtracts 10 months from each date in the SHIP_DATE port: 
    ADD_TO_DATE( SHIP_DATE, 'MONTH', -10 )
  • Day.
     Enter a positive or negative integer in the
    amount
     argument. Use any of the day format strings: D, DD, DDD, DY, and DAY. The following expression adds 10 days to each date in the SHIP_DATE port: 
    ADD_TO_DATE( SHIP_DATE, 'DD', 10 )
  • Hour.
     Enter a positive or negative integer in the
    amount
     argument. Use any of the hour format strings: HH, HH12, HH24. The following expression adds 14 hours to each date in the SHIP_DATE port: 
    ADD_TO_DATE( SHIP_DATE, 'HH', 14 )
  • Minute.
     Enter a positive or negative integer in the
    amount
     argument. Use the MI format string to set the minute. The following expression adds 25 minutes to each date in the SHIP_DATE port: 
    ADD_TO_DATE( SHIP_DATE, 'MI', 25 )
  • Seconds.
     Enter a positive or negative integer in the
    amount
     argument. Use the SS format string to set the second. The following expression adds 59 seconds to each date in the SHIP_DATE port: 
    ADD_TO_DATE( SHIP_DATE, 'SS', 59 )
  • Milliseconds.
     Enter a positive or negative integer in the
    amount
     argument. Use the MS format string to set the milliseconds. The following expression adds 125 milliseconds to each date in the SHIP_DATE port: 
    ADD_TO_DATE( SHIP_DATE, 'MS', 125 )
  • Microseconds.
     Enter a positive or negative integer in the
    amount
     argument. Use the US format string to set the microseconds. The following expression adds 2,000 microseconds to each date in the SHIP_DATE port: 
    ADD_TO_DATE( SHIP_DATE, 'US', 2000 )
  • Nanoseconds.
     Enter a positive or negative integer in the
    amount
     argument. Use the NS format string to set the nanoseconds. The following expression adds 3,000,000 nanoseconds to each date in the SHIP_DATE port: 
    ADD_TO_DATE( SHIP_DATE, 'NS', 3000000 )

Syntax

ADD_TO_DATE( 
date
, 
format
, 
amount
 )
The following table describes the arguments for this command:
Argument
Required/
Optional
Description
date
Required
Date/Time datatype. Passes the values you want to change. You can enter any valid transformation expression.
format
Required
A format string specifying the portion of the date value you want to change. Enclose the format string within single quotation marks, for example, 'mm'. The format string is not case sensitive.
amount
Required
An integer value specifying the amount of years, months, days, hours, and so on by which you want to change the date value. You can enter any valid transformation expression that evaluates to an integer.

Return Value

Date in the same format as the date you pass to this function.
NULL if a null value is passed as an argument to the function.

Examples

The following expressions all add one month to each date in the DATE_SHIPPED port. If you pass a value that creates a day that does not exist in a particular month, the
Data Integration Service
 returns the last day of the month. For example, if you add one month to Jan 31 1998, the
Data Integration Service
 returns Feb 28 1998.
Also note, ADD_TO_DATE recognizes leap years and adds one month to Jan 29 2000: 
ADD_TO_DATE( DATE_SHIPPED, 'MM', 1 )
ADD_TO_DATE( DATE_SHIPPED, 'MON', 1 )
ADD_TO_DATE( DATE_SHIPPED, 'MONTH', 1 )
DATE_SHIPPED
RETURN VALUE
 
Jan 12 1998 12:00:30AM
Feb 12 1998 12:00:30AM
Jan 31 1998 6:24:45PM
Feb 28 1998 6:24:45PM
Jan 29 2000 5:32:12AM
Feb 29 2000 5:32:12AM
  (Leap Year)
Oct 9 1998 2:30:12PM
Nov 9 1998 2:30:12PM
NULL
NULL
The following expressions subtract 10 days from each date in the DATE_SHIPPED port: 
ADD_TO_DATE( DATE_SHIPPED, 'D', -10 )
ADD_TO_DATE( DATE_SHIPPED, 'DD', -10 )
ADD_TO_DATE( DATE_SHIPPED, 'DDD', -10 )
ADD_TO_DATE( DATE_SHIPPED, 'DY', -10 )
ADD_TO_DATE( DATE_SHIPPED, 'DAY', -10 )
DATE_SHIPPED
RETURN VALUE
 
Jan 1 1997 12:00:30AM
Dec 22 1996 12:00AM
Jan 31 1997 6:24:45PM
Jan 21 1997 6:24:45PM
Mar 9 1996 5:32:12AM
Feb 29 1996 5:32:12AM
  (Leap Year)
Oct 9 1997 2:30:12PM
Sep 30 1997 2:30:12PM
Mar 3 1996 5:12:20AM
Feb 22 1996 5:12:20AM
NULL
NULL
The following expressions subtract 15 hours from each date in the DATE_SHIPPED port: 
ADD_TO_DATE( DATE_SHIPPED, 'HH', -15 )
ADD_TO_DATE( DATE_SHIPPED, 'HH12', -15 )
ADD_TO_DATE( DATE_SHIPPED, 'HH24', -15 )
DATE_SHIPPED
RETURN VALUE
 
Jan 1 1997 12:00:30AM
Dec 31 1996 9:00:30AM
Jan 31 1997 6:24:45PM
Jan 31 1997 3:24:45AM
Oct 9 1997 2:30:12PM
Oct 8 1997 11:30:12PM
Mar 3 1996 5:12:20AM
Mar 2 1996 2:12:20PM
Mar 1 1996 5:32:12AM
Feb 29 1996 2:32:12PM
  (Leap Year)
NULL
NULL

Working with Dates

Use the following tips when working with ADD_TO_DATE:
  • You can add or subtract any part of the date by specifying a format string and making the
    amount
     argument a positive or negative integer.
  • If you pass a value that creates a day that does not exist in a particular month, the
    Data Integration Service
     returns the last day of the month. For example, if you add one month to Jan 31 1998, the
    Data Integration Service
     returns Feb 28 1998.
  • You can nest TRUNC and ROUND to manipulate dates.
  • You can nest TO_DATE to convert strings to dates.
  • ADD_TO_DATE changes only one portion of the date, which you specify. If you modify a date so that it changes from standard to daylight savings time, you need to change the hour portion of the date.
0 COMMENTS
We’d like to hear from you!