SQL Server, Oracle and PostgreSQL Date Functions
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.
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:
|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|
|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|
|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.
The most common operation on dates is retrieving the actual datetime value.
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:
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
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
select to_char(trunc(sysdate), 'dd-mm-yyyy hh24:mi:ss') as actual_date from dual;
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
If we need to return the time with less precision, we can issue the same function with the precision in brackets:
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:
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.
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!
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;
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.
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.
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!
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:
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.
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.
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!
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.
- 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
- Functions: Date and Time Functions
- PostgreSQL, data 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
About the author
View all my tips
Article Last Updated: 2021-09-07