Importing Dates

[virtualranger]

I'm using the import wizard to import data from access tables. Dates in the source tables are in british format (dd/mm/yy), the local machine language is british, and sql server default language is british. However, when I import a the tables the british dates (dd/mm/yy) are converted to american format (mm/dd/yy). Anyone know how I stop it doing this?

Thanks,
Jamie

 

[mrdenny]

What collation type is the database set to?

Denny

--Anything is possible. All it takes is a little research. (Me)

 

[donutman]

Are the dates failing the conversion or are they displayed wrong?
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[virtualranger]

The dates are failing conversion. I didnt change collation type o install so I guess its set to US English, would this is whats causing it? What should I change it to and how easy is it to change the collation settings? dont I have to rebuild the master db or something?

Thanks,
Jamie

 

[mrdenny]

Yep, that's what is causing the display problem.

To change the default collation type of the server you will need to reinstall I believe. If you aren't going to create to many databases on the box I wouldn't bother.

You can use an ALTER DATABASE statement to change the collation of your database.

ALTER DATABASE {databasename}
COLLATE {CollationName}
/*
SELECT *
FROM ::fn_helpcollations()
will return a list of collations on the server.
*/

See ALTER DATABASE and COLLATE in BOL.

Denny

--Anything is possible. All it takes is a little research. (Me)