Are you a DBA and just inherited a SQL Server or Oracle database? Are you migrating from one or the other? I’ve decided to start a series comparing these two RDBMS giants and the first topic is date formatting.

Both of these systems use SQL, which is a language used to access and modify data in databases. SQL or Structured Query Language has been an ANSI and ISO standard since the mid 1980’s.

Since SQL itself is a standard, why is this post needed? Well as it turns out, each database vendor placed their own flavors of SQL in their software. For Oracle this may be PL/SQL and SQL Server it’s T-SQL. These additions help enhance the database performance over that of the ANSI standard language. An example of this is the ISNULL function.

Obtaining the current date and time in SQL Server

Querying dates is a breeze in SQL Server but can be done in many ways. To get the current date simply query the GETDATE() function. This will return the current time with accuracy of .000, .003, or .007 seconds and is based on the DATETIME data type. Using SYSDATETIME() results in accuracy of 100 nanoseconds because it’s based on the DATETIME2 data type.

These aren’t the only two functions for getting the current time. Actually there are quite a few more that deal with time zones and other such items but that’s a topic for another post. Today we’ll focus on just these two.

Example:

SELECT GETDATE(), SYSDATETIME()

Result:

2017-08-04 14:05:25.620    2017-08-04 14:05:25.6221444

Obtaining the current date and time in Oracle

Getting the current date from Oracle is just as easy but takes a tiny bit more. Rather than query a function directly you’ll want to query from a table. In this case we’ll query the DUAL table.

To query the current date and time, you’ll want to use the SYSDATE and SYSTIMESTAMP functions. There’s one catch. SYSDATE returns only the date but it has the time if you need it. Sounds complex but let’s look at it in action.

SELECT SYSDATE, SYSTIMESTAMP FROM dual;

Result:

04-AUG-17    04-AUG-17 02.19.05.880000 PM –05:00

Quite different results than that of SQL Server.

Formatting date and time values in SQL Server

Formatting comes in many forms in SQL Server. You can format dates using the CONVERT() function. You can also format using the FORMAT() function. Format was added in SQL Server 2012 and is structured very similar to MS Office.

FORMAT()

This is a really neat function because it allows you to do so much so easily. You can format as a culture such as ‘en-us’ or ‘de-de’. This will show the date and time as whatever is preferred in that culture.

SELECT FORMAT(getdate(), ‘d’, ‘en-gb’)

Result:

31/07/2017

You can also format by using the Office type strings such as ‘dd/MM/yyyy’ or ‘MM/dd/yy hh:mm.ss’

SELECT  FORMAT(getdate(), ‘MM/dd/yy hh:mm.ss’)

CONVERT()

Convert is great but it isn’t as easy. Since when is the “classic” method ever easy?  Convert has plenty of functions. Formatting datetime values is just a small fraction of the things it can do.

A full list of formats can be found here: https://docs.microsoft.com/en-us/sql/t-sql/functions/cast-and-convert-transact-sql

I’d say the one I’ve used the most over the years is 112. This is because I would generally use this in a BCP file name or something similar. 108 may also be useful for file names.

SELECT CONVERT(varchar(8), GETDATE(), 112)

Result:

20170731

Formatting date and time values in Oracle

One way to format date and time values in Oracle is to use the to_char() function. This function is similar to SQL Server’s convert() function but focuses on changing values to a character string.

SELECT to_char(sysdate, ‘MM/DD/YY HH24:MI:SS’) FROM dual;

Result:

07/31/17 14:37:04

Conclusion

Even though both Oracle and SQL Server support ANSI and ISO standards, performing certain tasks is different. Mastering these differences should be a breeze if you’ve been around databases or even a spreadsheet application long enough. It’s just a matter of simple syntax in many occasions.

If you enjoyed this article and would like to read more of my articles, please take a moment to look over my blog at SQLTechBlog.com.

If you’d like to connect on LinkedIn, I can be found here: LinkedIn.com.

I can also be found on Twitter and if you like food, fishing, and outdoor life: Instagram.