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 2133]

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 yesterday at 12:50 PM by Squall_FF8

[Uwe Raabe 2133]

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 23 hours ago by Squall_FF8

[Uwe Raabe 2133]

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 23 hours ago by Squall_FF8

[Uwe Raabe 2133]

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 22 hours ago by Squall_FF8

[Uwe Raabe 2133]

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 22 hours ago 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 2133]

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 21 hours ago by Olli73