SQL Server Performance

Undocumented DATE functions

Discussion in 'Performance-Related Article Discussions' started by Madhivanan, Jul 11, 2007.

  1. Madhivanan Moderator

    SELECT {fn CURRENT_DATE()}<br /><br />returns<br /><br /> <br />---------- <br />2007-07-11<br /><br />(1 row(s) affected)<br /><br /><br />I wonder what the datatype of the result is. [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />I never see anyone using that which seems available in SQL Server 2005 as well<br /><br />Madhivanan<br /><br />Failing to plan is Planning to fail
  2. satya Moderator

    This is used in MySQL & PostgreSQL too.
    It is used by DMO internally for date returned values, you may be aware there are three ways to retrieve the current datetime, they are CURRENT_TIMESTAMP, GETDATE(), {fn NOW().

    The return value is same as GETDATE.

    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.
  3. dineshasanka Moderator

    SELECT {fn CURRENT_TIME()} as well

    ----------------------------------------
    Contributing Editor, Writer & Forums Moderator
    http://www.SQL-Server-Performance.Com

    Visit my Blog at
    http://dineshasanka.spaces.live.com/
  4. Madhivanan Moderator

    Thanks Satya. But I dont see it in BOL and thats why wondering [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />Madhivanan<br /><br />Failing to plan is Planning to fail
  5. satya Moderator

    It is undocumented but specified in reserved key words of BOL, further I would like to hear from our resident datetime champion (you know who[<img src='/community/emoticons/emotion-1.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">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>
  6. 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 />It is undocumented but specified in reserved key words of BOL, further I would like to hear from our resident datetime champion (you know who[<img src='/community/emoticons/emotion-1.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">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 />Yes. I always call him DATETIME specialist [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />Madhivanan<br /><br />Failing to plan is Planning to fail
  7. satya Moderator

    [<img src='/community/emoticons/emotion-2.gif' alt=':D' />] Call Dr. DateTime<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>
  8. FrankKalis Moderator

    The {fn CURRENT_DATE()} is a canonical function implemented in ODBC. Since the OLE DB provider for SQL Server does support ODBC. I think it returns a string value according to the local settings:


    DECLARE @d DATETIME
    SET LANGUAGE German
    SET @d = {fn CURRENT_DATE()}
    SELECT @d
    GO
    DECLARE @d DATETIME
    SET LANGUAGE English
    SET @d = {fn CURRENT_DATE()}
    SELECT @d

    Changed language setting to Deutsch.

    -----------------------
    2007-11-07 00:00:00.000

    (1 row(s) affected)

    Changed language setting to us_english.

    -----------------------
    2007-07-11 00:00:00.000

    (1 row(s) affected)




    --
    Frank Kalis
    Microsoft SQL Server MVP
    Contributing Editor, Writer & Forum Moderatorhttp://www.sql-server-performance.com
    Webmaster:http://www.insidesql.de
  9. Adriaan New Member

    Funny how all date and time related system functions always return values that we must neutralize before further use. We have better things to do with our time here.

Share This Page