Add a day to a date in Postgres and SQL Server

user_3365033 0
Viewed 300

I'm looking to find a way to add a day to a date in both Postgres and SQL Server so I don't have to add an if condition checking which database the server is running

DATEADD(day, 1, STOP_DATE)

doesn't work in PostgreSQL &

STOP_DATE + 1

doesnt work in sql server

sql-server-2017 postgresql sql-server sql
2 Answers

Overall, it is not a good idea to try to write SQL code using syntax that is common on both SQL Server and Postgres. You are severely limiting yourself and will sooner or later come across a query that runs too slowly because it doesn't use syntax specific to one of the DBMS.

For example, with your approach you are artificially refusing to use lateral joins, because their syntax is different in Postgres (LATERAL JOIN) and SQL Server (CROSS/OUTER APPLY).

Back to your question.

You can add an integer value to a date value in Postgres and to datetime value in SQL Server.

SQL Server

CREATE TABLE T(d datetime);

INSERT INTO T VALUES ('2020-01-01');

SELECT
  d, d+1 AS NextDay
FROM T

http://sqlfiddle.com/#!18/d519d9/1

This will not work with date or datetime2 data types in SQL Server, only datetime.

Postgres

CREATE TABLE T(d date);

INSERT INTO T VALUES ('2020-01-01');

SELECT
  d, d+1 AS NextDay
FROM T

http://sqlfiddle.com/#!17/b9670/2

I don't know if it will work with other data types.

user_41160170

Define a function in PostgreSQL that works like the sql server function.

Edit:

can't pass day

Create a function with the same name on each database system that adds a day accordingly.

user_98926990
Related