Failure to accept the dates parameters format

Issue

Sometimes, you may receive error messages like the one below, varying depending on which executable objects it was generated.

Objective

To provide suggestions to resolve the conversion errors related to an inability to transform string values into datetime or smalldatetime datatypes.

Overview

Based on the collected data, it has been observed that these errors can arise from almost any executable object, such as Queries, Data Views, Data Transfer (Importing), or Graphic and Advanced Reports, which includes the Sample Tracker Charts. The common feature among them is their ability to receive date values as substitutional parameters. However, the date values may have been presented in different formats depending on the geographical region, personal preference, or existing historical records.

Solution

There are two ways to fix this conversion error, depending on the type of object affected.

1. Applies to Queries, Data Views, Graphic/Advanced Reports, Charts, Data Transfer (only Exporting objects), etc.
   Check and change the date parameters and Geobank objects with these date parameters.
   
   - Identify all the Geobank objects using date parameters. - In each object, go to the date parameter - [Substitution] tab and ensure the formatting is consistent and preferably matches the local Windows regional settings (e.g. dd/mm/yyyy).
   - From the [Substitution] tab, switch to the [Configuration] tab, and go to the dataset with date parameter/s.
   - In the SQL Code, add a new line above everything else already there and type: SET DATEFORMAT YMD. Although you could expect that if your date parameter is formatted as “dd/mm/yyyy”, then you may specify SET DATEFORMAT DMY; however, no, this will not pass, at least not in the current GB 2024 Service Pack 2 (24.0.1596.2) or previous versions of Geobank. “YMD” allows any date parameter format to be accepted. That differs from the native SQL Server engine behaviour and may be fixed in future versions of Geobank.  
   
   
   
   
2. Applies to Data Transfer (only Importing objects).
   
   - Identify all the Geobank objects using date parameters.
   - In each object, go to the date parameter - [Substitution] tab and ensure the formatting is consistent and preferably matches the local Windows regional settings (e.g. dd/mm/yyyy).
   - Select Properties for the Import object we need to fix
   - In the [Configuration] tab, click on the [Column mapping..] button in the [Target Table] frame.
   - Switch to the [Data Format] tab.
   - In each Date Field, we need to fix in [Format Data] frame the Format [Date/Time Format] to “Any Format” value (it works when the provided dates format matches the Windows regional date settings)
   
   
   

Note

You may have all the commonly shared substitution parameters between different executable objects defined as one instance each in a centralised place – Global Substitution Parameters. If that is the case, checking the format of how the dates are collected would be less of a task. Alternatively, you may need to check/modify all the date parameters in the Geobank objects separately.

Want to learn more?

Online Help Manuals - Click here for the latest version

Learning Management System - Click here to login or here to request access