Udemy logo

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:

SQL Server:

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

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:

 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:

Syntax:

CONVERT(data_type(length),expression,style)

Where,

Here are some examples of the style values:

Value

(century yy)

Value

(century yyyy)

Input/Output Standard
0 or 100 mon dd yyyy hh:miAM (or PM) Default
1 101 mm/dd/yy USA
2 102 yy.mm.dd ANSI
3 103 dd/mm/yy British/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:

The result would look something like this:

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.

Page Last Updated: April 2014

Top courses in SQL

SQL - The Complete Developer's Guide (MySQL, PostgreSQL)
Academind by Maximilian Schwarzmüller, Maximilian Schwarzmüller, Manuel Lorenz
4.7 (837)
The Complete SQL Bootcamp 2022: Go from Zero to Hero
Jose Portilla
4.7 (141,855)
Bestseller
The Ultimate MySQL Bootcamp: Go from SQL Beginner to Expert
Colt Steele, Ian Schoonover
4.6 (71,295)
Bestseller
Master SQL For Data Science
Imtiaz Ahmad
4.7 (10,716)
Complete SQL and Databases Bootcamp: Zero to Mastery [2022]
Andrei Neagoie, Mo Binni, Zero To Mastery
4.6 (3,157)
Complete Microsoft SQL Server Database Administration Course
Imran Afzal, Abbas Mehmood
4.4 (1,822)
Bestseller

More SQL Courses

SQL students also learn

Empower your team. Lead the industry.

Get a subscription to a library of online courses and digital learning tools for your organization with Udemy for Business.

Request a demo