Problem mapping SmallDateTime field to Timestamp
Moderators: chulett, rschirm, roy
-
- Premium Member
- Posts: 644
- Joined: Sat Aug 26, 2006 3:59 pm
- Location: Mclean, VA
Problem mapping SmallDateTime field to Timestamp
Hi ,
I'm facing problem in two scenarios
For a job whose src is SQL server and Target is Oracle I have a src field which is of the datatype SmallDatetime which is to be mapped to a target field of datatype Timestamp.
In Second Job whose src is Oracle and Target is SQL server have a src field which is of the datatype Timestamp which is to be mapped to a target field of datatype SmallDatetime.
Any Suggestions
Thanks InAdvance
I'm facing problem in two scenarios
For a job whose src is SQL server and Target is Oracle I have a src field which is of the datatype SmallDatetime which is to be mapped to a target field of datatype Timestamp.
In Second Job whose src is Oracle and Target is SQL server have a src field which is of the datatype Timestamp which is to be mapped to a target field of datatype SmallDatetime.
Any Suggestions
Thanks InAdvance
Attitude is everything....
-
- Charter Member
- Posts: 822
- Joined: Sat Sep 17, 2005 5:25 pm
- Location: USA
What are the requirements for a 'SmallDateTime'? Meaning what does the data look like or need to look like? I'm familiar with the Oracle bits, but not SQL Server.
Also take a moment and Search the forums for 'smalldatetime' as I'm sure this (in some context) has been discussed before.
Also take a moment and Search the forums for 'smalldatetime' as I'm sure this (in some context) has been discussed before.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
![Idea :idea:](./images/smilies/icon_idea.gif)
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
-
- Premium Member
- Posts: 644
- Joined: Sat Aug 26, 2006 3:59 pm
- Location: Mclean, VA
For first scenariochulett wrote:You might also want to specify what the actual problems are that you are having. Let us know what you've tried and if it "didn't work" tell us why. This helps us focus on solutions rather than shooting in the dark or repeating things you've already tried.
The date for the date column retrived via a DRS stage with Datatype Char and Data element SQL.SmallDateTime which is stored in an intermediate sequential file with the type Timestamp after performing few transformation it is written to the database.
the data stored in the sequential file is of the format "MM/DD/YYYY H:MI" The problem which I'm Facing here is if the date happen to be "01/06/2006 00:00" it is present in the file as "1/6/2006 0:00"
due to which i can't do substrings and retrive the date part only as the length would vary with the incoming date.
tried even Oconv(Iconv(DSLink75.ACCOUNTING_DT[1,10], "DYMD[2,2,2]"),"D-YMD[4,2,2]") it gives wrong answer
HOW DO I GET THE DATE AS "01/06/2006 00:00" In the Sequential file?
Attitude is everything....
-
- Premium Member
- Posts: 644
- Joined: Sat Aug 26, 2006 3:59 pm
- Location: Mclean, VA
I have created a dummy flat file where the data is of the format required and have used the following convert functn
Oconv(Iconv(DSLink75.ACCOUNTING_DT[1,10], "DYMD[2,2,2]"),"D-YMD[4,2,2]") and the applied a routine called to date which will convert the output into format "2005-06-29 00:00:00" checked the data by placing a sequential file it shows correct but while writing to the database i.e the Oracle table it seems to be writing with a value ACCOUNTING_DT = 22/0-/2-05 00:00:00 and throws an error " ORA-01843: not a valid month"
Please let me know what could be possibly wrong
Thanks In Advance
Oconv(Iconv(DSLink75.ACCOUNTING_DT[1,10], "DYMD[2,2,2]"),"D-YMD[4,2,2]") and the applied a routine called to date which will convert the output into format "2005-06-29 00:00:00" checked the data by placing a sequential file it shows correct but while writing to the database i.e the Oracle table it seems to be writing with a value ACCOUNTING_DT = 22/0-/2-05 00:00:00 and throws an error " ORA-01843: not a valid month"
Please let me know what could be possibly wrong
Thanks In Advance
Attitude is everything....
-
- Premium Member
- Posts: 644
- Joined: Sat Aug 26, 2006 3:59 pm
- Location: Mclean, VA
Well I have just a single row of data for testing purpose I analysed it by populating it into a sequential file the format which it gives is "1900-01-01 00:00:00" after all the transformations are done just before writing into the Oracle table. The job is running fine now without any errors or warnings but no data is populated in the table. Any clue why it must be so there is no user defined SQL attached. I'm using a DRS stage.DSguru2B wrote:That means it never got created properly. Go ahead and capture the rejects and analyze the data of the rejected records.
Thanks In Advance
Attitude is everything....
-
- Premium Member
- Posts: 644
- Joined: Sat Aug 26, 2006 3:59 pm
- Location: Mclean, VA
Thanks Alot DSguru for your suggestions.DSguru2B wrote:That means it never got created properly. Go ahead and capture the rejects and analyze the data of the rejected records.
the problem is resloved
For Scenario
the workaround solution was in one transformer I extract DSLink75.ACCOUNTING_DT[1,10] which would extract "1900-01-01" from the incoming date i.e. of type SmallDateTime i.e.
"1900-01-01 00:00"
then apply the ToDate routine in next transformer which would convert the date "1900-01-01" to "1900-01-01 00:00:00" and the data is populated to the tgt table
but here i found that if I perform this action in one transformer i.e. ToDate(DSLink75.ACCOUNTING_DT[1,10],'YYYY-MM-DD')
it sme how gives invalid month
if we retrive the data in the sequential file it gives the expected data but while writing to database it calcualtes wrongly
why so?? even if we use stage variable for the substring part and pass tht as the parameter to the ToDate routine yet the same issue.Any Clue why it must be like this?
For Scenario 2:
I merely applied substrings to the incoming data from Oracle database and it ran fine
Attitude is everything....