Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Datetime format

Author  Topic 

dr223
Constraint Violating Yak Guru

444 Posts

Posted - 2010-10-26 : 09:15:30
Hi,

How can I know the settings of the datetime of sql server 2005...

How can I also know the regional settings?

Many thanks

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-10-26 : 10:01:47
In most cases I think you don't need to know that...

Here is some useful info:
http://www.karaszi.com/SQLServer/info_datetime.asp


No, you're never too old to Yak'n'Roll if you're too young to die.
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2010-10-26 : 10:54:23
The following DBCC command will show you the settings for your connection.

The actual values are determined by the default language of the login, but can be modified at any time after login.

Language, dateformat, and datefirst determine various aspects of datetime settings.


dbcc useroptions with no_infomsgs 

Results:
Set Option       	Value
----------------------- -------------
textsize 64512
language us_english
dateformat mdy
datefirst 7
quoted_identifier SET
arithabort SET
ansi_null_dflt_on SET
ansi_defaults SET
ansi_warnings SET
ansi_padding SET
ansi_nulls SET
concat_null_yields_null SET







CODO ERGO SUM
Go to Top of Page

dr223
Constraint Violating Yak Guru

444 Posts

Posted - 2010-10-26 : 11:05:23

Woow really helpful indeed.

Now, I have the following;

language: us_english
dateformat: mdy
datefirst: 7

I want to change this to

language: British
dateformat: dmy
datefirst: 1

Please help...

thanks
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2010-10-26 : 13:44:02
You can change the initial setting by changing the default language for each login to British English.

You can change them at run-time using the SET command.



CODO ERGO SUM
Go to Top of Page

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-10-27 : 02:19:20

set dateformat dmy
--your statements

Also make sure to read this
http://beyondrelational.com/blogs/madhivanan/archive/2010/06/03/understanding-datetime-column-part-ii.aspx

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page
   

- Advertisement -