Datastage Date format

[kkmreddy]

Hello everyone ..

I have an issue with Date format - My input is Date datatype "18-Aug-2012" and my ouput should be "08/18/2012" wite date datatype . So i used StringToDate(Datetostring(date,"%mm/%dd/%yyyy),"%mm/%dd/%yyyy) ) .. The first convertion gave me output as 08/12/2012 (datetostring) and second convertion gave me output as 2012-08-12 but not 08/12/2012 ...

I search other post then i changed my project level default to %mm/%dd/%yyyy but then the output of date field is coming as 2012-08-12 .

Please provideme with your suggestions whatelse can i do to get my outpute date as 08/12/2012

[ArndW]

The most important question here is if you have "date" datatypes or "string" datatypes. "Date" has no format, it is an internal binary value and only gets a format when converted to a string, i.e. when writing to a sequential file or displayed to the screen.

Assuming you have a string:

To convert from "18-Aug-2012" to "08/18/2012" you need to perform 2 steps. First, you need to convert the input string into a date using the correct mask:

Code: Select all

StringToDate(In.Col,"%dd-%mmmm-%yyyy")

Then you want to convert this date into a string with the appropriate mask

Code: Select all

DateToString(<code from above>,"%mm/%dd/%yyyy")

I split the two functions apart for legibility.

[kkmreddy]

Thank you so much for the response Andrw ...

Thing is i have date datatype field coming in so its Date- 18-Aug-2012 .
So i used to converstion DatetoString(mydate,"%mm/%dd/%yyyy) then i coverted it back to Date filed so i used StringtoDate(abovecode,"%mm/%dd/%yyyy) .. Ideally this should give me output as 08/18/2012 . But its giving me as 2012-08-18

I changed the default format to %mm%dd%yyyy .. but still same ..

[jwiles]

What is your date source and what is your intended target for the date after you convert it? Database, sequential file, something else?

The DataStage date datatype uses an internal binary (i.e. non-character) format to store the date (FYI Time and Timestamps work similarly). It's only when it's converted to a character string (such as for ViewData, Peek, Sequential File, Char, VarChar, etc.) that you see the yyyymmdd, yy-mmm-dd, yyyy/mm/dd type of formats. ViewData and Peek will use whatever the system default format is to present the date for your viewing. Other applications (such as databases) may also use internal formats for date datatypes and may show them to you in a different format when viewing (dd-mmm-yy is a typical default for Oracle.

If the datatype within your job is Date and your target is a sequential file or some other Char or VarChar data type, then you probably just need to use a DateToString(your_date,"%mm/%dd/%yyyy") to convert it to a string of the proper format.

StringToDate(string,format) converts from a character string of the given format to the internal date representation. DateToString(date,format) converts from the internal date representation to a character string of the given format.

Using StringToDate() to "reformat" a date datatype column is a waste of time because it's NOT stored internally as a string!

Regards,

[ray.wurlod]

I changed the default format to %mm%dd%yyyy .. but still same ..

When you convert your input string, you must have "-" characters in the format string, as Arnd indicated. There is no indication that you have done this.