DATETIME in mssql issue again (Clarion 10)

questions
1

Clarion 10, MSSQL table.

Go standard way of using datetime type in Clarion:
in MS SQL it defined as
[ChangedOn] [datetime] NULL,

in Clarion:

ChangedOn       STRING(8)

ChangedOn_Group GROUP,OVER(ChangedOn)
DateChanged       DATE
TimeChanged       TIME
                END

Nothing special; almost all other tables work just fine - I meant standard insert, update works and SQL statement generated by Clarion MSSQL driver is correct.

But for one table, I got this error (from Profiler):

UPDATE dbo.RESN SET CHANGEDON = '2025-04-16 07:28:13.9100000'
WHERE RESOURCEID = 314

error: Conversion failed when converting date and/or time from character string.

Somehow, Clarion driver sets milliseconds not 3 but 7 digits!
it should be like

UPDATE dbo.RESN SET CHANGEDON = '2025-04-16 07:28:13.910'
WHERE RESOURCEID = 314

and then it should be updated with no issue:
image

not sure what is wrong with that table and how it can be fixed?

2

MSSQL is not my strong suit, but in Postgres you can define the precision of the date time type so datetime(2) give 2 decimal points, which is perfect for Clarion. Iknow thereā€™s something similar with MSSQL, but I donā€™t use it enough to remember.

1 Like
3

Sean,

Thank you for your feedback.

Anyway, I have 10 other tables with the same ChangedOn column (the same definition in MSSQL and Clarion), and they are working just fine! And only this one table has this kind of problem without any reasonā€¦

I canā€™t change SQL datatype - so need some workaroundā€¦

4

Hi,

What about using a ā€œTā€ between date and hour?
SET CHANGEDON = ā€˜2025-04-16T07:28:13.910ā€™

Just my 0.02

5

Flavio,
But it is already working fine:
UPDATE dbo.RESN SET CHANGEDON = ā€˜2025-04-16 07:28:13.910ā€™
WHERE RESOURCEID = 314

Anyway, that update is generated by the Clarion mssql driver and found in SQL Profiler, so I canā€™t (and no need) change itā€¦

the problem is that Clarion somehow generates
UPDATE dbo.RESN SET CHANGEDON = ā€˜2025-04-16 07:28:13.9100000ā€™
WHERE RESOURCEID = 314

and it causes an issueā€¦

6

Weirdā€¦
There must be something different on the DCT definitions of the table/fields, on the driver optionsā€¦

7

I think youā€™re on to something.
I wouldnā€™t expect Clarion generate a different statement if both the database definition and the DCT are identical for all tables. Makes no sense so the logical thing is that is a discrepancy somewhere along the line.
Iā€™d examine them very closely and maybe try importing the table from SQL to ensure correctness.

8

To add ā€¦ Iā€™ve occasionally had similar experiences & it always was some small detail Iā€™d missed.

9

What does it look like traced from the Clarion side? Mine (Oracle driver) looks like:

042B4H(1) 10:01:52.851 Parsing T9 Cursor 66946776 : UPDATE BUSCASE SET ā€œMODIFIED_DATEā€ = :MODIFIED_DATE_ WHERE ā€œCASE_IDā€ = :CASE_IDk0 Return Code: 0 Time Taken:0.00 secs
042B4H(1) 10:01:52.852 Binding :MODIFIED_DATE_ with value 2025-4-16 10:1:52 as type DATE for T9 Cursor 66946776 Return Code: 0 Time Taken:0.00 secs

As you can see, only two decimals of precision on the seconds.

One thing to check: you say you have it set up as a datetime in MSSQL, but maybe you have it set up as a timestamp?

10

What does it mean ā€œtimestampā€?? It set up as
[ChangedOn] [datetime] NULL

11

What 32-bit ODBC drivers are installed on the workstation where the error occurs?

12

Looks like timestamp only goes to three decimal places. But datetime2 is a possibility.

Why Iā€™m suggesting looking at both the Clarion profile and checking your declaration on the serverā€¦

your profile statement on the SQL server side has seven decimals.

If you look at the clarion side you should see, like what I posted above, that Clarion is passing only 2 decimals precision to the SQLserver ODBC layer. Iā€™d be surprised if clarion is telling the SQL server ODBC driver that it it passing anything with more precision that that. So probably what you are doing in Clarion is not the problem. I think that the MSSQL OBDC driver is translating what it has been passed (only two digits of precision) into a literal that has nine decimal digits of precision, and I think it is doing that because it thinks that the datatype of ChangedOn in that table takes seven digits of precision. A datetime2 data type takes nine digits. a datetime only takes three.

Bottom line, I donā€™t think Clarion is generating that statement with seven decimal digits, I think the it is actually the ODBC driver doing it, which is probably why Rick is asking about what ODBC drivers are installed.

1 Like
13

Jon, Rick,

Thank you for your feedback.

But again, I have 10 ā€œtablesā€ with identical declarations for datetime column in Clarion DCT / MSSQL server that works! And just one table that doesnā€™t work!

And for that table, Clarion driver somehow sends 7 precision decimals instead of 3.

I have imported the table from SQL in DCT (to check), and it imported the same as I have now for my current table.

14

I was going to be rude and suggest that since everything was exactly the same as with the other table, the problem must just be bad juju and should try a burnt offering, but Iā€™ll resist that temptation.

Letā€™s just go through how this worksā€¦

Clarion issues a PUT.
The Clarion MSSQL or ODBC driver turns that into a SQL statement, something like:

Update dbo.rsn set changdeon = :changedon
where resourceID = 314.

:changedon points to a memory location in your clarion program, where the binary value that corresponds to ā€˜2025-04-16 07:28:13.91" sits in a STRING(8). So far as remember it is basically three separate numbers for the date portion and the number of seconds since midnight for the time portion.

The MSSQL ODBC driver on your system gets this information and sends it to the database. Possibly it turns the value that Clarion has passed it into a string literal and hard-codes it into the rest of the query; it seems unlikely, but I think it makes sense given the error your are getting. The string literal could just be a function of the MSSQL profiler trying to make things easier for you to read.
I assume you have taken that query and tried submitting it directly to MSSQL? Does it work if you put the ā€œTā€ in he middle, like Flavio suggests? According to wikipedia the extra decimals could cause trouble: " There is no limit on the number of decimal places for the decimal fraction. However, the number of decimal places needs to be agreed to by the communicating parties. For example, in Microsoft SQL Server, the precision of a decimal fraction is 3 for a DATETIME, i.e., ā€œyyyy-mm-ddThh:mm:ss[.mmm]ā€.[29]"

but that is specific to datetime, and would not be true for datetime2.

So, what could be wrong? Firstly Iā€™m confused by the fact that the query arriving at the server does not have a ā€œproperā€ where clause. Clarion would write a query of the form:
update yourtable set dt = :dt where . Unless you have specified NOWHERE for every column except RESOURCEID it doesnā€™t look like the sort of query Clarion would produce. Secondly, your check ā€œI have imported the table from SQL in DCT (to check), and it imported the same as I have now for my current table.ā€ doesnā€™t help at all. I would think that if you try to import either a datetime or a datetime2 into Clarion it would create exactly the same structure: how could it do any different, there is no special TIME datatype in Clarion that allows for extra precision. Plus, as already mentioned, if the datetime value passed by Clarion is being turned into a string literal, it is not Clarion that is doing it.

Up to you to do more digging, or maybe you should just go a look for a fatted calf?

15

The fatted calf is for feasting. He needs a ram :joy:

16

Can it be reproduced in Wizard generated app, without or with minimal embedded code?

17

No, DCT definitions of the table/fields are exactly the same, and the driver options are exactly the same. That is why I am asking WHY it is possible that it doesnā€™t work for one table

18

Jon,

not sure why you are saying that is has no WHERE clause?

it has it for sure, RESOURCEID is identity columnL

19

Mike,

But this is mostly Clarion generated simple app - no embeds at all

20

Well there must be something different I think, either Clarion side or MS SQL side.

You mentioned the Profiler is showing different results for this particular table.But isnā€™t the Profile part of MS SQL? What results is the Clarion trace giving? Just wondering if that is different.

You said there is no difference for the tables in the dictionary and in the table definitions in MS SQL. Just a few wild thoughts :

  • Are you using the same connection string?
  • Is there a default value/binding? (Allow NULLS setting the same?)
  • Is the collation the same (check on both the table as the particular field)

And in the dictionary:

  • No NAME attribute set?
  • No driver string set on the table?