SQL Server 2k problem

[jam1et]

I'm importing some access tables to SQL server 2k using the import data wizard (not DTS). The original access tables contain british formatted dates, the local machine is set to british language, and sql server's default language is british. However, when I import the tables the dates (dd/mm/yy) keep getting converted to american format (mm/dd/yy). Anyone know how to get around this?

Ta

[m12_nathan]

write a script to change it once the data has been moved?

DTS?

[LaurenceFrost]

If you mean that they are stored in that format then there is no way around this.

AFAIK the dates are stored yyyy-mm-dd in the database (or at least, this is how they are presented in raw table form).

The easy way to test if they have been stored incorrectly during the conversion is to do the following:

SELECT CONVERT(varchar, column, 106) FROM table WHERE id=1;

You will need to alter the column and table names to reflect a table in your database. You can then compare this field to the matching one in the Access table.

This will present the date as "dd mmm yyyy".

[JamieT]

I think it has something to do with the collation settings of SQL server. I didnt change them from the default on installation which I guess is set to US English. The only problem is that to change them nom I have to rebuild the Master Db and I dont fancy doing that. I cant convert the data once its in becuase its failing on the import. I think the only way is use DTS (problem with that is that I have loads of tables to import which have loads of dates in them). unless someone can convince me that rebuilding the master db isnt too bad....