0

When I try to execute this insert 

INSERT INTO BCS_EXPEDIENTES_REGISTRADOS (FOLIO, DOCUMENTO, FECHA_REGISTRO_DPS, CANT_PAGINAS) 
VALUES ('24', 'Suc4437_X722INSURGEN_20200305033042.tiff', '2020-04-13 14:13:54', '79')

I get an error:

The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.

The process is:

  • I have a value example: 04/13/2020 09:13:41
  • I convert this value to this format: =format([G_RECEPCION], "yyyy-MM-dd HH:mm:ss") to 2020-04-13 14:13:54
  • But when I execute the INSERT, it throws that error.

Any ideas for this case? I need to export the datetime in this format on SQL Server yyyy-MM-dd HH:mm:ss

Dale K
  • 25,246
  • 15
  • 42
  • 71
  • 3
    You say "export" but the query you post is an INSERT statement. That description simply confuses the issues you are facing. In situations like this, the most appropriate path depends on the definition of your table and posting the DDL for that table is needed. I can also tell you that datetime values are stored as unreadable, binary values - any "format" you see is something provided by the application you use to view this information. – SMor Apr 13 '20 at 20:34

1 Answers1

0

Let me start by saying that datetime has no concept of display format.
The display format is only relevant when we talk about the string representations of datetime values.

Then, lets take a look at the format you're using: yyyy-MM-dd HH:mm:ss (known as ODBC canonical).
When converting strings of this format to DateTime, the result of the conversion is culture dependent. This means that when operating in some languages (like English) SQL Server will attempt to use yyyy-MM-dd as date, but in other languages (like German) it will attempt to use yyyy-dd-MM as date.
This is the the reason you get a conversion error.

Importent note: Unless you explicitly set the language (or DateFormat, for that matter), SQL Server will use the default language of the login - so for some users conversion might fail while for other users it will succeed.

Another note is that this problem only exists with DateTime, but not with DateTime2 - converting this format to DateTime2 will always be interpreted as yyyy-MM-dd.

So, considering all this information, you have three options here:

  1. Stop using DateTime, use DateTime2 instead.
  2. Instead of using the unsafe ODBC canonical format use the safe ISO8601 format whenever dealing with string representation of datetime values: yyyy-mm-ddThh:mi:ss.mmm.
  3. Explicitly set language or date format (to ymd) before your insert statement.

I would recommend combining the first two and avoid using the third if possible.
DateTime2 is a better data type than DateTime, and ISO 8601 is the universal standard and is supported throughout different platforms and languages as an unambiguous datetime format.

Zohar Peled
  • 79,642
  • 10
  • 69
  • 121