setup date format sql server 2000

Does anyone know if its possible to configure sql server 2000 so it reads in the date in a different format. Right now, it takes the date in the following format month day year. I want it to take in day month year instead.

I am using asp.net and that is properly configured to give day month year, but then this date is sent to sql server, i get into problems

please help

Thanks

But does that not set the date only for that sql statement.

I was looking for something more permanent. for example set it to uk date once and thats its. after that it will understand uk dates

is that not possible?

Anyone know why this does not work?

INSERT INTO mytable (CONVERT(nvarchar(20), [date], 113) AS date) VALUES (30/04/2004) where [name] = 'jo'

i want to insert 30/04/2004 into the database but it ends up like this 04/30/2004.

I want it to keep its formatting as 30/04/2004

that doesn't work because it's invalid syntax (i'm suprised it runs at all)

when you say INSERT INTO tablename ( columns ) you can only have column names in the parentheses

and even if you were able to run it, 30/04/2004 is not a valid date string, it has to be '30/04/2004'

finally, no matter how you are able to get a date value into a table column, when you display it, it will display in the default format

so if you want a particular format, you can use CONVERT to display it

thanks, figured it out.

Hi Narmi2,

I've been dealing with this date formatting issue for some time in a web based document management system that I work on. I was just wondering if you could elaborate on the solution you came to for this problem.

Thanks in advance.

Hi!

Use the format 'dd-MMM-yyyy' always to form the DML statements. You wont face any problems in inserting or updating values in a date field. For e.g.:

To form the same statement in VB.NET:

and use it to execute.

Hope this will help.