SQL Server, Oracle and PostgreSQL Date Functions


By:   |   Updated: 2021-09-07   |   Comments   |   Related: More > Other Database Platforms


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

Data Type Description
Time time of day (no date)
Date date (no time of day)
Smalldatetime both date value and time value, with format YYYY-MM-DD hh:mm:ss
Datetime both date value and time value, with format YYYY-MM-DD hh:mm:ss.nnn
Datetime2 both date value and time value, with format YYYY-MM-DD hh:mm:ss.nnnnnnn
Datetimeoffset both date value and time value, with format YYYY-MM-DD hh:mm:ss.nnnnnnn +/-hh:mm

Oracle

Data Type Description
DATE both date value and time value, with century, year, month, date, hour, minute and second.
TIMESTAMP both date value and time value (no time zone) same as DATE but with fractional seconds more.
TIMESTAMP WITH TIME ZONE both date value and time value, with time zone, same as timestamp but with time zone
TIMESTAMP WITH LOCAL TIME ZONE both date value and time value, with time zone, same as timestamp with time zone but no offset

PostgreSQL

Data Type Description
timestamp both date value and time value (no time zone) to microsecond
timestamp with time zone both date value and time value, with time zone to microsecond
date date (no time of day)
time time parts of day (no date)
time with time zone time of day (no date), with time zone to microsecond
interval time interval to microsecond

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
query results

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
query results

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

select CURRENT_TIMESTAMP
query results

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.

query results

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;
query results

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;
query results

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;
query results

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);
query results

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;
query results
SELECT CURRENT_TIME;
query results

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
query results

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
query results

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
query results

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
query results

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;
query results

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;
query results

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;
query results

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
query results

Extremely easy, now also subtract a week:

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

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;
query results

PostgreSQL

Now in PostgreSQL:

select current_date as actual_date, current_date+5 as actual_5_days;
query results

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;
query results

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
query results

Same for subtraction:

select current_date as actual_date, current_date -7 as date_week_before
query results

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
query results

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
query results

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;
query results

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;
query results

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;
query results

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')
query results

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
query results

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
query results

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;
query results

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
query results

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
query results

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





get scripts

next tip button



About the author
MSSQLTips author Andrea Gnemmi Andrea Gnemmi is a Database and Data Warehouse professional with almost 20 years of experience, having started his career in Database Administration with SQL Server 2000.

View all my tips


Article Last Updated: 2021-09-07

Comments For This Article





download














get free sql tips
agree to terms