Invalid datetime format. […] The conversion of a nvarchar data type to a datetime data type resulted in an out-of-range value

Question

Here is info about our technical development environment :

• .NET 6
• C# 10
• Hasura GraphQL v2.6.2-pro.1
• Micrsoft SQL server 2019
• Visual Studio 2022
• "GraphQL" version="7.3.1" Proxy
• "GraphQL.Client" version="5.1.1" Proxy
• "GraphQL.Client.Serializer.Newtonsoft" version="5.1.1" Proxy

I have application code that

Converts integer-based Unix timestamp into a C# DateTimeOffset
I convert said C# DateTimeOffset to a C# DateTime
The C# DateTime value is converted into a string-based DateTime format

Here is the code in question:

_DateCreated = DateTimeOffset
    .FromUnixTimeMilliseconds(emailEvent.TimeStamp)
    .DateTime
    .ToString("dd-MMM-yyyy hh:mm:ss tt");

Unfortunately, when I run the aforementioned code, I get the following error:

{"Errors (1)rn1: Data exception. Invalid datetime format.
[Microsoft][ODBC Driver 18 for SQL Server][SQL Server] The conversion
of a nvarchar data type to a datetime data type resulted in an
out-of-range value.rn"}

I searched the internet, and came across the this posting:

https://stackoverflow.com/a/2307624/1338998

In the aforementioned posting, one of the commentors states the following:

The safest possible "date time" format from .net to sql I have used to
date is "yyyy-MM-dd HH:mm:ss.fff". PK 🙂

Therefore, I changed my code in such way that the datetime’s string format would be different like in the following refactored code:

_DateCreated = DateTimeOffset
    .FromUnixTimeMilliseconds(emailEvent.TimeStamp)
    .DateTime
    .ToString("yyyy-MM-dd HH:mm:ss.fff");

Essentially, I changed the Datetime formatting of string from:

…………..DateTime.ToString("dd-MMM-yyyy hh:mm:ss tt")

To

………………….DateTime.ToString("yyyy-MM-dd HH:mm:ss.fff"),

I No longer received the error.
However, my concern is whether the error throwing up really depends on the Region Date and Time Setting of the server that hosts my application.

To elaborate, my current server that host the application has the following Region Date and Time Settings:

Therefore, if someone changes said date and time format settings on the host server then the error might show up again regardless of what kind of C# Datetime formatting that I use in my application code.

Therefore, I’m wondering if my C# Datetime formatting would be in vain if the host server’s date and time format settings are changed.

How can I make changes to my C# Datetime formatting code in such a way that it is more robust/fault-tolerant so that it will works regardless of the date and time format settings on the host server?

are you sure that's the actual code that errors? Because the error is coming from sql server, while your code looks to be plain simple c# — 1 hour ago
When I changed the C# code that formats the DateTime String value to "yyyy-MM-dd HH:mm:ss.fff" , the error disappeared. That's all I know. — 1 hour ago
Your error suggests SQL Server, but your question states PostgreSQL. Which is it? — 1 hour ago
Pass a date time value to your SQL query, not a string. You can't have a conversion error to a date time if the value is already a date time. — 1 hour ago
As a side note the regional settings of the user on the device has nothing to do with how SQL Server interprets a literal string; that is defined by the sessions date format settings, which is normally tied to the LOGINs language setting. — 57 mins ago

score 1 · Accepted Answer · 2023-11-02 14:52:01Z

TL;DR; – if you’re using DateTime2 or DateTimeOffset in your database the rest of this answer doesn’t apply to you.

However, if you’re working with SQL Server’s DateTime data type and use string representation of DateTime values, you should be using the ISO 8601 human readable format,
which is yyyy-MM-ddTHH:mm:ss.

Using the Odbc format, which is yyyy-MM-dd HH:mm:ss (almost the same as ISO 8601 but with a space instead of T as the separator between date and time parts) can cause errors or worst – wrong data, because the way SQL Server will convert it to DateTime depends on the login* Date format (which can be overriden if set directly in the query batch explicitly by using SET DATEFORMAT, or implicitly by using SET LANGUAGE).

*login means the SQL Server login that performed the query.

Here’s some code to show what I mean:

DECLARE @Iso varchar(20) = '2023-09-13T16:18:32',
        @Odbc varchar(16) ='2023-09-13 16:18:32';

SELECT TRY_CAST(@Iso As DateTime) As Iso,
       TRY_CAST(@Odbc As DateTime) As Compact;

SET DateFormat YMD;

SELECT TRY_CAST(@Iso As DateTime) As Iso,
       TRY_CAST(@Odbc As DateTime) As Compact;

SET DateFormat YDM;

SELECT TRY_CAST(@Iso As DateTime) As Iso,
       TRY_CAST(@Odbc As DateTime) As Compact;

and it’s results:

Iso	Compact
2023-09-13 16:18:32.000	2023-09-13 16:18:00.000

(DateFormat YMD)

Iso	Compact
2023-09-13 16:18:32.000	2023-09-13 16:18:00.000

(DateFormat YDM)

Iso	Compact
2023-09-13 16:18:32.000	null

You can see a live demo on db<>fiddle

Invalid datetime format. […] The conversion of a nvarchar data type to a datetime data type resulted in an out-of-range value