Export (0) Print
Expand All

Date and Time Formats

Fast parse supports the following formats for date and time data types.

The date data types that Integration Services provides are DT_DATE and DT_DBDATE. Fast parse supports both the DT_DATE and DT_DBDATE data types. For more information, see Integration Services Data Types.

Fast parse supports the following formats for date data:

  • Leading white spaces; for example, the value " 2- 3-2004" is valid.
  • ISO 8601 formats:

    Format Description

    YYYYMMDD

    YYYY-MM-DD

    Basic and extended formats for a four-digit year, a two-digit month, and a two-digit day. In the extended format, the date parts are separated by a hyphen (-).

    YYYYMM

    YYYY-MM

    Basic and extended reduced precision formats for a four-digit year and a two-digit month. In the extended format, the date parts are separated by a hyphen (-).

    YYYY

    Reduced precision format is a four-digit year.

    YYMM YY-MM

    Basic and extended truncated formats for a two-digit year and a two-digit month. In the extended format, the date parts are separated by a hyphen (-).

The following date formats are not supported:

  • Alphabetical month values. For example, the date format Oct-31-2003 is not valid.
  • Ambiguous formats such as DD-MM-YYYY and MM-DD-YYYY. For example, the dates 03-04-1995 and 04-03-1995 are not valid.
  • Basic and extended truncated formats for a four-digit calendar year and a three-digit day within a year, YYYYDDD and YYYY-DDD.
  • Basic and extended formats for a four-digit year, a two-digit number for the week of the year, and a one-digit number for the day of the week, YYYYWwwD and YYYY-Www-D
  • Basic and extended truncated formats for a year and week date are a four-digit year and a two-digit number for the week, YYYWww and YYYY-Www

The following formats are supported for the output of date data:

  • For the DT_DATE data type, the format is the computed value without white spaces.
  • Date values in truncated formats are padded. For example, YYYY becomes YYYY0101.

The time data type that Integration Services provides is DT_DBTIME. For more information, see Integration Services Data Types.

Fast parse supports the following formats for time data:

  • Leading white spaces. For example, the value " 10:24" is valid.
  • The 24-hour format. Fast parse does not support the AM and PM notation.
  • The following ISO 8601 time formats:

    Format Description

    hhmmss hh:mm:ss

    Basic and extended formats for a two-digit hour, a two-digit minute, and a two-digit second. In the extended format, the time parts are separated by a colon (:).

    hhmm hh:mm

    Basic and extended truncated format for a two-digit hour and a two-digit minute. In the extended format, the time parts are separated by a colon (:).

    hh

    Truncated format for a two-digit hour.

    00:00:00

    The format for midnight.

  • Time values that include a time zone. The uppercase Z indicates a time zone in the date value. For example, the value 19:20:49Z indicates that the time is represented in Coordinated Universal Time (UTC).
  • The following time values, which include a decimal fraction:

    Format Description

    HH.HHH

    The fraction .H indicates a fraction in hours. For example, the value 12.750 indicates 12:45.

    HHMM.MMM

    HH:MM.MMM

    The fraction .MMM indicates a fraction in minutes. For example, the value 1220.500 indicates 12:30:30.

    HHMMSS.SSS

    HH:MM:SS.SSS

    The fraction .SSS indicates a fraction in seconds. For example, the value 122040.250 indicates 12:20:40:15.

The following formats are supported for the output of time data:

  • The DT_DBTIME data type is in the OLE DB time format HHMMSS.SSS.
  • Time values in truncated formats are padded. For example, HH:MM becomes HH:MM:00:000.

The date/time data type that Integration Services provides is DT_DBTIMESTAMP. For more information, see Integration Services Data Types.

Fast parse supports the following formats for date/time data:

  • Leading white spaces. For example, the value " 2003-01-10T203910" is valid.
  • Combinations of valid date formats and valid time formats separated by an uppercase T. For example, YYMMDDThhmmss. Including a time format is not required. For example, 2003-10-14 is valid.
  • Coordinated Universal Time (UTC), a combination of a valid date format and a valid time format separated by an uppercase T and ending with an uppercase Z. For example, 2003-01-10T203910Z is a UTC date. The value of Z is 0.
  • Differences between local and UTC time. The difference is expressed in hours and minutes. When less precision is required, only hours are used. The local time is expressed the same as in a valid basic or extended format.

Fast parse does not support time intervals. For example, a time interval identified by a start and end date and time in the format YYYYMMDDThhmmss/YYYYMMDDThhmmss cannot be parsed.

The following formats are supported for the output of time/date data:

  • A DT_DBTIME data type in the OLE DB time format YYYYMMDDTHHMMSS.SSS.
  • Date/Time values in truncated formats are padded. For missing date and time parts, the following values are added:

    Date/Time part Padding

    Seconds

    Add 00.

    Minutes

    Add 00:00.

    Hour

    Add 00:00:00.

    Day

    Add 01 for the day of the month.

    Month

    Add 01 for the month of the year.

Was this page helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

ADD
Show:
© 2014 Microsoft