How to Convert date like or timestamp like text to a Timestamp value, which can then be converted to a Date value

Document ID : KB000055407
Last Modified Date : 14/02/2018
Show Technical Document Details

Operating Systems: All Windows

To_Date Function

(Conversion function) Converts date-like or timestamp-like text to a Timestamp value, which can then be converted to a Date value when necessary. If To_Date is unable to convert the value, it returns no result, which can be tested as a Null value.

To_Date is useful for converting numeric representations of dates and times, or unusually formatted dates and times, to F&T's Timestamp or Date data types with standard formats. It is also useful for returning the system date and time as a single Timestamp value and for validating user-entered dates.

Used in: Formulas, F&T SQL queries, report variables and expressions

Syntax

To_Date(text, date_format)

text [Text] The text to be converted.

date_format [Text] The date or timestamp format to be used to read the text value. Its format must match the text value's format. For valid date/time format characters, see the description of the data format symbols.

Examples

  • The expression below reads the numeric date string using a format that places the 4-digit year first, the 2-digit month next, and the 2-digit day last.

    To_Date('19910304', 'yyyymmdd')

    The result is 03/04/91 12:00 AM - assuming the F&T Timestamp format in effect is mm/dd/yy hh:mm AM. For text that does not include a time along with a date (as in this example), To_Date automatically inserts the initial time value formatted according to the time portion of the specified Timestamp format - or the default Timestamp format if the time portion is not specified. To convert the resulting Timestamp value to a date, use the Convert function:

    Convert(To_Date('19910304', 'yyyymmdd'), 'Date')

  • You can use quarter, day-of-year, week, and day-of-week format symbols to read and convert unusually formatted date strings - for example (assuming the date format in effect is mm/dd/yy):

    This expression returns 01/15/01 (quarter days are always 15th):

    Convert(To_Date('2001-Q1', 'yyyy-qq'), 'Date')

    This expression returns 12/31/01:

    Convert(To_Date('2001-365', 'yyyy-%jjj'), 'Date')

    This expression returns 01/07/01:

    Convert(To_Date('2001-02-1', 'yyyy-%ww-%dow'), 'Date')

  • To_Date is often useful with certain types of databases that store dates as numeric values. In such cases, you can query the database to bring the numeric dates into a CleverPath Forest & Trees view, then in another query view, use the F&T/W Active Views interface with the To_Date and To_Char functions to do the conversion:

    SELECT Convert(To_Date(To_Char("Integer Date Column", '#'), 'yyyymmdd'), 'Date')

    FROM DbView

    To_Char converts the Integer date to a Text value that To_Date can operate on, and its '#' parameter removes any thousands separator in the default F&T Integer format that would interfere with the date conversion. Note that the same expression would work if the dates are stored as Double (decimal) values.
    If you then want to extract the month from the converted date, you could use To_Char again on the outside of the expression:

    To_Char(Convert(To_Date(To_Char("Integer Date Column", '#'), 'yyyymmdd'), 'Date'), 'Month')

    Note that you can use the F&T/W Database Integrator interface, instead of the Active Views interface, to operate on the data directly from data source without bringing it into a view. The expression is the same, only it is applied to the column as it is named in the data source, with Integrator-specific prefixes and suffixes added to the table name (see Querying Data Sources with the Database Integrator):

    SELECT Convert(To_Date(To_Char(trans_date, '#') "Transaction Date", 'yyyymmdd'), 'Date')

    FROM DbView.imr_hist.table

System Timestamp

The following expression returns the current date and time as a single Timestamp value by applying the To_Date function to the results of the SysDate and SysTime functions:

To_Date(To_Char(SysDate) + ' ' + To_Char(SysTime),'mm/dd/yy hh:mm AM')

The example assumes that the default Date format is mm/dd/yy (or m/d/yy) and the default Time format is hh:mm AM (or h:mm AM).

Note: The Timestamp format specified for To_Date must be compatible with F&T's default Date and Time formats or you will get a Null result. If they are incompatible, either change To_Date's date_format parameter or use the To_Char function on SysDate and SysTime to reformat their results so they agree with the date_format parameter.

Related Documents: Please find more information regarding functions in the Forest & Trees help or contact CA support.

We take pride in our technical documents and are interested in your feedback. Please email your comments to us directly at: portalsdksupport@ca.com Be sure to include the document id in the message.