How to convert Numeric fiels into Date Field

[rajua99]

Hello,

In your database some of our date fields are stored as numeric fields in this format YYYYMMDD.

When it comes to designing Frameworks they want all the date fields in the date format.

Is there a way to convert those numeric fields into date fields in this format MM/DD/YYYY.

Thanks,
Raju

[tjohnson3050]

_make_timestamp (
cast(substring(cast([DATE_FIELD],char(8)),1,4),INTEGER),
cast(substring(cast([DATE_FIELD],char(8)),5,2),INTEGER),
cast(substring(cast([DATE_FIELD],char(8)),7,2),INTEGER))

[rajua99]

Thanks Johnson. I just want the date not the time at the end. Is there another function that i can use just to display date instead of date and time

[Lynn]

cast([DateNumber],DATE)

or use whatever database functions you have available for conversion.

[rajua99]

Thanks Lynn

[the6campbells]

cast([DateNumber],DATE)

or use whatever database functions you have available for conversion.

The ISO SQL standard defines what source data type (SD) can be cast to what target data type (TD). Some RDBMS vendor may provide non-ISO CAST or another type conversion function which allows an SD/TD combo that is not in the standard. Similarly, they may allow STRING representations of a value to be converted to a TD where the string is not in the form required by the ISO SQL standard.

Casting of a the numeric types (precise/imprecise) to DATE is not a valid SD/TD combination.

[Lynn]

Oh those cheeky RDBMS Vendors and their extenstions beyond ANSI/ISO standards! Wouldn't life be dull without them!!

;)

[MFGF]

Would you, by any chance, be referring to the RDBMS vendor of DB2? The name escapes me just now, but I have a nagging feeling it's a 3-letter acronym :)