GETDATE() -vs- fn NOW()

Question

GETDATE() -vs- fn NOW()

Bob Bridges-173967

SSCrazy

Points: 2094
More actions
May 19, 2005 at 1:36 pm

#186047

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

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

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

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

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 More actions · Answer 2

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

SELECT {fn NOW()}

SELECT GETDATE()

ron k SSCertifiable Points: 6103 More actions · Answer 3

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: 6103 More actions · Answer 4

ron k

SSCertifiable

Points: 6103

May 19, 2005 at 2:32 pm

#560045

...And so do I ?

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 5

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 More actions · Answer 6

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 More actions · Answer 7

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: 5163 More actions · Answer 8

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: 6103 More actions · Answer 9

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()}

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: 6103 More actions · Answer 10

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 More actions · Answer 11

Also in Visual Basic...

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

Jonathan Tomkins Ten Centuries Points: 1268 More actions · Answer 12

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: 6103 More actions · Answer 13

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 More actions · Answer 14

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?