Modify date that in string format to date format

[happyds]

Hello DS Experts! I'm trying to convert a date that is in a string datatype to a date datatype in a Modify stage, but am running into problems. The format of the string date value is different from the default format of "%yyyy-%mm-%dd", so I can't just use the "date_from_string(field_name)" specification.

Input string date value: 04/20/2007
Expected output value in date datatype: 04-20-2007

I tried using the following specification as specified in the Parallel Job Developer's Guide, but I keep receiving errors:
initial_date = date_from_string(initial_date) ["%mm-%dd-%yyyy"]

Please help me. Thank you.

Jessica

[DSguru2B]

You date is in the format mm/dd/yyyy and hence you need to give that format to the date_from_string() function. Then while loading it to a file you can specify change the default date format to mm-dd-yyyy.

[happyds]

Thanks for responding! One of the problems I am having is knowing how to correctly specify the date format within the function. I have tried all variations, but each time DS throws a parsing error. Here is an example of one my specifications.

The specification is what I have entered in the Modify stage: Initial_Date=date_from_string (Initial_Date) [%mm/%dd/%yyyy]

The error I receive is the following:
main_program: Error parsing modify adapter: Expected ';' or end, got: "["; input:
Initial_Date=date_from_string (Initial_Date) [%mm/%dd/%yyyy];

Any ideas on what is the correct format to specify for the date format?

Thanks again,
Jessica

[DSguru2B]

Your specification is wrong. Provide the following

Code: Select all

initial_date:date = date_from_string [%mm/%dd/%yyyy]  (initial_date) 

And if your loading to a flat file, in the sequential file stage, go to "Format" tab, under "Type Defaults", go to the "Date" folder and enter %mm-%dd-%yyyy for "Format String"

[ray.wurlod]

In case you missed it, the part in square brackets goes ahead of the part in parentheses. The part in square brackets is the format string, the part in parentheses is the input column name.

[happyds]

That worked! Thank you so much for your help!

Jessica