Date Function
Moderators: chulett, rschirm, roy
Date Function
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
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
Also your code:
You should be able to just go
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
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 ")
Code: Select all
StringToDate(DSLink2.Effective_date,"%mm/%dd/%yyyy")
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")
Last edited by ShaneMuir on Thu Nov 17, 2011 10:41 am, edited 2 times in total.
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.
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.
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.
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.
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.ntr wrote:Hi,
For testing purpose i am taking sequential in place of DB.
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")
Code: Select all
StringToDate(DSLink2,Effective_Date,"%mm/%dd/%yyyy")
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,
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,
- james wiles
All generalizations are false, including this one - Mark Twain.
All generalizations are false, including this one - Mark Twain.
it's better , first convert the 3/21/2011 to 03/21/2011 and then use
if you use %m alone,it will not work out when your month has 2 digits values.
Code: Select all
StringToDate(inputlink,"%mm/%dd/%yyyy")
Last edited by pandeesh on Fri Nov 18, 2011 1:11 am, edited 1 time in total.
pandeeswaran
Code: Select all
StringToDate(Right(Str('0',1):Field(DSLink2.Effective_date,"/",1,1),2):Field(DSLink2.Effective_date,"/",2),"%dd/%mm/%yyyy")
pandeeswaran
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:pandeesh wrote:it's better , first convert the 3/21/2011 to 03/21/2011 and then useif you use %m alne,it will not work out when your month has 2 digits values.Code: Select all
StringToDate(inputlink,"%mm/%dd/%yyyy")
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,
- james wiles
All generalizations are false, including this one - Mark Twain.
All generalizations are false, including this one - Mark Twain.