Hello
How does one define MSSQL DateTimeOffset column in Clarion dictionary?
When declaring it as a DATE in dictionary and using |READONLY and |NOWHERE flags in External name field I can open existing record and modify other columns using a form without errors.
However when creating a new record using a form and saving it I get “Error: Record changed By Another Station (89)”
Thanks,
Lasse
I’ve never had to deal with that data type in Clarion
But I just made a dummy table and imported it into a dictionary
CREATE TABLE dateTest (pk INT IDENTITY,
dto DATETIMEOFFSET NULL,
dto0 DATETIMEOFFSET(0) NULL,
name VARCHAR(100) NULL,
dt DATETIME NULL);
The import wizard converted the datetimeoffset to a string(34) and the datetimeoffset(0) to a string(26). You might try that?
Or if you don’t need the column in the app, what happens if you just omit the column from the dictionary?
Also check the downlevel compatibility section.
Thank you Jane!
Import wizard imports nothing for me for some reason, but your example helped and inserting a record in form works in one app now. SQL trace log shows as follows
However in another app using another dictionary inserting a new record in form throws “invalid character value for cast specification”. SQL trace log shows as follows
I can’t see any difference in dictionary column definitions between the apps and don’t know how to control C type in mssql driver log. So I ended up removing the datetimeoffset column from the second app, I can’t do “TABLE1{PROP:SQL} = 'SELECT * FROM TABLE1…” now, but inserting in a form works without errors.
I have an issue with inserting a new record with DATETIMEOFFSET column.
I have defined it in DCT as above (first dto group) but when I try inserting (the standard form) I get error…
Can somebody provide a code for how to Initialize all fields in that group please?
Do you need that column in your app?
LOL! Sure I need it.
OK, there was always the chance that the database had the field but your app didn’t need it, in which case the fix is easy, just leave it out of your dct.
Since you’re getting the error I presume the value is being set by the database, using GETDATE()?
Do you have access to edit the database? If so, you could try changing the precision of the time portion, since without you providing more detail I have to assume it is the time value that is too precise for Clarion. Have you looked at the data in the database? Do you get the error on all records, or only some? If so, what is different about the records that cause an error?
etc etc
https://chat.openai.com/share/f695a9ec-57e9-4b3b-a864-567cde0ca778
Just asking again the same question…
In MSSQL
[CreatedOn] [datetimeoffset](7) NOT NULL,
I have an issue with inserting/updating a record with DATETIMEOFFSET column.
I have defined it in DCT as above (first dto group)
CreatedOn STRING(34)
CreatedOn_GROUP GROUP,OVER(CreatedOn)
CreatedOn_Date STRING(@D10-)
CreatedOn_Space1 STRING(@P P)
CreatedOn_Time STRING(@T4)
CreatedOn_Dot STRING(@P.P)
CreatedOn_Hundreds STRING(@N7)
CreatedOn_PlusMinus STRING(2)
CreatedOn_TimeOffset STRING(@T1)
END
but when I try inserting (the standard form) I get an error…
Can somebody provide a code for how to Initialize all fields in that group, please? I mean a real Clarion code example.
I do not set the value by the database, using GETDATE() etc. I need to init it from the Clarion code so I can ADD() / PUT() and so on.
thanks in advance
Not an MSSQL user, but…
I think you want the ‘0’ version of all of those time formats. They always need to be fully padded, e.g. 2024-01-15, not 2024/1/15. so @d010, @t04, @t01
“String values returned to applications are always the same length for a given column. Year, month, day, hour, minute, and second components are padded with leading zeros to their maximum width, and there is one space between date and time in datetime values.”
From Type support, ODBC Date and Time - SQL Server | Microsoft Learn
Similarly the second fractions need to be right-padded with zeroes, which you are going to have to do manually.
Have no idea why you are using 7 digits of precision. Clarion will give you at most two.
Thanks, Jon.
re 7. Yep, it is like this in my mssql database (datetimeoffset(7)) so I can’t change it.
BTW I just noted that my declaration has a STRING(@D10-) but in original post it is PICTURE(@D10-)
strange - in DCT it defined as PICTURE() and in generated CLW file it is as STRING()
does it make sense?
A real Clarion code example for you would be like this:
MESSAGE('DateTimeOffset field value: '& CreatedOn)
after you’ve assigned all the fields inside CreatedOn_GROUP.
You know, formatted DateTimeOffset(7) field looks like 2025-01-15 17:34:40.6400000 +00:00
And Clarion help:
If the value is greater than the maximum value the picture can display, a string of pound signs (#) is displayed.
Example:
Numeric Result Format
@N9 4,550,000 Nine digits, group with commas (default)
Please note, @N7 picture has default grouping with commas. I think, you need numeric picture with leading zeroes and no grouping here. Hope this helps.
btw Guennadi, why wouldn’t you use following table definitions, still be able to ADD() / PUT() and so on?
CREATE TABLE [dbo].[TestTable](
[SysID] [int] IDENTITY(1,1) NOT NULL,
[CreatedOn] [datetimeoffset](7) NOT NULL,
[ChangedOn] [datetimeoffset](7) NULL,
[SomeString] [char](10) NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[TestTable]
ADD CONSTRAINT [DF_TestTable_CreatedOn] DEFAULT (SYSDATETIMEOFFSET()) FOR [CreatedOn]
GO
CREATE OR ALTER TRIGGER [dbo].[trgAfterUpdate]
ON [dbo].[TestTable]
AFTER UPDATE
AS
BEGIN
SET NOCOUNT ON;
UPDATE [dbo].[TestTable]
SET [ChangedOn] = SYSDATETIMEOFFSET()
FROM [dbo].[TestTable] t
INNER JOIN inserted i ON t.[SysID] = i.[SysID];
END;
GO
TestTable FILE,DRIVER('MSSQL'),PRE(TEST)
Record RECORD,PRE()
SysID LONG,NAME('SysID | READONLY')
CreatedOn STRING(34),NAME('CreatedOn | READONLY')
ChangedOn STRING(34),NAME('ChangedOn | READONLY')
SomeString STRING(10)
END
END
I agree the @N7 has to go, but I think it is probably best to have CreatedOn_Hundreds be a string(7) rather than a number. Clarion is going to give you a two digit decimal, say .88 for the fractional part of the second, and you have to change that to 8800000 which I suppose you could do by treating it as a number and multiplying, but it’s probably safer to take your 88 as ‘88’ and append ‘00000’ to it.
Agreed. It’s a matter of taste knowing desired result 2025-01-15 18:45:08.2334852 +00:00. The confusion comes from Clarion import table wizard creating such an ugly OVERed group. And nobody warns you that CreatedOn_PlusMinus = '+' assignment is wrong. This is because I recommended that MESSAGE() in the first place.
Oleg,
thank you for your notes.
BTW
is a default on the SQL part and I can’t control my column from Clarion in this case, it’s OK when you create a new record, but if you change the record (like you have other ChangedOn 
column ) and you would like to assign all info from Clarion…
and the main problem is how to fill in the last part:
CreatedOn_PlusMinus
CreatedOn_TimeOffset
is there a way to get TimeOffset from Clarion??
Ok, no problem. I updated T-SQL and Clarion table definitions in my post above by.adding a new ChangedOn READONLY field with AFTER UPDATE trigger. Take it as a preferred way to go.
Sure. You asked for “a real Clarion code example” - I am also (lazy) Clarion developer and provided just one line of real clarion code which can help a lot . Try it.
Seriously. When you are asking for a solution here, better provide your real Clarion code example which doesn’t work and you’ll get much better chances for spot on response.
Well, all the answers are given already.
or simple use @N_07 picture instead of @N7
The correct one is CreatedOn_PlusMinus = ' +' ! please note leading space
Update: the database can be updated outside of your Clarion program, hence SQL defaults and triggers is a preferred way to go.
Unfortunately, SQL trigger is not an option for me, I need to control all from Clarion. (but I like your approach as well).
RE my code example. 
I thought that people already had a solution since it should not be first time I discovered that type of SQL column
Lasse,
Did you realise how to work with DateTimeOffset column?
