Dates stored as 00-00-00 in database
-
Hello all,
For a client we have to transfer an ERP database from an Oracle environment into SQL Server 2008.
We have been using the SQL Server Assistant Manager for Oracle for this and have been able to transfer most to all of the structure of the database.
When migrating the data however we ran into a problem, the SSMA gives us
Year, Month, and Day parameters describe an un-representable DateTime. on several of the tables.
We quickly went into the database and looked to one of the tables that triggered this error:
We had a quick look into the data using the simple select * statement
Our suspicion was raised when we saw several 00-00-00 values stored in date columns, since this not a valid date the SSMA can not convert them.
I tried to see if maybe I could do an update statement on the affected records but you cant do a where 'date_column'=00-00-00.
If anyone got an idea on how I could solve this or has encountered this before please share.
I thank you for your time.
-
Resender (8/10/2011)
Hello all,For a client we have to transfer an ERP database from an Oracle environment into SQL Server 2008.
We have been using the SQL Server Assistant Manager for Oracle for this and have been able to transfer most to all of the structure of the database.
When migrating the data however we ran into a problem, the SSMA gives us
Year, Month, and Day parameters describe an un-representable DateTime. on several of the tables.
We quickly went into the database and looked to one of the tables that triggered this error:
We had a quick look into the data using the simple select * statement
Our suspicion was raised when we saw several 00-00-00 values stored in date columns, since this not a valid date the SSMA can not convert them.
I tried to see if maybe I could do an update statement on the affected records but you cant do a where 'date_column'=00-00-00.
If anyone got an idea on how I could solve this or has encountered this before please share.
I thank you for your time
where to_char(date_column,'mm-dd-yy') = '00-00-00'
_____________________________________
Pablo (Paul) BerzukovAuthor of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice. -
I have created a post describing this problem in detail with solutions to work-around. Hopefully, it will help a few people trying to work with Oracle data in .Net.
https://sqljana.wordpress.com/2014/12/15/oracle-nasty-dates-finder/
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply