SQL Date Functions for Microsoft SQL Server, Oracle and PostgreSQL

Problem

SQL date functions are very useful in order to make operations and calculations on dates, both to filter or format the data retrieved by queries. Unfortunately, there are some differences between Microsoft SQL Server, Oracle and PostgreSQL which we will outline in this article.

Solution

In this tutorial we will review some of the basic date functions, the various possibilities, best practices and differences on doing operations with dates in SQL Server, Oracle and PostgreSQL as well as the different Date and Time data types.

Different Data Types for Date and Time

First we’ll take a look at the different data types involved in the following table:

SQL Server

Oracle

PostgreSQL

Simple Date Operations

As we see there are already some differences in the data types involved, let’s take a look now at some simple date operations, as always we will use the github freely downloadable database sample Chinook, as it is available in multiple RDBMS formats at this link: https://github.com/cwoodruff/ChinookDatabase. It is a simulation of a digital media store, with some sample data, all you have to do is download the version you need and you have all the scripts for data structure and all the Inserts for data.

SQL Server

The most common operation on dates is retrieving the actual datetime value.  In SQL Server this is easily done using the getdate() function, with a return value of the current datetime value.

select getdate() as actual_date

This is extremely easy, it returns actual date and time, but what if we want to format this datetime showing only the date? Then we can cast or convert the result:

select cast(getdate() as date) as actual_date

Also, there is the ANSI standard SQL function that does the same:

select CURRENT_TIMESTAMP

Oracle

If we try to do the exact same thing in Oracle we end up with totally different functions, first of all to retrieve the actual date/time we must use the sysdate function:

select sysdate as actual_date from dual;

Remember also that in Oracle it is not allowed to run a query without a FROM clause, thus we always add the from dual clause.

Moreover, in Oracle what is returned depends on the session’s national formatting so that’s why this time we see only the date, but if we apply a date formatting:

select to_char(sysdate, 'dd-mm-yyyy hh24:mi:ss') as actual_date from dual;

We see that we have both date and time, if we’d like to consider only the date we use the trunc function:

select to_char(trunc(sysdate), 'dd-mm-yyyy hh24:mi:ss') as actual_date from dual;

PostgreSQL

With PostgreSQL we have another function, or to be correct we have more than one function, all ANSI SQL standard. For example we can return the actual date and time with CURRENT_TIMESTAMP:

SELECT CURRENT_TIMESTAMP;

If we need to return the time with less precision, we can issue the same function with the precision in brackets:

SELECT CURRENT_TIMESTAMP(0);

Please note that with CURRENT_TIMESTAMP time zone is also returned.

If we’d like to return just the date or the time, in PostgreSQL we have these functions:

SELECT CURRENT_date;

SELECT CURRENT_TIME;

Add and Subtract from Dates

Let’s now do some operations with these dates, one of the most common would be to add or subtract a certain amount of time to a date.

SQL Server

In SQL Server we have the DATEADD function for this purpose. Let’s review an example adding 5 days to the actual date:

select dateadd(d,5,getdate()) as actual_5_days

For the syntax: the first entry is the interval, it can be days, years, months, weeks, etc. The second entry is the number that we need to add and finally the date in which to add.

Suppose now that we need to add 5 days to the actual date but we do not need the time so just the date. We need to cast the result of GETDATE() as we did before.

select dateadd(d,5,cast(getdate() as date)) as actual_5_days

Now let’s suppose instead that we need to add 7 days to our invoice date in the Invoice table in Chinook database in order to have the due payment date of invoices greater than 20 Euros:

select Invoicedate, dateadd(d,7,Invoicedate) as PaymentDate, Total
from Invoice
where total>20

Quite easy, no? Now let’s suppose that we need to subtract a week from the actual date:

select dateadd(WEEK,-1,cast(getdate() as date)) as week_before

Please notice that we use the same DATEADD() function also for subtracting, but using a negative number!

Oracle

Let’s try the same operations on Oracle. First add 5 days to actual date:

select sysdate+5 as actual_5_days from dual;

As you can see in Oracle it’s extremely simple as you use a simple + to add an interval. As you notice the number here represents a day, so if we want to add an hour, we can do it with a fraction:

select to_char(sysdate+1/24,'dd/mm/yyyy hh24:mm:ss') as actual_1_hour from dual;

If we need just the date as before we use the TRUNC function:

select to_char(TRUNC(sysdate)+5,'dd/mm/yyyy hh24:mm:ss') as actual_5_DAYS from dual;

Now let’s do the same query as in SQL Server adding 7 days to invoice date:

select Invoicedate, Invoicedate+7 as PaymentDate, Total
from chinook.Invoice
where total>20

Extremely easy, now also subtract a week:

select sysdate as actual, sysdate-7 as week_before from dual;

We can also apply all the formatting that we’ve seen before:

select to_char(sysdate,'mm/dd/yyyy') as actual, to_char(sysdate-7,'mm/dd/yyyy') as week_before from dual;

PostgreSQL

Now in PostgreSQL:

select current_date as actual_date, current_date+5 as actual_5_days;

Again, here we encounter almost the same syntax as Oracle, the notable difference is the way in which we can define time intervals different than 1 day:

select current_timestamp as actual_date, current_timestamp+interval '1 hour' as actual_1_hour;

Now let’s add 7 days to InvoiceDate. Please notice that since the column InvoiceDate is a timestamp data type, we need to specify the interval, if we had put just 7 it would have returned an error.

select "InvoiceDate","InvoiceDate"+ interval '7 days' as PaymentDate, "Total"
from "Invoice"
where "Total">20

Same for subtraction:

select current_date as actual_date, current_date -7 as date_week_before

Having used the function current_date that returns a date data type I can use just an integer and it’s implicit that is days. If we instead use current_timestamp we need to specify the interval:

select current_timestamp as actual_date, current_timestamp - interval '7 days' as date_week_before

Time Range Between Two Dates

Another common operation on dates is to obtain the time range between two dates.

SQL Server

For this purpose, in SQL Server we use the DATEDIFF function, we suppose that we need to obtain the average time passed between one purchase (first row) and the next (following rows) for each customer. I actually used this example in my windows functions tip.

So, I just copied the code and explain just the SELECT DATEDIFF syntax:

;WITH InvCust AS
(SELECT DISTINCT CustomerID, 
        InvoiceDate AS cur_d, 
        next_d = LEAD(InvoiceDate, 1) OVER (PARTITION BY CustomerID ORDER BY InvoiceDate)
  FROM Invoice)
,customer_avg as 
(SELECT CustomerID,cur_d
       ,next_d
       ,dif_day = DATEDIFF(DAY, cur_d, next_d)
       ,avg_cust = AVG(DATEDIFF(DAY, cur_d, next_d)) OVER (PARTITION BY CustomerID)
       ,avg_all = AVG(DATEDIFF(DAY, cur_d, next_d)) OVER ()
FROM InvCust)
select distinct customer.customerid, FirstName + ' ' + lastname as Customer, country,avg_cust, avg_all
from customer_avg
inner join customer
on Customer.CustomerId=customer_avg.CustomerId
order by avg_cust

So, the DATEDIFF is quite simple as we have just to give the unit in which we want the difference, in this case DAY, then the first date and the second day.

Oracle

WITH InvCust AS
(SELECT DISTINCT CustomerID, 
        InvoiceDate AS cur_d, LEAD(InvoiceDate, 1) OVER (PARTITION BY CustomerID ORDER BY InvoiceDate) as next_d
  FROM chinook.Invoice)
,customer_avg as 
(SELECT CustomerID,cur_d, next_d
  ,(next_d-cur_d)as dif_day, AVG((next_d-cur_d)) OVER (PARTITION BY CustomerID) as  avg_cust
  ,AVG(next_d-cur_d) OVER () as avg_all
FROM InvCust)
select distinct customer.customerid,FirstName||' '||lastname as Customer, country,cast(avg_cust as decimal(5,2)) as avg_customer, cast(avg_all as decimal(5,2)) as avg_allcust
from customer_avg
inner join chinook.customer
on Customer.CustomerId=customer_avg.CustomerId
order by avg_customer, customerid;

In Oracle as you can see is even simpler, just a subtraction between two dates and you have automatically the difference in days!

PostgreSQL

WITH InvCust AS
(SELECT DISTINCT "CustomerId", 
        "InvoiceDate" AS cur_d, LEAD("InvoiceDate", 1) OVER (PARTITION BY "CustomerId" ORDER BY "InvoiceDate") as next_d
  FROM "Invoice")
,customer_avg as 
(SELECT "CustomerId",cur_d, next_d
  ,next_d-cur_d as dif_day, AVG(next_d-cur_d) OVER (PARTITION BY "CustomerId") as avg_cust
  ,AVG(next_d-cur_d) OVER () as avg_all
FROM InvCust)
select distinct "Customer"."CustomerId","FirstName"||' '||"LastName" as Customer, "Country",avg_cust, avg_all
from customer_avg
inner join "Customer"
on "Customer"."CustomerId"=customer_avg."CustomerId"
order by avg_cust;

And same in PostgreSQL, although in this RDBMS we also have the function AGE, let’s do the same query using this function:

WITH InvCust AS
(SELECT DISTINCT "CustomerId", 
        "InvoiceDate" AS cur_d, LEAD("InvoiceDate", 1) OVER (PARTITION BY "CustomerId" ORDER BY "InvoiceDate") as next_d
  FROM "Invoice")
,customer_avg as 
(SELECT "CustomerId",cur_d,next_d
  ,age(next_d,cur_d) as dif_day,AVG(age(next_d,cur_d)) OVER (PARTITION BY "CustomerId") as avg_cust
  ,AVG(age(next_d,cur_d)) OVER () as avg_all
FROM InvCust)
select distinct "Customer"."CustomerId","FirstName"||' '||"LastName" as Customer, "Country",avg_cust, avg_all
from customer_avg
inner join "Customer"
on "Customer"."CustomerId"=customer_avg."CustomerId"
order by avg_cust;

As you can see the result is no more returned in days but it is transformed in months and years (if we have an interval big enough). It can give you also the result subtracting from the current_date:

select age(timestamp'2014-01-01')

Please notice that we have passed the date formatted as a timestamp using the timestamp function together with the manually inserted date.

Extract Date Part from Date

Another common operation that we all do on dates is to extract a part of it, like the year or the month.

SQL Server

In SQL server to do this we can use basically 4 different functions depending on what we want to achieve, the most obvious that can do pretty much everything is DATEPART:

SELECT getdate() as actual_date, 
       DATEPART(d,getdate()) as date_day,
       DATEPART(month,getdate()) as date_month,
       DATEPART(year,getdate()) as date_year

As you can see it’s pretty easy, just put the datepart you need to extract and the date and it’s done! But this can be achieved also in a different way:

SELECT getdate() as actual_date, 
       day(getdate()) as date_day,
       month(getdate()) as date_month,
       year(getdate()) as date_year

Same result also here, using directly the DAY, MONTH and YEAR functions.

Oracle

In Oracle we achieve the same results using the function EXTRACT:

SELECT SYSDATE AS ACTUAL_DATE, 
       EXTRACT(DAY FROM SYSDATE) AS ACTUAL_DAY,
       EXTRACT(MONTH FROM SYSDATE) AS ACTUAL_MONTH,
       EXTRACT(YEAR FROM SYSDATE) AS ACTUAL_YEAR FROM DUAL;

Quite easy and almost the same syntax as in SQL Server!

PostgreSQL

In PostgreSQL there are basically 2 functions to do the same, as we have both date_part and extract:

SELECT current_date AS ACTUAL_DATE, 
       EXTRACT(DAY FROM current_date) AS ACTUAL_DAY,
       EXTRACT(MONTH FROM current_date) AS ACTUAL_MONTH,
       EXTRACT(YEAR FROM current_date) AS ACTUAL_YEAR

Exactly the same syntax as in Oracle, now the date_part:

SELECT current_date AS ACTUAL_DATE, 
       date_part('day',current_date) AS ACTUAL_DAY,
       date_part('month', current_date) AS ACTUAL_MONTH,
       date_part('year', current_date) AS ACTUAL_YEAR

A slightly difference with the SQL Server syntax but almost the same, please notice that the standard ANSI SQL function in this case is the EXTRACT one.

Next Steps

• We have learned in this tip some of the functions related with manipulating dates and times in all 3 RDBMS, there are some more functions, but these are by far the most used and the ones that you’ll absolutely need to know.
• As usual links to the official documentation:
  • SQL Server: Date and Time Data Types and Functions (Transact-SQL)
  • Oracle, data types: Datetime Data Types and Time Zone Support
  • PostgreSQL, data types: Date/Time Types
    • Functions: Date/Time Functions and Operators
• Some links to other MSSQLTips articles regarding date functions:
  • SQL Server Date and Time Functions with Examples
  • SQL DATE Functions
  • SQL Server Dates Tips
  • SQL Server DATEPART Function for DayofYear, Weekday, Day of the Week, etc.
  • Converting UTC to local time with SQL Server CLR
  • Determine SQL Server Date and Time Parts with DATEPART and DATENAME Functions
  • GETDATE and GETUTCDATE SQL Server Functions