Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 582
  • Last Modified:

MS SQL DTS - Oracle Dates error out on import into MS SQL table

I am using MS SQL DTS to stage Oracle data to an MS SQL data store. MS SQL does not like the Oracle dates. Currently I have just changed the MS SQL data columns to a varchar field type. I would like instead to convert the date so that it can be imported into the datetime field. I have heard that there is an Oracle equivilant to the MS SQL CAST function. Anybody have any ideas on how to do this transformation? Just a note, it is not a driver issue, the same drivers have no problem with the Oracle dates when I link them in an access data base.
0
EVANFAGER
Asked:
EVANFAGER
  • 2
2 Solutions
 
JaffaKREECommented:
0
 
RaisinJCommented:
Try this...  You can also convert Date values in Oracle using the TO_CHAR, eg. TO_CHAR(@DATE, 'YYYY/MM/DD HH24:MM:SS'), function and TO_DATE, eg. to_date('1998/05/31:12:00:00AM', 'yyyy/mm/dd:hh:mi:ssam'), function.  The web page below goes over these functions and DATE formats you can use.  

Also, SQL Server has date limits where Oracle doesn't.  SQL Server cannot except dates less than January 1, 1753 and greater than December 31, 9999.  Oracle can, so make sure you don't accidently have any date values inside you Oracle DB which are beyond the range of SQL Server.

http://www-db.stanford.edu/~ullman/fcdb/oracle/or-time.html


0
 
RaisinJCommented:
I am all set thanks...  Hopefully my statements above gave some insite.
0

Featured Post

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now