problem with date format

[meet_deb85]

Hi

I am getting date for one column in the format m/d/yyyy

I am taking it using a sequential file where i have taken it as varchar.

But i have to put it into a DB2 table which supports date in the format

yyyy-mm-dd. Can someone plz suggest me the conversion technique.....

[ray.wurlod]

Firstly is the data type of your input field Date or some kind of string?
Secondly, is it always a single digit for month and day, as the format string you've specified suggests?
Only then can you start worrying about manipulating formats. Essentially you will need to convert to a string so you can achieve a consistent format (with leading zeroes where necessary) then convert that to a Date.

[vanathy]

I have a doubt... how could be the date and month in the single character.... or the format is mm/dd/yyyy

[ray.wurlod]

January through September, and the first nine days of each month.

I've just noticed that the job is designated as a server job. In that case the solution is easy.

Code: Select all

Oconv(Iconv(InLink.TheDate,"DMDY"),"D-YMD[4,2,2]")

[meet_deb85]

ok i am correcting myself

its mm/dd/yyyy

[ag_ram]

Hi ,

Create 3 stage variables using the functions as mentioned below..

Str('0',2-Len(Field(linkname.columnname,'/',1))):Field(linkname.columnname,'/',1) --- stage variable 1

column name -- column in which u have the date in the varchar format

Str('0',2-Len(Field(linkname.columnname,'/',2))):Field(linkname.columnname,'/',2) --- stage variable 2

stage variable1:'/':stage variable 2:'/':Field(linkname.columnname,'/',3) -- stage variable 3

then in derivation section use the function

StringToDate(stage variable 3,"%mm/%dd/%yyyy") --- OutputDate(column name)


OutputDate column will give u date in format yyyy-mm-dd

U can also validate the conversion happened by using the following function

IsValid("date", StringToDate(stage variable 3,"%mm/%dd/%yyyy"))..

Hope this will help you.

Hi

I am getting date for one column in the format m/d/yyyy

I am taking it using a sequential file where i have taken it as varchar.

But i have to put it into a DB2 table which supports date in the format

yyyy-mm-dd. Can someone plz suggest me the conversion technique.....

[ray.wurlod]

What if the incoming field is a Date data type?

Incidentally, it's not U (one of our occasional posters) who is having this issue, it's meet_deb85. The correct spelling of the second person personal pronoun is "you".

[DSguru2B]

DB2 requires date in internal format. Just do an ICONV() on the field and feed it to the table.

[ray.wurlod]

Moderaotr: please move to server forum