SQL Server Performance Forum – Threads Archive

datetime and smalldatetime format

The SQL have default format for the datetime and smalldatetime data type.
1)May I know where to check the default date format?
2)How to change the date format?
Thanks

Steve Kass has contributed the following notes <blockquote id="quote">quote:<hr height="1" noshade id="quote"> There is no international standard for representation of datetime values. ISO-8601 has a huge number of options, and SQL Server accepts at least a couple of the ISO-8601 alternatives. If you have timezone information in data and want one product that works with all back ends, then you’ve probably got trouble. Not all products support timezones, so your data will end up with different values on different products. If you want consistency, either eliminate or convert the timezone data in your front end, and send every back end you connect to datetime values as strings of the form ‘YYYYMMDD HH:MM<img src=’/community/emoticons/emotion-7.gif’ alt=’:S’ />S’ (seconds optional or with fractional seconds as well).<hr height="1" noshade id="quote"></blockquote id="quote"> Depending on the language set in your database (normally english) you have to format Dates accordingly. For the english language format this is: MM/DD/YYYY and KBA<a target="_blank" href=http://support.microsoft.com/default.aspx?scid=kb;en-us;173907>http://support.microsoft.com/default.aspx?scid=kb;en-us;173907</a> fyi. Satya SKJ Microsoft SQL Server MVP Writer, Contributing Editor & Moderator http://www.SQL-Server-Performance.Com</a> <center>This posting is provided AS IS with no rights for the sake of knowledge sharing. <hr noshade size="1">The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.</center>

Check also this one:http://www.karaszi.com/SQLServer/info_datetime.asp —
Frank Kalis
Moderator
Microsoft SQL Server MVP
Webmaster:http://www.insidesql.de

[<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] I thought you will refer your artcile too, but here it is:<a target="_blank" href=http://www.sql-server-performance.com/fk_datetime.asp>http://www.sql-server-performance.com/fk_datetime.asp</a> a good one to more in depth. Satya SKJ Microsoft SQL Server MVP Writer, Contributing Editor & Moderator http://www.SQL-Server-Performance.Com</a> <center>This posting is provided AS IS with no rights for the sake of knowledge sharing. <hr noshade size="1">The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.</center>

[<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] – Frank Kalis Moderator Microsoft SQL Server MVP Webmaster:<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a>

Just for clarification, do you think your article still applicable to SQL 2005 too [<img src=’/community/emoticons/emotion-5.gif’ alt=’

‘ />]. Satya SKJ Microsoft SQL Server MVP Writer, Contributing Editor & Moderator http://www.SQL-Server-Performance.Com</a> <center>This posting is provided AS IS with no rights for the sake of knowledge sharing. <hr noshade size="1">The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.</center>

Yes! But you’re right, I might need to have a look at it again. I’ll add this to my todo list: Item nnn. < Sigh > —
Frank Kalis
Moderator
Microsoft SQL Server MVP
Webmaster:http://www.insidesql.de

<blockquote id="quote">quote:<hr height="1" noshade id="quote">Originally posted by tboonleong The SQL have default format for the datetime and smalldatetime data type. 1)May I know where to check the default date format? 2)How to change the date format? Thanks <hr height="1" noshade id="quote"></blockquote id="quote"> 1 DBCC Useroptions 2 Dont worry. Let front end do that [<img src=’/community/emoticons/emotion-5.gif’ alt=’

‘ />] Madhivanan Failing to plan is Planning to fail

<blockquote id="quote">quote:<hr height="1" noshade id="quote">Originally posted by satya [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] Madhivanan Failing to plan is Planning to fail

[<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] That should be opposite when it comes to Knowledge sharing or educating other… Satya SKJ Microsoft SQL Server MVP Writer, Contributing Editor & Moderator http://www.SQL-Server-Performance.Com</a> <center>This posting is provided AS IS with no rights for the sake of knowledge sharing. <hr noshade size="1">Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.</center>

I still feel strange when refering to my own article. Feel this looks like a show-off. [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] – Frank Kalis Moderator Microsoft SQL Server MVP Webmaster:<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a>

<blockquote id="quote">quote:<hr height="1" noshade id="quote">Originally posted by FrankKalis I still feel strange when refering to my own article. Feel this looks like a show-off. [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] – Frank Kalis Moderator Microsoft SQL Server MVP Webmaster:<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a> <hr height="1" noshade id="quote"></blockquote id="quote"> No worries. I am always referring your articles in SQLTeam whenever date related questions are asked [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] Madhivanan Failing to plan is Planning to fail

Thanks! [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] – Frank Kalis Moderator Microsoft SQL Server MVP Webmaster:<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a>

I don’t think so and it is better that you will get the feedback to fine tune anything that is required. [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] <blockquote id="quote">quote:<hr height="1" noshade id="quote">Originally posted by FrankKalis I still feel strange when refering to my own article. Feel this looks like a show-off. [<img src=’/community/emoticons/emotion-1.gif’ alt=’

]]>