problem with date format
Moderators: chulett, rschirm, roy
-
- Premium Member
- Posts: 132
- Joined: Tue Sep 04, 2007 11:38 am
- Location: NOIDA
problem with date format
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.....
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.....
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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.
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.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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.
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]")
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
-
- Premium Member
- Posts: 132
- Joined: Tue Sep 04, 2007 11:38 am
- Location: NOIDA
Re: problem with date format
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.
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.
meet_deb85 wrote: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.....
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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".
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".
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact: