Undocumented DATE functions

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

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.

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/

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

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>

<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

[<img src='/community/emoticons/emotion-2.gif' alt='' />] 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>

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