Problem with date formats

I have a visual basic program that is communicating with a SQL Server database. The program is required to store details on staff members including 3 different date fields.

The problem that I am having is that when I input a date in to the database it keeps switching the day and month, going from dd/mm/yyyy to mm/dd/yyyy. I have checked with our IT section and they have said that the server is on the correct date time format for this not too happen.

Additionally the database doesn't seem to be able to take any date where either of the first 2 fields figures are largeer than 12, for example:

13/01/1971 - will cause an error
01/13/1971 - will cause an error also.

12/01/1971 - No error but then when reading from database comes up as 01/12/1971
01/12/1971 - No error but comes back as 12/01/1971

The error that I am getting has to do with the "conversion of a char data type in to a date time format results in an out of range value."

I can successfully convert the string value to a date format in VB but when it comes time to run the SQL statement it produces an error.

Can someone help please!!!!

Have you tried using dashes? ie 01-01-2004

Also, try passing now() into the DB

Just a tip but any time I send dates to SQL I use the format dd MMM yyyy.

This way there can be no confusion as to what part of the date is the day, month and year.

When returning dates in queries you can use CONVERT(DATETIME, <myfield>, 106) to get the date in the above format

Or use CONVERT(DATETIME, <myfield>, 113) to get both the date and time.

And finally use SET DATEFORMAT dmy to tell the server that you wish to enter your dates as dd/MM/yyyy.