dateformat is ignored

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!

newtophp2000@yahoo.com 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
--

David Portas  wrote:
> 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.


I read from the file line by line and parse the line to extract the
fields.  I then use the bound variables in the prepared Insert
statement to add it to the database.  I wanted to change the DATEFORMAT
configuration as it seemed to be such a straight answer.  I guess I
could use the CONVERT function if it is fast enough.  I can do some
tests to see how it performs.

I am curius: is there a particular reason to shy away from setting
DATEFORMAT?  Is it not reliable as implemented or something else?

Thanks a lot!


> --
> David Portas
> SQL Server MVP
> --

Hi

If you are parsing a string then you constructing the date in CCYYMMDD
format will be a safe option.

John

<newtophp2000@yahoo.com> wrote in message
news:1135777730.480129.321010@z14g2000cwz.googlegroups.com...
> David Portas wrote: 
>
>
> I read from the file line by line and parse the line to extract the
> fields.  I then use the bound variables in the prepared Insert
> statement to add it to the database.  I wanted to change the DATEFORMAT
> configuration as it seemed to be such a straight answer.  I guess I
> could use the CONVERT function if it is fast enough.  I can do some
> tests to see how it performs.
>
> I am curius: is there a particular reason to shy away from setting
> DATEFORMAT?  Is it not reliable as implemented or something else?
>
> Thanks a lot!
>
> 
>

David and John,

Thank you very much for your input.  I am now using the techniques that
you suggested and it works great!