'How do I update a column named Date in SQL?

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?

sqlsql-updateexasol


Solution 1:[1]

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;

Solution 2:[2]

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...

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Zakaria
Solution 2

Related Questions