Understanding Oracle SQL Date Format Made Easy

oracle sql date formatYou must have often found it challenging to handle date formats in queries, especially when a time portion is combined with the date. The most difficult part of working with dates is to be sure that the format of the date you are inserting in a table, matches the format of the date column in the database.

Did You Know This Interesting Fact About SQL Server Dates?

An SQL server supports only those dates that start from and after January 1, 1753. The logic behind this is that the English-speaking world started using the Gregorian calendar in 1753, before which the Julian calendar was used. It can be quite challenging converting prior dates to the Gregorian calendar. So the datetime data type is used only for dates after January 1, 1753. While reading through this article if you want to check out the Date function and its syntax then Learn SQL in 6 days is one of the courses to refer to.

Data Types in Different Severs

MySQL Server:

Following data types are used for storing a date or a date/time value in the database:

  • DATE – format YYYY-MM-DD
  • DATETIME – format: YYYY-MM-DD HH:MM:SS
  • TIMESTAMP – format: YYYY-MM-DD HH:MM:SS
  • YEAR – format YYYY or YY

SQL Server:

The SQL server comes with the following data types for storing a date or a date/time value in the database:

  • DATE – format YYYY-MM-DD
  • DATETIME – format: YYYY-MM-DD HH:MM:SS
  • SMALLDATETIME – format: YYYY-MM-DD HH:MM:SS
  • TIMESTAMP – format: a unique number

Oracle Server:

Oracle supports both date and time, and instead of using two separate entities, date and time, Oracle only uses one, DATE. The DATE type is stored in a special internal format that includes not just the month, day, and year, but also the hour, minute, and second.

Oracle’s default format for DATE is “DD-MON-YY”.

Oracle Database and PL/SQL provide a set of date and time datatypes that store both date and time information in a standard internal format: Here are the datatypes you can use for dates and times:

  1. DATE: This datatype stores a date and a time, resolved to the second. It does not include the time zone.
  2. TIME(s): This describes a time on a particular day, with seconds precision s, using the fields HOUR, MINUTE and SECOND in the format HH:MM:SS[.sF] where F is the fractional part of the SECOND value. If a seconds precision is not specified, then the default value of s is assumed.
  3. TIMESTAMP: Time stamps are similar to dates, but come with two key features – (1) You can store and manipulate times resolved to the nearest billionth of a second (9 decimal places of precision), and (2) You can associate a time zone with a time stamp. This describes both a date and time, with seconds precision s, using the fields YEAR, MONTH, DAY, HOUR, MINUTE and SECOND in the format YYYY-MM-DD HH:MM:SS[.sF] where F is the fractional part of the SECOND value. If a seconds precision is not specified, s defaults to 6.
  4. INTERVAL: INTERVAL records and computes a time duration or difference between two time periods. You can specify an interval in terms of years and months, or days and seconds, such as “4 years” or “60 days” or “9 minutes and 55 seconds”. 

There are effectively two kinds of INTERVAL:

  • “YEAR-MONTH” containing one or both of the fields YEAR and MONTH.
  • “DAY-TIME” containing one or more consecutive fields from the set DAY, HOUR, MINUTE and SECOND.

 SQL Server Date Functions

Here are some of the important built-in date functions in SQL Server along with their descriptions:

Function                

                                                                                           Description
GETDATE()Returns the current date and time
DATENAME()Returns a character string that represents the specifieddatepart of the specified date.
DATEPART()Returns an integer that represents the specified datepart of the specified date.
DATEADD()Returns a new datetime value by adding an interval to the specified datepart of the specified date.
DATEDIFF()Returns the time between two dates
CONVERT()Displays date/time data in different formats

Working with Dates in SQL

A FAQ in SQL Server forums is how to format a datetime value or column into a specific date format. Here is an explanation of the CONVERT function which is used to:

  • Convert an expression of one data type to another.
  • Display date/time data in different formats.

Syntax:

CONVERT(data_type(length),expression,style)

Where,

  • data_type(length) specifies the target data type (with an optional length)
  • expression specifies the value to be converted
  • style specifies the output format for the date/time

Here are some examples of the style values:

Value
(century yy)
Value
(century yyyy)
Input/OutputStandard
0 or 100mon dd yyyy hh:miAM (or PM)Default
1101mm/dd/yyUSA
2102yy.mm.ddANSI
3103dd/mm/yyBritish/French

 

Example of Convert Function

The following script uses the CONVERT() function to display different formats. We will use the GETDATE() function to get the current date/time:

  • CONVERT(VARCHAR(19),GETDATE())
  • CONVERT(VARCHAR(10),GETDATE(),10)
  • CONVERT(VARCHAR(10),GETDATE(),110)
  • CONVERT(VARCHAR(11),GETDATE(),6)
  • CONVERT(VARCHAR(11),GETDATE(),106)
  • CONVERT(VARCHAR(24),GETDATE(),113)

The result would look something like this:

  • Nov 08 2012 12:45 PM
  • 11-08-12
  • 11-08-2012
  • 08 Nov 12
  • 08 Nov 2012
  • 08 Nov 2012 12:45:34:243

How to Take it Forward?

You don’t have to stop your learning process with Date and Time formats. You can work further on SQL queries and groom yourself as a successful programmer with practical SQL skills. You can start with the refresher course SQL Database for Beginners and move on to the more challenging and advanced courses, including high-demand courses such as Oracle SQL 11g – Advance.