Problem mapping SmallDateTime field to Timestamp

just4geeks · Post by **just4geeks** » Wed Mar 21, 2007 12:02 am

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

us1aslam1us · Post by **us1aslam1us** » Wed Mar 21, 2007 3:47 am

In the first case i think it will be a direct transformation but you need to check the target format. For the other one can use CAST function in the SQL to map the timestamp as smalldatetime .

chulett · Post by **chulett** » Wed Mar 21, 2007 6:50 am

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.

chulett · Post by **chulett** » Wed Mar 21, 2007 6:53 am

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.

just4geeks · Post by **just4geeks** » Wed Mar 21, 2007 8:06 am

chulett 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.

For first scenario
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?

just4geeks · Post by **just4geeks** » Wed Mar 21, 2007 9:12 am

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

ganesh123 · Post by **ganesh123** » Wed Mar 21, 2007 9:18 am

Can you post the generated SQL ? Check the TO_DATE() in it.

DSguru2B · Post by **DSguru2B** » Wed Mar 21, 2007 9:21 am

That means it never got created properly. Go ahead and capture the rejects and analyze the data of the rejected records.

just4geeks · Post by **just4geeks** » Wed Mar 21, 2007 11:49 am

DSguru2B wrote:That means it never got created properly. Go ahead and capture the rejects and analyze the data of the rejected records.

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.

Thanks In Advance

DSguru2B · Post by **DSguru2B** » Wed Mar 21, 2007 12:26 pm

That could only mean that your populating in one environment and looking at another? Or infact your updating instead of inserting a non-empty table.

just4geeks · Post by **just4geeks** » Wed Mar 21, 2007 8:28 pm

DSguru2B wrote:That means it never got created properly. Go ahead and capture the rejects and analyze the data of the rejected records.

Thanks Alot DSguru for your suggestions.

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