getting error on 00000000 while doing stringtodate

[times29]

HI,
i am getting below error while doing
StringToDate(DSLink77.AAA,"%yyyy-%mm-%dd ")

Error: Data string '00000000' does not match format '%yyyy-%mm-%dd ': an integer was expected to match tag %dd.

Issue is some data is good(20110302) and some is not (00000000) and we need to convert this from varchar to date

But again 00000000 we can make it null as this is not useful for business

[GJ_Stage]

Hi,

Apply condition if string is 00000000 then set as null else you can use tringToDate(DSLink77.AAA,"%yyyy-%mm-%dd ")

[times29]

getting warning now

If DSLink77.ANLA_AIBDT = '00000000' Then setnull() Else StringToDate(DSLink77.ANLA_AIBDT,"%yyyy-%mm-%dd")

TFM_ANLA,1: Data string '20091231' does not match format '%yyyy-%mm-%dd': an integer was expected to match tag %dd.

[chulett]

Your format string needs to match the data coming in. Do you have dashes in your data string?

[times29]

Data comes in as 20091231

[chandra.shekhar@tcs.com]

Use

Code: Select all

If DSLink77.ANLA_AIBDT = '00000000' Then setnull() Else StringToDate(DSLink77.ANLA_AIBDT,"%yyyy%mm%dd") 

[chulett]

I know, that's right there in your error message. You were meant to look at the question, answer "no" in your mind and then realize that you needed to fix the format you are using in the function call.

[chulett]

Or you could just wait for someone to come along and hand you the answer on a silver platter. I believe in stimulating critical thinking and learning... teach a man to fish and all that rather than just handing him one.

[times29]

Thanks for fishing comments
i did try below and it worked but when i query database data is coming like 09-12-31
If DSLink77.ANLA_AIBDT = '00000000' Then setnull() Else StringToDate(DSLink77.ANLA_AIBDT,"%yyyy%mm%dd")

[ArndW]

I can't check but seem to recall having to use '0001-01-01' as low-date in DataStage in a project. Months 0 and Day 0 don't exist in any case, so '0000000' is not a legal date in any case.

[chulett]

That's all on your select query, assuming your target column is a DATE in the database. If you want it to come out in a particular format, you'd need to control the string conversion that is automatically going on. Oracle? Use "TO_CHAR" and the appropriate format mask. Others would be very similar.

And yes, not only is a zero date "not good for business" it's not a valid date so you couldn't load it if you wanted to.

[times29]

ok i am updating in database now so i put below sql in after sql it runs in datbase but i get below error

The OCI function executeDirect returned status -1. Error code: 1861, Error message: ORA-01861: literal does not match format string. (CC_OraUtils::handleBeforeAfterSQL, file CC_OraUtils.cpp, line 4633)

UPDATE AA.Test
SET ACQUIS_DATE = TO_CHAR(ACQUIS_DATE,'RRRRMMDD')

[ShaneMuir]

Seriously?

What Craig meant, was that if your target DB has the field set as DATE then when you look at the value and see 09-12-32, that is just the representation of the date in which ever tool you are using.

If you would like to see it represented otherwise, either change the settings in the view properties of whatever tool you are using to display the way you wish.

Else as Craig suggests, when using your select query to view the data you can choose how you wish to display your data by casting it whatever format you wish. The data in the DB doesn't need changing it is correct.

[ray.wurlod]

I can't check but seem to recall having to use '0001-01-01' as low-date in DataStage in a project. Months 0 and Day 0 don't exist in any case, so '0000000' is not a legal date in any case.

Year 0000 doesn't exist either.

[chulett]

Yah, we skipped that one.