Sybase to Oracle Date/time conversion

[deployDS]

Hi all,
My job is to dump the data from sybase database to Oracle database. I'm using the following conversion in transformer before loading it to the oracle DB.

[b]Source Date format: 2003-10-16 00:00:00.000
conversion used: Substrings(sbo_data.col_name,1,19)[/b]

I don't have any problem with the conversion.
My question is: are there any problems using the above mentioned substring function to convert the date into oracle format? I know that the following function too work:

[b]Oconv(Iconv(sbo_data.col_name[1,10], 'D-YMD[4,2,2]'),'D-YMD[4,2,2]')[/b]


For both conversions, While writing the records to the DB, oracle uses the [b]To_Date[/b] function by default in the generated insert sql.
Which is the best approach for the conversion?

[chulett]

What you've done is perfectly fine as long as your select ensures it always matches that format with two digit (zero filled) months and days. That is the timestamp format you want for generated Oracle sql and since you are already in that format, with the exception of the milliseconds, best to just chop off what you want and go.

Personally, I prefer the substring operators, the [] notation:

Code: Select all

sbo_data.col_name[1,19]

Don't recall exactly what, but think there is something about the substrings function that made me not want to use it, something Ray mentions on occassion.

Of course, Left() can be used here as well. Save the "Conv Brothers" for when you really need them, for example when the incoming date could be in multiple formats.

[deployDS]

Thanks for the explanation Craig. But, I ended up using the "Conv" functions as i'm still a developer (and there are several forces that dictates what I'm doing and sometimes even how I'm doing )

I'm marking the topic as Resolved (I'm not sure if this has to be marked as Resolved or Workaround)

But, I still have one question. As I mentioned in my first post, the example date format was as seen from the "View Data" of the SybaseOC stage. Can some one help me know if that is the internal format that the sybase stores the date time value or I am lucky enough that taking the substring worked for me?

[chulett]

Been a long time since I've touched Sybase or that stage, so don't really know the answer or what's going on under the covers there. However, I will say that most (if not all) databases will store date/timestamp values in an 'internal' numeric format with all of the formatting - the 'external' version - being generated only when the data is selected for consumption elsewhere. And that conversion either happens under direct control of the sql used - say by a TO_CHAR() in Oracle, for example, or based on the default date mask in place in the database.

My personal preference is to force the conversion using whatever functions are available so that I "know" what the results will always be, regardless of any differences between environments or versions or settings. Nothing like relying on a default and then having the default changed by the DBA one day and seeing everything break. Or having it work "fine" in dev/test and seeing it break in production.