How to write a date concisely in SQL?

Question

I have the employee table:

CREATE TABLE employee 
(
    id            integer        PRIMARY KEY,
    surname       character(15),
    employed_date date
);

to which I insert the following row:

INSERT INTO employee 
VALUES (4, 'Smith', to_date('2015-11-28','YYYY-MM-DD'));

Can I instead simply write?

INSERT INTO employee 
VALUES (4, 'Smith', '2015-11-28');

It works in my PostgreSQL installation. However, I would like to write portable code that works also in other databases, including Oracle, SQL Server, MySQL and SQLite; and with any locales.

Does it work also in these databases? If no, then maybe another format works in these databases and/or is ANSI standard?

[ISO 8601](https://en.wikipedia.org/wiki/ISO_8601) is the standard date format which is YYYY-MM-DD. However, all but trivial portable SQL code is almost impossible, especially for date handling. If you want to write portable code, use a SQL builder library. — Schwern, Oct 03 '20 at 11:20

dnoeth · Answer 1 · 2020-10-03T16:30:42.073

2

Most DBMSes (but not SQL Server & SQLite) support ANSI/Standard SQL date literals with a fixed YYYY-MM-DD format:

date '2020-09-20'

Similar for time and timestamp:

time '12:34:56'
timestamp '2020-09-20 12:34:56.02'

edited Oct 03 '20 at 16:30

answered Oct 03 '20 at 10:33

dnoeth

59,503
4
39
56

I have found this literal format in [Oracle](https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/Literals.html#GUID-8F4B3F82-8821-4071-84D6-FBBA21C05AC1), [PostgreSQL](https://www.postgresql.org/docs/13/datatype-datetime.html), and [MySQL](https://dev.mysql.com/doc/refman/8.0/en/date-and-time-literals.html) documentations. I cannot find it in SQL Server docs - maybe SQL Server has rather this literal: `{d '2015-11-28'}` ([link](https://learn.microsoft.com/en-us/sql/odbc/reference/develop-app/date-time-and-timestamp-literals))? – iwis Oct 03 '20 at 12:34
I don't know the exact location in the manuals, but it's valid T-SQL since at least SS2014 – dnoeth Oct 03 '20 at 13:04
1

Sorry, my fault, SQL Server still doesn't support it, Gordon is right. Same for SQLite – dnoeth Oct 03 '20 at 16:28

score 1 · Answer 2 · answered Oct 03 '20 at 11:48

First, you are using a char() type for the surname. This type pads the name with spaces, which is generally unadvisable for names. In general, you want a varchar() for names:

CREATE TABLE employee (
    id            integer        PRIMARY KEY,
    surname       varchar(15),
    employed_date date
);

I should add that 15 characters is probably not long enough for last names.

Or perhaps nvarchar(). The above works in all your mentioned databases, although Oracle recommends varchar2().

As for date constants, there are basically two ways to insert them:

date '2020-11-03'
'2020-11-03'

The first is standard SQL, but not all databases support it. Of the databases you mention, only Oracle requires the date keyword. SQL Server and SQLite don't allow it.
The databases that do not allow date will convert the string to a date, if a date is expected.

So, there is no single way to pass in a date constant that works across the databases you have mentioned. Well, the major exception is Oracle. And you can change the default date format to accept YYYY-MM-DD rather than DD-MMM-RR, but that is rarely done.

1. I was aware that `character(15)` type can be not well suited. I was migrating `VARCHAR2(15)` in tutorial database from Oracle to Postgres, and temporary chose it. 2. There is even no `to_date()` in SQL Server [link](https://stackoverflow.com/questions/45074493/use-to-date-in-sql-server-2012), which I used in Oracle and Postgres. — iwis, Oct 03 '20 at 13:04
If there is no common constant format, then maybe there is some function common for Oracle, SQL Server, MySQL and Postgres; like [Cast or Convert](https://stackoverflow.com/a/7175369/4592248)? — iwis, Oct 03 '20 at 13:22

score 0 · Answer 3 · edited Oct 03 '20 at 10:35

0

You can use the sql statement below to add a new employee to employee table. With the date format 'YYYY-MM-DD'.

 INSERT INTO employee (id,name,date) VALUES (4, 'Smith', '2015-11-28')

edited Oct 03 '20 at 10:35

bilginyuksel

160
2
7

answered Oct 03 '20 at 10:30

Can Güven

111
5

How to write a date concisely in SQL?

3 Answers3