GETDATE() -vs- fn NOW()

  • Bob Bridges-173967

    SSCrazy

    Points: 2094

    Can someone help me understand when it is better to use GETDATE() as opposed to fn NOW()?

    I obviously get the same result when I run them in Query Analyzer but I can't find any documentation concerning when you should use one over the other.

    Thanks,

    Bob Bridges

  • Ninja's_RGR'us

    SSC Guru

    Points: 294069

    Actually now() is not a standard sql function.. are you sure that now is note a view or a user defined function that returns the getdate() value??

  • Bob Bridges-173967

    SSCrazy

    Points: 2094

    In Query Analyzer, I get the exact same results from the following two queries...

    SELECT {fn NOW()}

    SELECT GETDATE()

  • ron k

    SSCertifiable

    Points: 6053

    Using sql server 2000

    Select Now()

    ------------------------

    Server: Msg 195, Level 15, State 10, Line 1

    'now' is not a recognized function name.

  • ron k

    SSCertifiable

    Points: 6053

    ...And so do I ?

     

  • Ninja's_RGR'us

    SSC Guru

    Points: 294069

    I agree that it returns the same thing.. but I can't find any reference of it in the books online nor yahoo... So I guess it's an undocumented command that returns the date. So that would be my reason not to use it .

  • sushila

    SSC-Dedicated

    Points: 35293

    Here's something my googling fished out...

    Conversion of "NOW()" Access datatype in SQL Server 2000 datatype - GETDATE()







    **ASCII stupid question, get a stupid ANSI !!!**

  • sushila

    SSC-Dedicated

    Points: 35293

    Don't know why it says datatype and not function though...

    Don't shoot - I'm only the messenger!







    **ASCII stupid question, get a stupid ANSI !!!**

  • Michael Du Bois

    SSCertifiable

    Points: 5143

    Now() is used in both EXCEL and ACCESS to return the current date and time.

    But it is undocumented in T-SQL a sure road to trouble.

    HTH Mike

  • ron k

    SSCertifiable

    Points: 6053

    In addition to several replies advising to stay away from such undocumented features I did get this reply to my post on a ms newsgroup:


    <TD class=oa title="Anith Sen " noWrap width="1%" bgColor=#b71c0c>Anith Sen

      Jul 14 2003, 4:02 pm     show options

    Newsgroups: microsoft.public.sqlserver.programming
    From: "Anith Sen" <a...@bizdatasolutions.com> - Find messages by this author
    Date: Mon, 14 Jul 2003 14:59:18 -0500
    Local: Mon,Jul 14 2003 3:59 pm
    Subject: Re: getDate() vs {fn Now()}
    Reply to Author | Forward | Print | View Thread | Show original | Report Abuse

    GETDATE() is a T-SQL specific function which returns the current system date

    and time. The SQL standard equivalent is CURRENT_TIMESTAMP which is

    applicable in T-SQL as well. The {fn Now()} is an ODBC canonical function

    which can be used in T-SQL since the OLE DB provider for SQL Server supports

    them. There are no notable performance difference between these though. You

    can also use canonical format like :

    SELECT {fn CURRENT_TIMESTAMP()} AS "date & time",

           {fn CURRENT_DATE()} AS "date only",

           {fn CURRENT_TIME()} AS "time only" ;

     

  • ron k

    SSCertifiable

    Points: 6053

    Bob,

    Your best bet is to stick with Getdate().  This is the standard function used by just about everyone.

    I curious, where did you come accross fn Now() ?

     

  • sushila

    SSC-Dedicated

    Points: 35293

    Also in Visual Basic...







    **ASCII stupid question, get a stupid ANSI !!!**

  • Jonathan Tomkins

    Ten Centuries

    Points: 1248

    If you make them go wrong ( a useful skill to have no matter what anyone says )

    select GETDATE(0)

    select {fn Now(0)}

    they both give the same error:

    Server: Msg 174, Level 15, State 1, Line 1

    The getdate function requires 0 arguments.

    so it looks like {fn Now()} just routes to GETDATE()

    tried {fn LCase('LiTtLe')} as well, and if you make that go wrong it says it is using LOWER

    Interesting, but as ron k said, I'd stick with the standard TSQL statements.

     

  • ron k

    SSCertifiable

    Points: 6053

    Here's one more interesting reply from the ms newgroup.


    The ODBC escape sequences and functions are not undocumented.

    They are documented at:

    http://msdn.microsoft.com/library/en-us/odbc/htm/odbcescape_sequences_in_odbc.asp

    http://msdn.microsoft.com/library/en-us/odbc/htm/odbcscalar_functions.asp

    According to Books Online, topic "SQL Syntax Recommendations"

    http://msdn.microsoft.com/library/en-us/architec/8_ar_ad_87sj.asp

    you should use ODBC escape sequences when they provide functionality

    needed by the application but not provided by SQL-92 and use

    Transact-SQL syntax when it provides functionality required by the

    application but not provided by SQL-92 or the ODBC escape sequences.

    Razvan

    PS. Personally, I've never used ODBC escape sequences in my code.

    I think that ADO automatically uses some escape sequences in certain

    cases, but I'm not sure when.

  • Simon Berrange

    SSC Enthusiast

    Points: 116

    Yes, But what if you want to select entries where the date is bigger than or equal too todays date. When I use " >= GETDATE() " entries that are equal to todays date are not included?

Viewing 15 posts - 1 through 15 (of 25 total)

You must be logged in to reply to this topic. Login to reply