Insert from Oracle into SQL Server datetime field

Posted on 2013-09-20
Medium Priority
Last Modified: 2013-09-24
I have a web service that extracts data from an Oracle table and inserts into a SQL Server table. I'm getting the "String was not recognized as a valid datetime" error. Is there a way to get this to work? I'm using Visual Studio 2012, SQL Server 2008 R2 and Oracle 11g. Is there a fix I can use in the actual SQL statement that grabs the data? Thanks.
Question by:dodgerfan
LVL 16

Assisted Solution

DcpKing earned 668 total points
ID: 39510625
It sounds like you're not formatting the string correctly. In SQL Server there are a number of formats you can use that it will convert into date and time. By default you should try to format the incoming date like this:

1900-01-01 00:00:00

(YYYY-MM-DD hh:mm:ss)

Can you work out which records are causing the problem and show us the offending data?
How are you actually pushing the data into records? With a SQL statement in a language like VB.Net or C# ? If so, you might want to consider using a stored procedure in SQL Server instead, and coding it to check for good dates ... if it finds a problem it can divert the record to an errors table instead of your intended destination ...



(ref: here for date definition)
LVL 49

Assisted Solution

PortletPaul earned 664 total points
ID: 39510819
Could well be the ISO8601 format  (style number 126) being provided
maybe with with time zone Z (style number 127)


convert(datetime, your_data_here ,126)
convert(datetime, your_data_here ,127) -- if there is time zone info

CAST and CONVERT (Transact-SQL)

{+an edit}
LVL 13

Accepted Solution

jonnidip earned 668 total points
ID: 39514278
Once you get what the correct input format you have, you can use this method to convert the Varchar to a DateTime value:
public static DateTime GetDateTime_From_String(String string_date, String format)
    return DateTime.ParseExact(string_date.Trim(), format, CultureInfo.InvariantCulture, DateTimeStyles.None);

Open in new window

The usage is:
String dateFromOtherDB = "2013-09-23 11:16:00";
DateTime typedDateValue = GetDateTime_From_String(dateFromOtherDB, "yyyy-MM-dd HH:mm:ss");

Open in new window

Please take a minute to look at this article: http://www.experts-exchange.com/Programming/Theory/Software-Design/A_11953-Date-formats-are-for-humans-Machines-just-use-the-type.html


Author Closing Comment

ID: 39518616
All of this helped me find what I needed. Thanks.

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
High user turnover can cause old/redundant user data to consume valuable space. UserResourceCleanup was developed to address this by automatically deleting user folders when the user account is deleted.
The purpose of this video is to demonstrate how to set up the WordPress backend so that each page automatically generates a Mailchimp signup form in the sidebar. This will be demonstrated using a Windows 8 PC. Tools Used are Photoshop, Awesome…
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.
Suggested Courses

619 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question