Systems Engineering and RDBMS

Back to the Basics: Getting the system date

Posted by decipherinfosys on January 6, 2009

One of the readers had asked a question on how one can get the system date using a function in SQL Server. The question came from a DBA who has worked in Oracle and is now getting to know SQL Server as well as part of his responsibilities. There are a couple of ways of getting the system date in SQL Server:

1) You can use the GETDATE() function. It is a non-deterministic function which means that anytime you execute the function, you will be getting different values. You can execute this by a simple select statement:

select getdate()

You can also use this in a select statement, in the filter criteria etc. There are restrictions to using it in a UDF – you can read more here.

2) Another way of doing the same thing is by using the niladic function: current_timestamp.  The output is exactly the same as in the case of the getdate() function and all the same usage patterns and restrictions apply.

3) Yet another way of getting it is by using the ODBC canonical function: {fn Now()}.  This can also be used in T-SQL.  The output is again the same as the above two methods.

So, the very next question would be: Which one should we use when all three provide the same output?  If you are a vendor developing an application against both Oracle and SQL Server, then use current_timestamp since it is supported in Oracle as well.  In Oracle though, the output of that function gives you a TIMESTAMP WITH TIME ZONE value so the output would need to be formatted in case you need it a certain way.

If you are only using SQL Server, usage of any of the above approaches is fine.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: