How do I update a column named Date in SQL?

Question

I have a table that has a column named Date. This causes problems because Date is a data type name. I tried the following statements to escape it:

Update Tables.Subtable SET `Date` = "2022-03-14 07:20:32"  WHERE ID=960646;
Update Tables.Subtable SET "Date" = "2022-03-14 07:20:32"  WHERE ID=960646;

Update Tables.Subtable SET Tables.Subtable."Date" = "2022-03-14 07:20:32"  WHERE ID=960646;
Update Tables.Subtable SET Tables.Subtable.`Date` = "2022-03-14 07:20:32"  WHERE ID=960646;

Update Tables.Subtable SET Subtable."Date" = "2022-03-14 07:20:32"  WHERE ID=960646;
Update Tables.Subtable SET Subtable.`Date` = "2022-03-14 07:20:32"  WHERE ID=960646;

All of them caused an error. What is the right syntax here?

`SET Tables.Subtable."Date" = DATE'2022-03-14 07:20:32'`, or perhaps `SET Tables.Subtable."Date" = '2022-03-14 07:20:32'`. — jarlh, Mar 14 '22 at 08:28
If you are using sql server, use `[Date]`. Avoid naming the column as such. — shahkalpesh, Mar 14 '22 at 08:28
`date` is a SQL reserved word, and therefore needs to be delimited if used as an identifier. https://en.wikipedia.org/wiki/SQL_reserved_words — jarlh, Mar 14 '22 at 08:29
PLEASE include the actual error messages when you ask this type of question. — MatBailie, Mar 14 '22 at 08:41

score 2 · Accepted Answer · answered Mar 14 '22 at 08:31

2

The problem is in your date literal being delimited by double quotes. This should work:

Update Tables.Subtable SET "Date" = '2022-03-14 07:20:32'  WHERE ID=960646;

answered Mar 14 '22 at 08:31

Zakaria

4,715
2
5
31

1

Which is in the manual... https://docs.exasol.com/db/latest/sql_references/literals.htm#DateTimeLiterals – MatBailie Mar 14 '22 at 08:40
I get the error: [Code: 0, SQL State: 42000] object "Date" not found [line 2, column 35] (Session: 1727260684604729758) [Script position: 137 - 138] but this one works: `select * from Table.Subtable order by Table.Subtable."DATE" desc limit 4 ` So the column "Date" should acutally exist, right? – algebruh Mar 14 '22 at 09:00
1

@Roman27 Make sure that you use the correct capitalization of "Date". Identifiers inside double quotes are case sensitive! – sirain Mar 14 '22 at 09:13

SQLpro · Answer 2 · 2022-03-14T10:03:59.207

I see that you are using MS SQL Server...

First, SQL identifier AKA name of tables, columns, routines, constraints... must apply the standard SQL rules that says :

use only figures and pure latin letters (without accent...) and the underscore character
do not begin by a figure
lenght must be 128 characters maximum
try to do not use SQL keywords ("table', 'column", "date"...) except if the name is surrounded of double quote

So, a column nammed Date must be surround of double quote and that works perfectly in SQL Server

SELECT "Date", ...

Some RDBMS add a specific surround technic. In SQL Server you can use squared brackets to do so...

SELECT [Date], ...

Another trouble in you query can be the datetime format.

If the column use a DATETIME datatype which is not recommanded, the only date and time format supported whatever the settings of the session, is the short SQL ISO format that is :

'AAAAMMJJ hh:mm:ss.nnn'

You can execute this text to convince you :

SET LANGUAGE French;

SELECT CAST('2022-12-31 23:59:58' AS datetime) AS "Date";

--> Msg 242, Niveau 16, État 3, Ligne 3 La conversion d'un type de données varchar en type de données datetime a créé une valeur hors limites.

SET LANGUAGE English;

SELECT CAST('2022-12-31 23:59:58' AS datetime) AS "Date";

Date
-----------------------
2022-12-31 23:59:58.000

This firts test shows that for some languages, this format (Long SQL ISO) is unacceptable.

But when you use the short SQL ISO format, no trouble...

SET LANGUAGE French;

SELECT CAST('20221231 23:59:58' AS datetime) AS "Date";

Date
-----------------------
2022-12-31 23:59:58.000

SET LANGUAGE English;

SELECT CAST('20221231 23:59:58' AS datetime) AS "Date";

Date
-----------------------
2022-12-31 23:59:58.000

When using datatype DATE, DATETIME2, DATETIMEOFFSET, TIME, the only format that is supported whatever the settings of the session is, called long SQL ISO, relies on 'AAAA-MM-JJ hh:mm:ss.nnnnnnn'. For some compatability reasons with XML, the standard XML datetime format is also supported 'YYYY-MM-DDThh:mm:ss.nnnnnnn'

AS you can see in my test :

SET LANGUAGE French;

SELECT CAST('2022-12-31 23:59:58' AS datetime2) AS "Date";

SET LANGUAGE English;

SELECT CAST('2022-12-31 23:59:58' AS datetime2) AS "Date";

Now the two SQL statement works properly...

NOTE : the .nnn... parts is not required and the scale can be smaller.

Why two formats ? One (short SQL ISO) for DATETIME / SMALLDATETIME and the second (long SQL format)... Because the SQL ISO standard evolves by the time. The firts format was done for the SQL 2 (1992) of the SQL ISO Standard. The second one when SQL:1999 has been release. DATETIME and SMALDATETIME was inherited from Sybase at the end of the eighties. DATETIME2, DATE and DATTIME offset was relase in SQL Server 2008...

How do I update a column named Date in SQL?

2 Answers2