String was not recognized as a valid DateTime error

[Former Member]

I recently needed to test our program on Windows XP because of a bug that was specific to that OS. So I rebooted a laptop we have that can boot either XP or Windows 8 into XP and installed our software.

Since then, our back-end has been generating "String was not recognized as a valid DateTime" exceptions when the code tries to retrieve data from the database using the Entity Framework Model. Note that the same code works fine on later versions of Windows and other machines running XP. There must be something different about the settings on this laptop.

This error is happening in several different places, but one place it's happening is in the following code:

using ( CarSystemEntities context = new CarSystemEntities() ) {
    IQueryable<CarSystem.Site> childSites = context.Sites.Where( s => s.ParentId == SiteId && s.FromVersion == null );
    if ( childSites.Count() == 0 ) {
        StartAutoConfigurationMode();
    } else {
        foreach ( CarSystem.Site child in childSites ) { // <= Error happens here
            BuildModule( context, child );
        }
    }
}

The laptop's region is set to en-US. All of the region settings are fine. The application is receiving data from our server and inserting it into the local database properly, so the code in that module is fine. And, as I said, it runs fine on other versions of Windows. It even runs fine on Windows 8 on the same laptop.

We're using SA 12.0.1.3697. Anybody got any suggestions?

[former_SQLA_member1694878 · Accepted solution]

Hi Tony,

This could be caused by CR#746575 which is fixed in 12.0.1.3971 and later.

As a workaround, you should be able to execute:

SET OPTION PUBLIC.timestamp_with_time_zone_format = 'YYYY-MM-DD HH:NN:SS.SSSSSS+HH:NN'

on the server to ensure that the correct time stamp format is set for every connection.

[Former Member · Accepted solution]

Thanks, that option command worked. I thought I had executed that statement, but I realize now I probably left off the "PUBLIC." part of it.