This error has quite been difficult to resolve it.
“Conversion failed when converting date and/or time from character string”
I see there is only load_date and load_time fields that are available in table. They work fine though.
Trust me, it is too tedious.
DBS-070401: |Data flow DF_LedgerSumFactNewGLPivot_SAP|Loader FirstorDelta_FirstLoad_LEDGER_SUMMARY_STAGE_VR
ODBC data source error message for operation : <[Microsoft][SQL Server Native Client 10.0][SQL
Server]Conversion failed when converting date and/or time from character string.
Based on the error it sounds like there is an implicit conversion from string to date and it’s happening on the job server. That’s strange. What is the data type of those fields in the Dataflow?
There is one only date field called LOAD_DATE=to_date(to_char(sysdate(),‘yyyy.mm.dd HH24:mi:ss’),‘yyyy.mm.dd HH24:mi:ss’).
The database used is SQL Server 2008 R2.
Assign NULL to all possible DATETIME fiield to just confirm that the issue is really with a DATEIME column/DATETIME Conversion
Don’t double convert for dates. Instead I would suggest to create a Local/Global Variable of DATETIME datatype and directly assign SYSDATE() to it and use the variable in the dataflow…
Do you see a optimized SQL ? Try taking the optimized SQL and run in the source system
I absolutely agree with points #2 and #3 that Ganesh posted above. Using a constant for the load date/time can actually improve your execution time.
Your answer quoted here isn’t the answer I want to see. When you look in the database with a tool such a MS SQL Server Management Studio what is the exact data type that is listed. Data Services often changes the data type in the meta data to something more common. For example, in Oracle a NUMBER(4) comes into Data Services as INT in the meta data.
The error is definitely indicating a problem with the target table. Somehow the Dataflow is sending a string to the target table.
It’s possible that there is a date format mismatch between the database server and the client (where the job server runs). Assuming the job server is submitting the data as a string 12-05-2013, meaning Dec 5, 2013, the database server may see it as May 12, 2013. That works, even though you get the wrong date stored. An error would show up should the client use a date format of DD-MM-YYYY and process a date of 31-12-2013.
Thanks Ganesh and Jim for such good and quick response.
Regarding #1, when I set null for all date fields, the job now runs without any issue.But no values are loaded.The data type in the target table is datetime.
This implies that there is data type mismatch between BODS and SQL Server database table.
Is it something to do with settings in SQL Server database or can we apply specific date format implicit conversion in mapping.
No values loaded in sense, no records are loaded or you got NULL for the fields you meant ? (Of course if you map NULL then nothing is going to be loaded…no wonder…) The theme is to isolate where the issue is.
My best bet is the double conversion stuff that I have seen is many many many places. That was a standard because of a old (actually say age old bug like 2009 and before) that existed for 11.x versions
Try using the variable as I had suggested…Or you had tried that already?
(BOB member since 2007-09-12)
(BOB member since 2007-09-12)