SQL Server Performance

Actual Size of datetime

Discussion in 'General Developer Questions' started by Madhivanan, Jun 7, 2007.

  1. Madhivanan Moderator


    CASE 1


    --Create Table
    create table #t(d datetime)

    --Create procedure
    create procedure #t_mysp (@d datetime)
    as
    Select @d=getdate()
    print @d

    Now
    see the prec, scale columns returned by the both

    exec tempdb..sp_help #t
    exec tempdb..sp_help #t_mysp

    CASE 2
    Set result mode to text in QA and run

    Select getdate()
    The result is


    ------------------------------------------------------
    2007-06-07 13:03:49.717

    (1 row(s) affected)

    Now note the length of ------------------------------------------------------
    It is 54

    So, what would be the actual size of datetime?

    Madhivanan

    Failing to plan is Planning to fail
  2. techbabu303 New Member

    Hi ,

    Not sure I got your Q right but here is some useful info from BOL

    Values with the datetime data type are stored internally by Microsoft SQL Server as two 4-byte integers.

    The first 4 bytes store the number of days before or after the base date, January 1, 1900. The base date is the system reference date. Values for datetime earlier than January 1, 1753, are not permitted.

    The other 4 bytes store the time of day represented as the number of milliseconds after midnight.


    Cheers
    Sat
  3. Adriaan New Member

    Do not try to find any significance in the number of hyphens above text results, even if it seems to have some for columns containing character data.
  4. FrankKalis Moderator

    <blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by Madhivanan</i><br /><br /><br />CASE 1<br /><pre id="code"><font face="courier" size="2" id="code"><br />--Create Table<br />create table #t(d datetime)<br /><br />--Create procedure<br />create procedure #t_mysp (@d datetime)<br />as<br />Select @d=getdate()<br />print @d<br /><br />Now <br />see the prec, scale columns returned by the both<br /><br />exec tempdb..sp_help #t<br />exec tempdb..sp_help #t_mysp<br /></font id="code"></pre id="code"><br />CASE 2<br />Set result mode to text in QA and run<pre id="code"><font face="courier" size="2" id="code"><br />Select getdate()</font id="code"></pre id="code"><br />The result is<br /><pre id="code"><font face="courier" size="2" id="code"> <br />------------------------------------------------------ <br />2007-06-07 13:03:49.717<br /><br />(1 row(s) affected)<br /></font id="code"></pre id="code"><br />Now note the length of ------------------------------------------------------ <br />It is 54<br /><br />So, what would be the actual size of datetime?<br /><br />Madhivanan<br /><br />Failing to plan is Planning to fail<br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br />You've asked this before. [<img src='/community/emoticons/emotion-5.gif' alt=';)' />]<br />I remember that I've asked this question - after you've posted it here - in the private MVP newsgroups. Though I did get some answers from MS, nobody could remember why this is happening and if there was a reason for this at all. [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />Call it a feature.<br /><br />--<br />Frank Kalis<br />Microsoft SQL Server MVP<br />Contributing Editor, Writer & Forum Moderator<a target="_blank" href=http://www.sql-server-performance.com>http://www.sql-server-performance.com</a><br />Webmaster:<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a>
  5. satya Moderator

    I believe something for me to take it up here for an answer... watch this space.

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
  6. FrankKalis 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 />I believe something for me to take it up here for an answer... watch this space.<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><br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br />Good luck! If you really find it all that interesting, I'm all ears what you come up with. [<img src='/community/emoticons/emotion-5.gif' alt=';)' />]<br /><br />--<br />Frank Kalis<br />Microsoft SQL Server MVP<br />Contributing Editor, Writer & Forum Moderator<a target="_blank" href=http://www.sql-server-performance.com>http://www.sql-server-performance.com</a><br />Webmaster:<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a>
  7. Madhivanan Moderator

    &lt;&lt;<br />You've asked this before. <br />&gt;&gt;<br />Not CASE1 [<img src='/community/emoticons/emotion-5.gif' alt=';)' />]<br /><br />&lt;&lt;<br />Call it a feature.<br />&gt;&gt;<br /><br />Well. then define that feature [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />Madhivanan<br /><br />Failing to plan is Planning to fail
  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 satya</i><br /><br />I believe something for me to take it up here for an answer... watch this space.<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><br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br />I am too waiting for the answer [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />Madhivanan<br /><br />Failing to plan is Planning to fail
  9. Adriaan New Member

    Is it the maximum number of characters you can have in a "long date" format, including the full name of the month, in any language version of Windows?
  10. 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 Adriaan</i><br /><br />Is it the maximum number of characters you can have in a "long date" format, including the full name of the month, in any language version of Windows?<br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br />Yes. This makes sense [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />Madhivanan<br /><br />Failing to plan is Planning to fail
  11. bradmcgehee New Member

    Datetime always takes 8 bytes, no matter what is actually stored there.

    --------------------------------------------------------------
    Brad M. McGehee, SQL Server MVP
    Technical Editor/Moderator www.SQL-Server-Performance.Com
    Director of DBA Education for www.Red-Gate.Com
    www.sqlbrad.com
    www.sqlHawaii.com
  12. FrankKalis Moderator

    <blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by Madhivanan</i><br /><br /><blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by Adriaan</i><br /><br />Is it the maximum number of characters you can have in a "long date" format, including the full name of the month, in any language version of Windows?<br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br />Yes. This makes sense [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />Madhivanan<br /><br />Failing to plan is Planning to fail<br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br />Btw, it is likely to be a bug in QA. Run this in SSMS and you'll get:<br /><pre id="code"><font face="courier" size="2" id="code"><br />Select getdate()<br /><br />-----------------------<br />2007-06-08 20:52:08.440<br /><br />(1 row(s) affected)<br /></font id="code"></pre id="code"><br />[<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />--<br />Frank Kalis<br />Microsoft SQL Server MVP<br />Contributing Editor, Writer & Forum Moderator<a target="_blank" href=http://www.sql-server-performance.com>http://www.sql-server-performance.com</a><br />Webmaster:<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a>
  13. Madhivanan Moderator

    Well. What about CASE 1? [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />Madhivanan<br /><br />Failing to plan is Planning to fail
  14. FrankKalis Moderator

    Open a case on Connect. [<img src='/community/emoticons/emotion-5.gif' alt=';)' />]<br /><br />--<br />Frank Kalis<br />Microsoft SQL Server MVP<br />Contributing Editor, Writer & Forum Moderator<a target="_blank" href=http://www.sql-server-performance.com>http://www.sql-server-performance.com</a><br />Webmaster:<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a>
  15. satya Moderator

    I have been through MS dev.team & other MVPs during Tech-ed and every one is saying datetime is 8 and small datetime is 4, I'm not sure where you are getting 54 on yoru results.

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
  16. Adriaan New Member

    Satya, on text results, the hyphens for character columns usually correspond to the maximum number of characters on the column.

    That's why I suggested 54 may be the largest number of characters in any language for the full "weekday, day month year time" format (like you might see for GETDATE()).
  17. FrankKalis Moderator

    Adriaan, <br />here's the repro from Steve Kass on the first inquiry about this issue:<br /><pre id="code"><font face="courier" size="2" id="code"><br />declare @oldlang nvarchar(50)<br />set @oldlang = @@language<br /><br />declare @s table (<br /> lang nvarchar(50),<br /> longdate nvarchar(60)<br />)<br /><br />declare C cursor for<br /> select name from syslanguages<br /><br />declare @lang nvarchar(50)<br /><br />open C<br />fetch next from C into @lang<br />while @@fetch_status = 0 begin<br /> set language @lang;<br /> with N(Nbr) as (<br /> select distinct top 12 number<br /> from spt_values<br /> where number &gt; 0<br /> order by number<br /> ), Dy(nd,d) as (<br /> select Nbr+6,datename(weekday,Nbr)<br /> from N<br /> where Nbr &lt;= 7<br /> ), Mn(md,m) as (<br /> select Nbr,datename(month,dateadd(month,Nbr,0))<br /> from N<br /> where Nbr &lt;= 12<br /> ), Dte(s) as (<br /> select<br /> d + ', ' + right(100+day(<br /> dateadd(day,datediff(week,0,<br /> dateadd(month,md,'20001201'))*7+7,0)<br /> + nd - 1),2)<br /> + space(1) + m + ' 2006 13:14:15.000'<br /> from Dy, Mn<br /> )<br /> insert into @s(lang,longdate)<br /> select top (1) @lang, s<br /> from Dte<br /> order by len(s) desc<br /> fetch next from C into @lang<br />end<br /><br />close C<br />deallocate C<br /><br />select *, from @s<br />-- longest result is<br />-- poniedzia³ek, 15 paŸdziernik 2006 13:14:15.000<br /><br />set language @oldlang<br /></font id="code"></pre id="code"><br />There are 2 occurences of 46 characters. Probably they left the remaining 8 spaces as room for future additions. [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />--<br />Frank Kalis<br />Microsoft SQL Server MVP<br />Contributing Editor, Writer & Forum Moderator<a target="_blank" href=http://www.sql-server-performance.com>http://www.sql-server-performance.com</a><br />Webmaster:<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a>
  18. Adriaan New Member

    Nice code - if you're in SQL 2005.
  19. FrankKalis Moderator

    Err, yes... [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />--<br />Frank Kalis<br />Microsoft SQL Server MVP<br />Contributing Editor, Writer & Forum Moderator<a target="_blank" href=http://www.sql-server-performance.com>http://www.sql-server-performance.com</a><br />Webmaster:<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a>
  20. Madhivanan Moderator

    Thanks Frank. At last I knew [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />Madhivanan<br /><br />Failing to plan is Planning to fail

Share This Page