Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Categories

Date Conversion Error

tyaramistyaramis Member Posts: 18
Hi All,
We have a problem in Sql Server 7.0 while transferring data from text file to into a smalldatetime field of table. The data in text file has "dd/mm/yyyy" format. When we try to insert data to date field by using bulk copy method, we are getting this error.

"Server: Msg 4864, Level 16, State 1, Line 1
Bulk insert data conversion error (type mismatch) for row 182015, column 1 (TARIH)."

After the row '182015', the date data in text file is begining like this;
"13/03/2002".
Then, the problem has occured!!!!

P.S: We checked the reginol settings, the date setting of system is also "dd/mm/yyyy"

Does anyone has idea about this problem?

Thank u for your interest...

Tolga



Comments

  • japlhajaplha Member Posts: 12
    It doesn't matter to SQL Server if you change the regional settings.
    The regional settings change the way dates are displayed.
    The way dates are interpreted (for SQL Server) is based on which version of SQL server you installed.

    For SQL server you must specify the correct date format in order for it to store a date.
    My guess is the dd/mm/yyyy format is invalid for your version of SQL Server. SQL Server see's the date 13/03/2002 as mm/dd/yyyy. Which is an invalid date.
    If you can, change your text file so the dates are formatted as mm/dd/yyyy.
    If that's not possible try using DTS and specify a transformation to format the date.
    Or you could insert the data into a table (tbl_table1) with a varchar datatype specified for the date. Then insert the data from tbl_table1 into the final table.
    You can specify the date format using the convert function.
    Something like this:
    insert into tbl_yourtable (col1, col2, date)
    select col1, col2, convert(smalldatetime,[date],103)
    from tbl_table1

    The 103 specifies the dd/mm/yyyy format.

    I hope this helps.

    : Hi All,
    : We have a problem in Sql Server 7.0 while transferring data from text file to into a smalldatetime field of table. The data in text file has "dd/mm/yyyy" format. When we try to insert data to date field by using bulk copy method, we are getting this error.
    :
    : "Server: Msg 4864, Level 16, State 1, Line 1
    : Bulk insert data conversion error (type mismatch) for row 182015, column 1 (TARIH)."
    :
    : After the row '182015', the date data in text file is begining like this;
    : "13/03/2002".
    : Then, the problem has occured!!!!
    :
    : P.S: We checked the reginol settings, the date setting of system is also "dd/mm/yyyy"
    :
    : Does anyone has idea about this problem?
    :
    : Thank u for your interest...
    :
    : Tolga
    :
    :
    :
    :

  • Justin BibJustin Bib USAMember Posts: 0

    _______ < http://forcoder.org /> free video tutorials and ebooks about [ C# PHP Python Assembly PL/SQL Ruby Visual Basic .NET Swift Go Java Scratch Delphi Visual Basic C R MATLAB Objective-C Perl JavaScript C++ D Scheme Lua Prolog Fortran Lisp FoxPro SAS ABAP Scala Bash Awk Apex Clojure Rust Crystal Dart ML Hack Kotlin Erlang COBOL VBScript Ada Transact-SQL F# Julia LabVIEW Alice Logo ] ______________

Sign In or Register to comment.