SQL Server Performance

datetime and smalldatetime format

Discussion in 'SQL Server 2005 General DBA Questions' started by tboonleong, May 8, 2007.

  1. tboonleong Member

    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
  2. satya Moderator

    Steve Kass has contributed the following notes <blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><br />There is no international standard for representation of datetime values. ISO-8601 has a huge<br />number of options, and SQL Server accepts at least a couple of the ISO-8601 alternatives.<br /><br /><br />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<br />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"></font id="quote"></blockquote id="quote"><br /><br />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.<br /><br /><b>Satya SKJ</b><br />Microsoft SQL Server MVP<br />Writer, Contributing Editor & Moderator<br /<a target="_blank" href=http://www.SQL-Server-Performance.Com>http://www.SQL-Server-Performance.Com</a><br /><center><font color="teal"><font size="1">This posting is provided AS IS with no rights for the sake of <i>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.</i></font id="size1"></font id="teal"></center>
  3. FrankKalis Moderator

  4. satya Moderator

    [<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.<br /><br /><b>Satya SKJ</b><br />Microsoft SQL Server MVP<br />Writer, Contributing Editor & Moderator<br /<a target="_blank" href=http://www.SQL-Server-Performance.Com>http://www.SQL-Server-Performance.Com</a><br /><center><font color="teal"><font size="1">This posting is provided AS IS with no rights for the sake of <i>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.</i></font id="size1"></font id="teal"></center>
  5. FrankKalis Moderator

    [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />--<br />Frank Kalis<br />Moderator<br />Microsoft SQL Server MVP<br />Webmaster:<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a>
  6. satya Moderator

    Just for clarification, do you think your article still applicable to SQL 2005 too [<img src='/community/emoticons/emotion-5.gif' alt=';)' />].<br /><br /><b>Satya SKJ</b><br />Microsoft SQL Server MVP<br />Writer, Contributing Editor & Moderator<br /<a target="_blank" href=http://www.SQL-Server-Performance.Com>http://www.SQL-Server-Performance.Com</a><br /><center><font color="teal"><font size="1">This posting is provided AS IS with no rights for the sake of <i>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.</i></font id="size1"></font id="teal"></center>
  7. FrankKalis Moderator

    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
  8. Madhivanan Moderator

    <blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by tboonleong</i><br /><br />The SQL have default format for the datetime and smalldatetime data type. <br />1)May I know where to check the default date format? <br />2)How to change the date format?<br />Thanks<br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br />1 DBCC Useroptions<br />2 Dont worry. Let front end do that [<img src='/community/emoticons/emotion-5.gif' alt=';)' />]<br /><br />Madhivanan<br /><br />Failing to plan is Planning to fail
  9. Madhivanan Moderator

    <blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by satya</i><br /><br />[<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.<br /><br /><b>Satya SKJ</b><br />Microsoft SQL Server MVP<br />Writer, Contributing Editor & Moderator<br /<a target="_blank" href=http://www.SQL-Server-Performance.Com>http://www.SQL-Server-Performance.Com</a><br /><center><font color="teal"><font size="1">This posting is provided AS IS with no rights for the sake of <i>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.</i></font id="size1"></font id="teal"></center><br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br />Frank is so modest [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />Madhivanan<br /><br />Failing to plan is Planning to fail
  10. satya Moderator

    [<img src='/community/emoticons/emotion-1.gif' alt=':)' />] That should be opposite when it comes to Knowledge sharing or educating other...<br /><br /><b>Satya SKJ</b><br />Microsoft SQL Server MVP<br />Writer, Contributing Editor & Moderator<br /<a target="_blank" href=http://www.SQL-Server-Performance.Com>http://www.SQL-Server-Performance.Com</a><br /><center><font color="teal"><font size="1">This posting is provided AS IS with no rights for the sake of <i>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.</i></font id="size1"></font id="teal"></center>
  11. FrankKalis Moderator

    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=':)' />]<br /><br />--<br />Frank Kalis<br />Moderator<br />Microsoft SQL Server MVP<br />Webmaster:<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a>
  12. Madhivanan Moderator

    <blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by FrankKalis</i><br /><br />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=':)' />]<br /><br />--<br />Frank Kalis<br />Moderator<br />Microsoft SQL Server MVP<br />Webmaster:<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br />No worries. I am always referring your articles in SQLTeam whenever date related questions are asked [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />Madhivanan<br /><br />Failing to plan is Planning to fail
  13. FrankKalis Moderator

    Thanks! [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />--<br />Frank Kalis<br />Moderator<br />Microsoft SQL Server MVP<br />Webmaster:<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a>
  14. satya Moderator

    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=':)' />]<br /><blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by FrankKalis</i><br /><br />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=':)' />]<br /><br />--<br />Frank Kalis<br />Moderator<br />Microsoft SQL Server MVP<br />Webmaster:<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote">

Share This Page