Import txt data to SQL

[topci]

Hi,
I have a little stupid problem. I need transfer data from txt file (using visual basic application) direct to SQL table. The Date field in TXT table is in yymmdd format (for example: 051103). When I write into SQL data over ADO and convert date field into next format: '11/03/2005', I get in Sql table format 11-03-2005 (month: 03, day: 11, year: 2005). And this is wrong. With same logic (and same version) is the date format for 051129 correct.

Any ideas, what can be wrong?

 

[vongrunt]

Yup.

How things work:

If date string literal has no separators (typically /, - or .), server always expects ISO-like format ([yy]yymmdd). One format = there is no chance to miss, as long as you keep it that way.

With some separators in string, interpretation depends on server/session settings. See SET DATEFORMAT and SET LANGUAGE in Books online for more details.

In this case server/session are probably set to dmy format while you are sending dates in mdy. Months and days get swapped, and in cases when day is greater than 12 a runtime error will happen.

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)