How do I update a column named Date in SQL?-CodePudding

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?

CodePudding user response：

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;

CodePudding user response：

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