getting error on 00000000 while doing stringtodate
Moderators: chulett, rschirm, roy
getting error on 00000000 while doing stringtodate
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
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
-
- Premium Member
- Posts: 353
- Joined: Mon Jan 17, 2011 5:03 am
- Location: Mumbai, India
Use
Code: Select all
If DSLink77.ANLA_AIBDT = '00000000' Then setnull() Else StringToDate(DSLink77.ANLA_AIBDT,"%yyyy%mm%dd")
Thanx and Regards,
ETL User
ETL User
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.![Wink :wink:](./images/smilies/icon_wink.gif)
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.
![Wink :wink:](./images/smilies/icon_wink.gif)
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
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')
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')
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.
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.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Year 0000 doesn't exist either.ArndW wrote: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.
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.