GETDATE() -vs- fn NOW()

  • 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.


    Bob Bridges

  • 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??

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

    SELECT {fn NOW()}


  • Using sql server 2000

    Select Now()


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

    'now' is not a recognized function name.

  • ...And so do I ?


  • 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 .

  • 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 !!!**

  • 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 !!!**

  • 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

  • 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" <> - 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" ;


  • 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() ?


  • Also in Visual Basic...

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

  • 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.


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

    The ODBC escape sequences and functions are not undocumented.

    They are documented at:

    According to Books Online, topic "SQL Syntax Recommendations"

    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.


    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.

  • 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