SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Datetime format
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

dr223
Constraint Violating Yak Guru

417 Posts

Posted - 10/26/2010 :  09:15:30  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

Germany
8765 Posts

Posted - 10/26/2010 :  10:01:47  Show Profile  Visit webfred's Homepage  Reply with Quote
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)

USA
7020 Posts

Posted - 10/26/2010 :  10:54:23  Show Profile  Reply with Quote
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

417 Posts

Posted - 10/26/2010 :  11:05:23  Show Profile  Reply with Quote

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)

USA
7020 Posts

Posted - 10/26/2010 :  13:44:02  Show Profile  Reply with Quote
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

India
22765 Posts

Posted - 10/27/2010 :  02:19:20  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote

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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000