Oracle Timestamp to Sql Server Datetime conversion

[cwinans]

I'm importing data from an Oracle database into Sql Server. Everything is going smoothly except oracle table timestamp columns.

I've made the conversion from timestamp to char using TO_CHAR(field_name,'MM/DD/YYYY HH:MI:SS PM') which puts in in the sql server format.

Am I doing something wrong or is there another step or a better solution to this issue?

I hope this helped! ;-)
- Casey Winans

 

[JayKusch]

What error message(s) are you getting back?

Thanks

J. Kusch

 

[roccorocks]

Whats the error?

Rocco

 

[SQLBill]

Every time MM/DD/YYYY or DD/MM/YYYY is used it seems to cause problems. MS SQL Server can't figure out which format you are using. To solve the issue you can do one of two things...

1. Add the SET DATEFORMAT command to the beginning of your SQL Server script.

SET DATEFORMAT mdy

2. Use a format that isn't misunderstood:

yyyy-mm-dd hh:mi:ss (24hour format)

-SQLBill

 

[nimarii]

hello - I've run into the problem of needing to convert the Oracle timestamp field (from a text file), and since I'm a beginner with SQL Server, I have no idea where to begin.

Do you run a query to convert the data, or is it part of the DTS into sql server? i know, i'm totally lost...any help would be extremely helpful.

thanks!