convert string to date

kris_r · Post by **kris_r** » Tue Oct 24, 2006 3:29 am

Hi,
my input is a string (123179) which needs to be converted to date type.
I tried using iconv and oconv but am still getting date conversion error.
Can someone help me with this..
Thanks,
Krishna.

kumar_s · Post by **kumar_s** » Tue Oct 24, 2006 3:33 am

Hi Krishna,
What is the format of the string and what is error you getting?
Is it in mmddyy format or some kind of julian date or something like date since..?

kris_r · Post by **kris_r** » Tue Oct 24, 2006 3:36 am

Hi,
the format is mmddyy.
The error I am getting is "Attempting to convert string value "12/31/79" to Date is Unsuccessful".

kumar_s · Post by **kumar_s** » Tue Oct 24, 2006 3:42 am

But it complains that the source is in MM/DD/YY format. What is your target stage?
Use

Code: Select all

 Iconv("Link.Date", "D2/")

to convert it into Date fromate from String. If it is a database stage, you can use To_date() that avaialble in database SQL statement.

kris_r · Post by **kris_r** » Tue Oct 24, 2006 3:45 am

But the problem is there is no / between the dates in the Input.
It is just MMDDYY. That is the reason i feel it is causing the error.

kumar_s · Post by **kumar_s** » Tue Oct 24, 2006 4:04 am

Have you tried Iconv("Link.Input", "DYMD[2,2,2]")?
Or you can even try the same previous option by concatinating "/" in middle. StageVariable = Link.Input[1,2]:"/":Link.Input[3,2]:"/":Link.Input[5,2], and use this stage variable for conversion.

kris_r · Post by **kris_r** » Tue Oct 24, 2006 4:34 am

Hi,
I tried creating a stage variable and concatenating '/'. But still I am getting the error message, "Value treated as NULL
Attempt to convert String value "" to Date type unsuccessful".

ArndW · Post by **ArndW** » Tue Oct 24, 2006 4:51 am

Kumar's suggestion of using a ICONV(In.Col,'D2MDY[2,2,2]') will work, as will manually creating a string with separators and then ICONving it.

DSguru2B · Post by **DSguru2B** » Tue Oct 24, 2006 6:36 am

ArndW is right. That option will work.
You can also try

Code: Select all

OCONV(ICONV(in.date,"DMDY[2,2,2]":@VM:"MCN"),"D/MDY[2,2,2]")

kris_r · Post by **kris_r** » Tue Oct 24, 2006 7:17 am

I think I am doing something seriously wrong...
It is still not working...

DSguru2B · Post by **DSguru2B** » Tue Oct 24, 2006 7:21 am

I am sure,
for testing purposes just create a simple routine with one arg and paste either mine or kumar's solution there. Test it there. You will see that they both work.
Try loading it to a sequential file. What database are you loading it too.
For most databases, the date format is YYYY-MM-DD
Try this

Code: Select all

OCONV(ICONV(in.date,"DMDY[2,2,2]":@VM:"MCN"),"D-YMD[4,2,2]")

ray.wurlod · Post by **ray.wurlod** » Tue Oct 24, 2006 9:27 am

All too complex. To convert to an internal date all that is needed is

Code: Select all

Iconv(InLink.TheDate, "DMDY")

nivas · Post by **nivas** » Tue Oct 24, 2006 12:43 pm

Hi Kris_r

Could you tell me what is your target database?
thanks
Nivas