Re: dateformat is ignored

newtophp2000@xxxxxxxxx wrote:

> Hello,
>
> I receive a file containing some character fields along with a date.
> The date values in the file are formatted as "dd/mm/yy", that is
> 2-digit day, 2-digit month, and 2-digit year. The separator could be
> slash or a dash ("-"). The file is in a proprietary format, and bcp is
> not an option.
>
> So, I decided to load the file using a prepared statement. I open a
> cursor with an INSERT statement, read from the file, parse out values,
> and put it in the database using the cursor. All is OK; except that
> the date values are mangled. This is despite the fact that I am issuing
> a "set dateformat dmy" before running the INSERT statement.
>
> It seems that the "set dateformat dmy" is not being accepted, or it is
> being ignored. I set it at the beginning right after opening a
> connection to the database. From what I understand, it should work.
> Am I doing something wrong? Any suggestions on how to get this to
> work?
>
> Thanks!

You say BCP isn't an option but you didn't explain what other method
you are using to read the file or why a cursor is necessary. Don't rely
on SET DATEFORMAT. Use the CONVERT function with the style parameter to
specify the exact format. Looks like style 3 or 103 is what you need.

--
David Portas
SQL Server MVP
--

.

Relevant Pages

  • Re: Cursors and DTS
    ... Fixed the problem, my own fault, it was a problem with a SET DATEFORMAT ... statement which I had placed after the cursor had been declared. ... Placing it before the declaration of the cursor fixed the problem ... and the Stored Procedure runs fine if I call it via Query ...
    (microsoft.public.sqlserver.dts)
  • RE: Sql Server Date problems
    ... "SET DATEFORMAT dmy" should make it so that you don't have to use an ... unambiguous date format in the SELECT statement. ... is overriding the SET DATEFORMAT. ... > AMB ...
    (microsoft.public.sqlserver.programming)