Date Function

[ntr]

HI,
I need help i struck at this point.
My input :
Columns
account|effective_date
112|3/21/2011
The records are with |delimeter
I am reading with sequential file all columns as varchar.

I want to convert 3/21/2011 as 03-21-2011
I am using string to date function as

StringToDate(DateToString(StringToDate(Right(Str('0',1):Field(DSLink2.Effective_date,"/",1,1),2):'/':Field(DSLink2.D_TRADE_DATE,"/",2,2),"%mm/%dd/%yyyy"),"%mm-%dd-%yyyy"),"%mm-%dd-%yyyy ")

but the out put is coming as 2011-21-03.

Can any one guide i need output as 03-21-2011

Thanks

[ShaneMuir]

What is your output metadata? Is it to be a date or a string? Is it to a DB or to a sequential file?

I ask because it looks like its converted to a date value, and it could merely be the tool that you are using to view that is interpreting the date in that format?

[ShaneMuir]

Also your code:

Code: Select all

StringToDate(DateToString(StringToDate(Right(Str('0',1):Field(DSLink2.Effective_date,"/",1,1),2):'/':Field(DSLink2.D_TRADE_DATE,"/",2,2),"%mm/%dd/%yyyy"),"%mm-%dd-%yyyy"),"%mm-%dd-%yyyy ") 

You should be able to just go

Code: Select all

StringToDate(DSLink2.Effective_date,"%mm/%dd/%yyyy")

Or something very similar. That should put it into a date format.

If you want to output the date as a string you would then take that value which is now a date value and transform it back to a string in the format you require

Code: Select all

DateToString(StringToDate(DSLink2.Effective_date,"%mm/%dd/%yyyy"),"%mm-%dd-%yyyy")

[ntr]

Hi,
my output is DB
Date is datatype

[ShaneMuir]

And are you viewing the value in the database? Then I would say that what ever tool you are using to view the field, interprets that database's internal representation of the date value in the format which you have seen.

Most database store a date value as an internal value which is just a number (please somebody correct me if I am wrong) and then appropriate programs will interpret that according to what we expect to see.

[ntr]

Hi,
For testing purpose i am taking sequential in place of DB.

I tryed using this function
StringToDate(DSLink2,Effective_Date,"%mm-%dd-%yyyy")

It was showing the follow warning not able to view the data

<Sequential_File_76,0> Data string '**********' does not match format '%yyyy-%mm-%dd': an integer was expected to match tag %yyyy.

[ShaneMuir]

Hi,
For testing purpose i am taking sequential in place of DB.

Ok so your initial derivation is turning your value into a date field. And you have specified the output as a date. However when writing to a sequential file its technically a string. So when writing the file datastage will implicitly convert the date value to a string based on the datastage default. This would most likely be why you are seeing the date as you do in the file.

To me it would seem that to change it for the purpose of testing would be redundant, and that the value should be correct when writing to a database.

As for the code
I tryed using this function

Code: Select all

I tryed using this function 
StringToDate(DSLink2,Effective_Date,"%mm-%dd-%yyyy") 

I think it should actually read

Code: Select all

StringToDate(DSLink2,Effective_Date,"%mm/%dd/%yyyy") 

Sorry.

[jwiles]

StringToDate(DSLink2.Effective_date,"%m/%d/%yyyy") should convert your source string to a date datatype. This assumes no leading zeros--or "variable-width"--for the month and day fields (see the Parallel Job Developer's Guide for Time and Date formats).

As long as your database column is also defined as a date datatype, you should be able to load directly to it after the above conversion. This has been discussed many times in the forum.

Regards,

[pandeesh]

it's better , first convert the 3/21/2011 to 03/21/2011 and then use

Code: Select all

StringToDate(inputlink,"%mm/%dd/%yyyy")

if you use %m alone,it will not work out when your month has 2 digits values.

[ntr]

Hi Thanks,

May i know how to add '0'.
If i add 0 in feature the date will come as 11/21/2011.

Then?

[pandeesh]

you can make use of Right().
If the day value has single digit , add 0 .

[ntr]

Hi Pandeeswaran,

Still know i am facing the same warning.

Can u give an example.

Appricate ti u r help

[pandeesh]

Code: Select all

StringToDate(Right(Str('0',1):Field(DSLink2.Effective_date,"/",1,1),2):Field(DSLink2.Effective_date,"/",2),"%dd/%mm/%yyyy")

please check this.I dont have access to datastage to test now.

[jwiles]

it's better , first convert the 3/21/2011 to 03/21/2011 and then use

Code: Select all

StringToDate(inputlink,"%mm/%dd/%yyyy")

if you use %m alne,it will not work out when your month has 2 digits values.

I'm sorry, but you are dead wrong with your answer. The code I provided works extremely well for a mixture of 1 and 2 digit values...%m is for variable-width numbers, as documented in the Parallel Job Developer's Guide which I alluded to in my earlier post. My test:

My logic:
date_date: StringToDate(DSLink4.date_string,"%m/%d/%yyyy")

date_date is a Date datatype, date_string is a generated VarChar.

My results:
Peek_1,1: date_date:2011-01-01 date_string:01/01/2011
Peek_1,1: date_date:2011-12-01 date_string:12/01/2011
Peek_1,1: date_date:2011-01-30 date_string:1/30/2011
Peek_1,1: date_date:2011-01-01 date_string:1/1/2011


The suggested code you gave, while it may have the same end results, is an inefficient method to use when the StringToDate() function performs that work intrinsically when given the correct format string.

Regards,