Change default date time format on a single database in SQL Server

Question

I need to change the date format from US  mm dd YYYY  to UK  dd mm YYYY  on a single database on a SQL server machine   How can this be done   I ve seen statements that do this for the whole system  and ones that do it for the session  but I can t change the code now as it will have to go through QA again  so I need a quick fix to change the date time format   Update  I realize that the date time has nothing to do with how SQL Server stores the data  but it does have a lot to do with how it parses queries   I m chucking raw data from an XML file into a database  The dates in the XML file are in UK date format

User · Answer

In order to avoid dealing with these very boring issues  I advise you to always parse your data with the standard and unique SQL ISO date format which is YYYY-MM-DD  Your queries will then work internationally  no matter what the date parameters are on your main server or on the querying clients  where local date settings might be different than main server settings

User · Answer

If this really is a QA issue and you can t change the code   Setup a new server instance on the machine and setup the language as  British English

User · Answer

You can only change the language on the whole server  not individual databases   However if you need to support the UK you can run the following command before all inputs and outputs   set language  british english    Or if you are having issues entering datatimes from your application you might want to consider a universal input type such as     1-Dec-2008

User · Answer

In order to avoid dealing with these very boring issues  I advise you to always parse your data with the standard and unique SQL ISO date format which is YYYY-MM-DD  Your queries will then work internationally  no matter what the date parameters are on your main server or on the querying clients  where local date settings might be different than main server settings

User · Answer

You do realize that format has nothing to do with how SQL Server stores datetime  right   You can use set dateformat for each session  There is no setting for database only   If you use parameters for data insert or update or where filtering you won t have any problems with that

User · Answer

In order to avoid dealing with these very boring issues  I advise you to always parse your data with the standard and unique SQL ISO date format which is YYYY-MM-DD  Your queries will then work internationally  no matter what the date parameters are on your main server or on the querying clients  where local date settings might be different than main server settings

User · Answer

You can only change the language on the whole server  not individual databases   However if you need to support the UK you can run the following command before all inputs and outputs   set language  british english    Or if you are having issues entering datatimes from your application you might want to consider a universal input type such as     1-Dec-2008

User · Answer

Although you can not set the default date format for a single database  you can change the default language for a login which is used to access this database   ALTER LOGIN your login WITH DEFAULT LANGUAGE British   In some cases it helps

User · Answer

You do realize that format has nothing to do with how SQL Server stores datetime  right   You can use set dateformat for each session  There is no setting for database only   If you use parameters for data insert or update or where filtering you won t have any problems with that

User · Answer

You could use SET DATEFORMAT  like in this example  declare  dates table  orig varchar 50   parsed datetime   SET DATEFORMAT ydm   insert into  dates select  2008-09-01   2008-09-01   SET DATEFORMAT ymd  insert into  dates select  2008-09-01   2008-09-01   select   from  dates   You would need to specify the dateformat in the code when you parse your XML data

User · Answer

In order to avoid dealing with these very boring issues  I advise you to always parse your data with the standard and unique SQL ISO date format which is YYYY-MM-DD  Your queries will then work internationally  no matter what the date parameters are on your main server or on the querying clients  where local date settings might be different than main server settings

User · Answer

If this really is a QA issue and you can t change the code   Setup a new server instance on the machine and setup the language as  British English

User · Answer

You could use SET DATEFORMAT  like in this example  declare  dates table  orig varchar 50   parsed datetime   SET DATEFORMAT ydm   insert into  dates select  2008-09-01   2008-09-01   SET DATEFORMAT ymd  insert into  dates select  2008-09-01   2008-09-01   select   from  dates   You would need to specify the dateformat in the code when you parse your XML data

User · Answer

You do realize that format has nothing to do with how SQL Server stores datetime  right   You can use set dateformat for each session  There is no setting for database only   If you use parameters for data insert or update or where filtering you won t have any problems with that

User · Answer

For SQL Server 2008 run   EXEC sp defaultlanguage  username    british

User · Answer

If this really is a QA issue and you can t change the code   Setup a new server instance on the machine and setup the language as  British English

User · Answer

For SQL Server 2008 run   EXEC sp defaultlanguage  username    british

User · Answer

Use   select   from mytest EXEC sp rename  mytest eid    id    COLUMN  alter table mytest add id int not null identity 1 1  update mytset set eid id ALTER TABLE mytest DROP COLUMN eid  ALTER TABLE  dbo   yourtablename  ADD DEFAULT  getdate    FOR  yourfieldname    It s working 100

User · Answer

Use   select   from mytest EXEC sp rename  mytest eid    id    COLUMN  alter table mytest add id int not null identity 1 1  update mytset set eid id ALTER TABLE mytest DROP COLUMN eid  ALTER TABLE  dbo   yourtablename  ADD DEFAULT  getdate    FOR  yourfieldname    It s working 100

User · Answer

You could use SET DATEFORMAT  like in this example  declare  dates table  orig varchar 50   parsed datetime   SET DATEFORMAT ydm   insert into  dates select  2008-09-01   2008-09-01   SET DATEFORMAT ymd  insert into  dates select  2008-09-01   2008-09-01   select   from  dates   You would need to specify the dateformat in the code when you parse your XML data

User · Answer

You could use SET DATEFORMAT  like in this example  declare  dates table  orig varchar 50   parsed datetime   SET DATEFORMAT ydm   insert into  dates select  2008-09-01   2008-09-01   SET DATEFORMAT ymd  insert into  dates select  2008-09-01   2008-09-01   select   from  dates   You would need to specify the dateformat in the code when you parse your XML data

User · Answer

Use   select   from mytest EXEC sp rename  mytest eid    id    COLUMN  alter table mytest add id int not null identity 1 1  update mytset set eid id ALTER TABLE mytest DROP COLUMN eid  ALTER TABLE  dbo   yourtablename  ADD DEFAULT  getdate    FOR  yourfieldname    It s working 100

User · Answer

If this really is a QA issue and you can t change the code   Setup a new server instance on the machine and setup the language as  British English

User · Answer

Although you can not set the default date format for a single database  you can change the default language for a login which is used to access this database   ALTER LOGIN your login WITH DEFAULT LANGUAGE British   In some cases it helps

User · Answer

You do realize that format has nothing to do with how SQL Server stores datetime  right   You can use set dateformat for each session  There is no setting for database only   If you use parameters for data insert or update or where filtering you won t have any problems with that

User · Answer

You can only change the language on the whole server  not individual databases   However if you need to support the UK you can run the following command before all inputs and outputs   set language  british english    Or if you are having issues entering datatimes from your application you might want to consider a universal input type such as     1-Dec-2008

User · Answer

Use   select   from mytest EXEC sp rename  mytest eid    id    COLUMN  alter table mytest add id int not null identity 1 1  update mytset set eid id ALTER TABLE mytest DROP COLUMN eid  ALTER TABLE  dbo   yourtablename  ADD DEFAULT  getdate    FOR  yourfieldname    It s working 100

User · Answer

You can only change the language on the whole server  not individual databases   However if you need to support the UK you can run the following command before all inputs and outputs   set language  british english    Or if you are having issues entering datatimes from your application you might want to consider a universal input type such as     1-Dec-2008

[sql-server] Change default date time format on a single database in SQL Server

Examples related to sql-server

Examples related to datetime