Custom Time format in TDBGrid

[Squall_FF8 1]

Hey Guys,

I use a stock TDBGrid (Delphi 12.2) connected to an FDQuerry (MS SQL). One of the field is Hour (time7) and I would like to customize the output to just "hh:mm".
Could you advise how that could be achieved?

[Uwe Raabe 2147]

The simplest way is to create static fields for the query and set the DisplayFormat of the Hour field to hh:nn (Note: mm gives the month).

[Squall_FF8 1]

58 minutes ago, Uwe Raabe said:

The simplest way is to create static fields for the query and set the DisplayFormat of the Hour field to hh:nn (Note: mm gives the month).

I tried (long ago)... for unknown reason the quarry returns WideString instead of Time.
And I dont know how to force the right type ...

Edited May 2 by Squall_FF8

[Uwe Raabe 2147]

29 minutes ago, Squall_FF8 said:

for unknown reason the quarry returns WideString instead of Time.

Seems it is time to get to know the reason now.

[Squall_FF8 1]

4 minutes ago, Uwe Raabe said:

Seems it is time to get to know the reason now.

Hehe easier to say then do 🙂
Any hints? Why all fields are WideString?
BTW that is tFDQuerry connected to tFDConnection.

Edited May 2 by Squall_FF8

[Uwe Raabe 2147]

Can you show the actual SQL for that query?

[Squall_FF8 1]

8 minutes ago, Uwe Raabe said:

Can you show the actual SQL for that query?

sure I can .... but I doubt there is a hint (for a problem) in it: Hour is a field of CD_Main.
Personally I think the problem is from FD engine ... it returns all fields as WideString instead of appropriate type.
 

const
  sql_Base =
    'SELECT '+
    '  m.*, cat.Name Categories, t.Name City,'+
    '  c.Name FirmName, c.EIK FirmEIK, c.Address FirmAddress, '+
    '  c2.Name ToFirmName '+
    'FROM CD_Main m '+
    '  left join CD_Category cat on (cat.ID = m.Category) '+
    '  left join CD_Town t on (t.ID = m.Town) '+
    '  left join CD_Company c on (c.ID = m.Firma) '+
    '  left join CD_Company c2 on (c2.ID = m.ToFirm)';

 

Edited May 2 by Squall_FF8

[Uwe Raabe 2147]

11 minutes ago, Squall_FF8 said:

Personally I think the problem is from FD engine ... it returns all fields as WideString instead of appropriate type.

Well, it does not do that in all of my queries and those of my customers.

 

Can you also show the table definition of CD_Main (the CREATE TABLE command)?

[Squall_FF8 1]

12 minutes ago, Uwe Raabe said:

Well, it does not do that in all of my queries and those of my customers.

 

Can you also show the table definition of CD_Main (the CREATE TABLE command)?

BTW I'm not sure is it related, but the Query and the Fields are dynamic. The columns are static. I did this because  I needed custom labels for titles 

Here is the CD_Main ... As you can see I have plenty of types, yet they all come as WideString (after Open)

CREATE TABLE [dbo].[CD_Main] (
    [ID]           INT            IDENTITY (1, 1) NOT NULL,
    [ForWho]       NVARCHAR (100) NULL,
    [Dates]        DATETIME       NULL,
    [Category]     INT            NULL,
    [Sum]          MONEY          NULL,
    [Notes]        NVARCHAR (200) NULL,
    [Hour]         TIME (7)       NULL,
...
    CONSTRAINT [PK_CD_Main] PRIMARY KEY CLUSTERED ([ID] ASC)
);

 

Edited May 2 by Squall_FF8

[Uwe Raabe 2147]

Works perfectly over here with static fields. Probably something in your setup.

[Squall_FF8 1]

23 minutes ago, Uwe Raabe said:

Works perfectly over here with static fields. Probably something in your setup.

 

The screenshot shows that this is in Design time.
Could you try that at runtime? The connection need to be closed on start, and activated with a button. Also the query to have at least 2 fields (Hour and whatever) (so we have a static and dynamic fields at the same time).

Edited May 2 by Squall_FF8

[Olli73 6]

What are your connection settings?

[Squall_FF8 1]

6 minutes ago, Olli73 said:

What are your connection settings?

Name=Jet
DriverID=MSSQL
Server=192.168.0.169
Database=xxx
User_Name=xxx
Password=xxx

FDConnection is all default - (after you drop it on the form)

[Uwe Raabe 2147]

13 minutes ago, Squall_FF8 said:

Could you try that at runtime?

It also works at runtime.

 

14 minutes ago, Squall_FF8 said:

Also the query to have at least 2 fields (Hour and whatever) (so we have a static and dynamic fields at the same time).

In that case you need to set the FieldOptions.AutoCreateMode of the query to acCombineAlways to create the dynamic fields and the FieldOptions.PositionMode to poFieldNo to get the same order as before.

[Olli73 6]

You could also try to enable ExtendedMetaData for connection.

Edited May 2 by Olli73

[Squall_FF8 1]

Update:
Sorry guys for the delay but we had a break (National holidays).

So I thought that the problem comes from my weird setup: Delphi 12.2, 64 bit application, no design time info for TFDConnection, no SQL for TFDQuerry, all done in run-time.
After @Uwe Raabe showed me tTimeField, I decided to play by the book: a new 32 bit app, (because Delphi IDE is 32) design time connection + query (with SQL) , Auto-created fields in design time. All of the fields got appropriate type (Int, Date got TSQLTimeStamp, Currency,..) except the Hour field:

QryHour: TWideStringField;

 If it matters, the MS SQL is old 2008. I also tried to cheat with static field, but the result was the same (error message after opening the query)

This leads me to the conclusion: Delphi 12.2 has a bug for MS SQL 2008  when the type is time(7). Unfortunately I cant change the type (easily) to experiment with other time versions or even try DateTime for Time portion.


P.S. @Uwe Raabe, I tried your suggestions for mixing static dynamic fields - no result. Sorry but I have to ask - what version is your Delphi and are you using time(7) in MS SQL?

[Lajos Juhász 319]

FireDAC is using the meta information from the connection library to determine the data types. You can try to use cast to change the data type for the field in your test application.

[Squall_FF8 1]

1 hour ago, Lajos Juhász said:

You can try to use cast to change the data type for the field in your test application.

Solid advice!
Since I'm stubborn when I feel I'm so close, I decided to test all types (that make sense) - time, time 0..7 and datetime variants. From the 14 possibilities, only 2 gave me (The rest was tWideString):

QryHour: TSQLTimeStampField;

Browsing MS SQL types page I stubbled upon:

So maybe it is ok to get WideStringField.
On the other hand, Date formats return SQLTimeStamp ...

[Uwe Raabe 2147]

2 hours ago, Squall_FF8 said:

what version is your Delphi and are you using time(7) in MS SQL?

I tried with Delphi 12.3 using SQL Server 2019. The Hour field is definitely time(7) - I used the CREATE TABLE command given by you.

[Stompie 3]

I had something similar 

Check out Q11 on this page: https://docwiki.embarcadero.com/RADStudio/Athens/en/MS_SQL_Server_Questions_(FireDAC)

I would recommend installing ODBC18 on your dev environment, and the machine(s) you deploy to.

[Squall_FF8 1]

5 hours ago, Stompie said:

Check out Q11 on this page:

Whoah that is exactly my case!
Thanks to the help of the guys here, I also came to: "ODBC driver does not recognize these types and maps them to WideStrings"

Unfortunately I dont know how to map, (or is it even possible to change it).
As an easy fix, I just manipulate the string on GetText, it turned out the first 5 chars is what I need.

[Uwe Raabe 2147]

5 hours ago, Stompie said:

I would recommend installing ODBC18 on your dev environment, and the machine(s) you deploy to.

That would only help if you are using a Delphi version that supports it (i.e. Delphi 11.2 or higher).

See https://docwiki.embarcadero.com/RADStudio/Alexandria/en/11_Alexandria_-_Release_2#Updated_FireDAC_Drivers

Quote

FireDAC supports the latest Microsoft ODBC Driver for SQL Server (version 18).