Hello.

By default my Firebird table has the formatting mm/dd/yyyy for date fields.
In Norway this is somewhat unfamiliar and I want to change it to be 
dd/mm/yyyy from the creation of the database, so I don't have to reformatt 
it by code everywhere I need it.
I have looked at the whole chain from DBGrid to TUniQuery component without 
finding a property that handles this.
Have also tried to set Persistent Fields, but no property seems available 
where I expected it to be.

I can see that the DDL lists it as MyDateField, Date, Default 'TODAY',
Is there some SQL formatting I can do here at creation time, or do I 
overlook a property somewhere?
Using D2009, Firebird, Database WorkBench, UniDac components.

Thanks in advance.

:)
Kai Inge

On 24.06.2010 20:52, Kai Inge Buseth wrote:
> Hello.
>
> By default my Firebird table has the formatting mm/dd/yyyy for date fields.
> In Norway this is somewhat unfamiliar and I want to change it to be
> dd/mm/yyyy from the creation of the database, so I don't have to reformatt
> it by code everywhere I need it.
> I have looked at the whole chain from DBGrid to TUniQuery component without
> finding a property that handles this.
> Have also tried to set Persistent Fields, but no property seems available
> where I expected it to be.
>
> I can see that the DDL lists it as MyDateField, Date, Default 'TODAY',
> Is there some SQL formatting I can do here at creation time, or do I
> overlook a property somewhere?
> Using D2009, Firebird, Database WorkBench, UniDac components.
>
> Thanks in advance.
>
> :)
> Kai Inge


I don't think Firebird has a default format for displaying date fields - 
this is a client "thing".  Using Firebird and DBW on my computer I see 
dates as dd.mm.yyyy (having specified absolutely nothing in particular 
(IIRC!), except Windows (regional) settings).


-- 
Aage J.

"Aage Johansen" <aagjohan@offline.no> skrev i melding 
news:254607@forums.embarcadero.com...
>
> I don't think Firebird has a default format for displaying date fields -
> this is a client "thing".  Using Firebird and DBW on my computer I see
> dates as dd.mm.yyyy (having specified absolutely nothing in particular
> (IIRC!), except Windows (regional) settings).
>
>
> -- 
> Aage J.

Hello Aage.

Thank you for answering me.
The date's are transferred from an old dBaseIV table into my Firebird table.
The formatting is probably from there.
My Windows settings is dd/mm/yyyy.

Perhaps I have to reformat each and every one by code.

:)
Kai Inge

Firbird stores dates internally as the number of dates
since a base date. The date is converted to a month, day
and year and formatted by the client software. There is no
formatting information stored in the database. The only way
the format of the date in the dBase table could affect the
FB database is if the date is stored in the FB database as
a string, not as a DATE datatype. I do not know anything
about UniDac but most client software uses the Windows
regional settings to determine the default date format.

-- 
Bill Todd (TeamB)

"Kai Inge Buseth" <kaiib@broadpark.no> wrote in message 
news:254592@forums.embarcadero.com...
>
> By default my Firebird table has the formatting mm/dd/yyyy for date 
> fields.

No it doesn't. If the field is a *date* or *datetime* field type then there 
is no formatting at all in the database, it is stored as a number. It is 
only the client that does formatting and the default on each client is the 
regional settings for that computer. So given the same database, if two 
clients have different date display settings, they will see the same date 
accordingly.

If there is any component that is not displaying according to your regional 
settings then either your application is *changing* the settings (look for 
ShortDateFormat in code) or else the component has a bug.

Note for the same reason - regional settings - you should never try to 
format a date as a string for use in an SQL statement. For that, 
Firebird/Interbase have a limited set of formats it can handle it it cannot 
handle the ambiguity between 'mm/dd/yyyy' and 'dd/mm/yyyy' (the former is 
the standard for the database).

-- 
Wayne Niddery (TeamB)

"Wayne Niddery" <wniddery@chaffrogers.com> skrev i melding 
news:254653@forums.embarcadero.com...
> "Kai Inge Buseth" <kaiib@broadpark.no> wrote in message
> news:254592@forums.embarcadero.com...
>>
>
> If there is any component that is not displaying according to your 
> regional
> settings then either your application is *changing* the settings (look for
> ShortDateFormat in code) or else the component has a bug.
>
> Wayne Niddery (TeamB)

Thank you for a clarifying answer. It seems that this is a bug in the UniDac 
Grid, because if I change the regional settings then the Grid changes to 
that setting, but only for the date clicked on/ having focus. So when 
scrolling through the whole table, then at last all dates are viewed 
accordingly to regional settings.

The real problem is that when I close down and restart the program, it's 
back to wrong setting and I have to repeat the peculiar procedure to get 
each and every date to view the right setting.
So it seems that I have to address UniDac for this matter.

:)
Kai Inge

"Bill Todd" <no@no.com> skrev i melding 
news:254645@forums.embarcadero.com...
.. The date is converted to a month, day
> and year and formatted by the client software. There is no
> formatting information stored in the database. The only way
> the format of the date in the dBase table could affect the
> FB database is if the date is stored in the FB database as
> a string, not as a DATE datatype. I do not know anything
> about UniDac but most client software uses the Windows
> regional settings to determine the default date format.
>
> -- 
> Bill Todd (TeamB)

Thank you, Bill.
I have learned a lot from this case, but it probably is the UniDac component 
that is tricking me here.
They may have a solution when I contact them.
Forgot to mention that I use Win7, perhaps the potentially bug have 
something to do with that environment.

:)
Kai Inge

"Wayne Niddery" <wniddery@chaffrogers.com> skrev i melding 
news:254653@forums.embarcadero.com...
> "Kai Inge Buseth" <kaiib@broadpark.no> wrote in message
> news:254592@forums.embarcadero.com...
>>
>
> If there is any component that is not displaying according to your 
> regional
> settings then either your application is *changing* the settings (look for
> ShortDateFormat in code) or else the component has a bug.
> Wayne Niddery (TeamB)

Thanks again Wayne.
There was no ShortDateFormat in my code, so where this setting 'm/d/yyyy' is 
coming from I don't know.

Then I suddenly realized that you provided me with the solution:
why not use that function in code to override the mysterious setting?

Now it runs like a dream.
Delphi is wonderful, and you all out there too :-)

:)
Kai Inge

"Of  course, the best solution have been if I could retrieve the regional 
settings from Windows
and force this into the ShortDateFormat function, but I don't know how to do 
this.
Can anyone provide me with the syntax for this?

> :)
> Kai Inge

Kai Inge Buseth wrote:
> "Of  course, the best solution have been if I could retrieve the regional 
> settings from Windows
> and force this into the ShortDateFormat function, but I don't know how to do 
> this.
> Can anyone provide me with the syntax for this?
> 
>> :)
>> Kai Inge

This looks promising.
http://www.rgagnon.com/pbdetails/pb-0181.html

Mike

"Mike Reublin" <nf4l_NO_SPAM@nf4l.com> skrev i melding 
news:254765@forums.embarcadero.com...
> Kai Inge Buseth wrote:
>> "Of  course, the best solution have been if I could retrieve the regional
>> settings from Windows
>
> This looks promising.
> http://www.rgagnon.com/pbdetails/pb-0181.html
>
> Mike

Yes, it did, but I have no Clue how to use it. Where and when to use the 
numbers:
Say ShortDateFormat 33 and then 1 for D M Yr and 36 and 1 for Full century.
Confusing, but thanks anyway. I will look into it.

CONSTANT long LOCALE_SSHORTDATE = 31       '  short date format string
CONSTANT long LOCALE_SLONGDATE = 32        '  long date format string
CONSTANT long LOCALE_STIMEFORMAT = 4099    '  time format string
CONSTANT long LOCALE_IDATE = 33            '  short date format, 0 M-D-Y,1 
D-M-Yr,2 Y-M-D
CONSTANT long LOCALE_ILDATE = 34           '  long date format, 0 M-D-Y,1 
D-M-Y,2 Y-M-D
CONSTANT long LOCALE_ITIME = 35            '  time format, 0 AM/PM 12-hr 
format, 1 24-hr format
CONSTANT long LOCALE_ICENTURY = 36         '  Use full 4-digit century, 0 
Two digit.1 Full century
CONSTANT long LOCALE_ITLZERO = 37          '  leading zeros in time field, 0 
No , 1 yes
CONSTANT long LOCALE_IDAYLZERO = 38        '  leading zeros in day field, 0 
No , 1 yes
CONSTANT long LOCALE_IMONLZERO = 39        '  leading zeros in month field, 
0 No , 1 yes

:)
Kai Inge

> {quote:title=Kai Inge Buseth wrote:}{quote}
> "Of  course, the best solution have been if I could retrieve the regional 
> settings from Windows
> and force this into the ShortDateFormat function, but I don't know how to do 
> this.

ShortDateFormat is not a function. It's a global variable defined in the SysUtils unit (D2007+), and IIRC in the System unit in earlier versions of Delphi. It's initialized when the runtime is started with whatever the Windows regional short date format (LOCALE_SSHORTDATE) is set to automatically.

> Can anyone provide me with the syntax for this?

{code}
ShortDateFormat := 'dd/mm/yy';
{code}

See the Delphi help file topic "ShortDateFormat" for more information.

Edited by: Ken White on Jun 25, 2010 4:22 PM

<Ken White> skrev i melding news:254885@forums.embarcadero.com...
...
>
>> Can anyone provide me with the syntax for this?
>
> {code}
> ShortDateFormat := 'dd/mm/yy';
> {code}
>
> See the Delphi help file topic "ShortDateFormat" for more information.
>
> Edited by: Ken White on Jun 25, 2010 4:22 PM

Thank you.
This part I managed, but was unsure on retrieving the regional settings to 
set the ShortDateFormat accordingly in the program.
(For some reason my program does not follow the regional setting from start 
and I have to force it to by setting ShortDateFormat explicit.)
Thanks anyway.

:)
Kai Inge

Kai Inge Buseth wrote:

> This part I managed, but was unsure on retrieving the regional
> settings to set the ShortDateFormat accordingly in the program.

SysUtils.GetLocaleFormatSettings()


ain

> SysUtils.GetLocaleFormatSettings()
>
>
> ain

Thank you.

I'm getting closer, but the help file did not provide a syntax example on 
this, so I get stuck in missing parameters.

It seemes that TFormatSettings is type of Record and as such should be able 
to handle this syntax:

TFormatSettings() := GetLocaleFormatSettings()

but of course it's not that simple :-)

Do you have a syntax example on this one?

:)
Kai Inge

"Kai Inge Buseth" <kaiib@broadpark.no> wrote in message 
news:254592@forums.embarcadero.com...
> Hello.
>
> By default my Firebird table has the formatting mm/dd/yyyy for date 
> fields.
> In Norway this is somewhat unfamiliar and I want to change it to be 
> dd/mm/yyyy from the creation of the database, so I don't have to reformatt 
> it by code everywhere I need it.
> I have looked at the whole chain from DBGrid to TUniQuery component 
> without finding a property that handles this.
> Have also tried to set Persistent Fields, but no property seems available 
> where I expected it to be.
>
> I can see that the DDL lists it as MyDateField, Date, Default 'TODAY',
> Is there some SQL formatting I can do here at creation time, or do I 
> overlook a property somewhere?
> Using D2009, Firebird, Database WorkBench, UniDac components.
>
> Thanks in advance.
>
> :)
> Kai Inge

Kai,

Your dates will be stored in FB as numbers without any formatting applied. 
You may just be seeing dates in that format depending on whatever you use to 
access the database for design & maintenance purposes (e.g. FlameRobin or 
whatever you use)

When it comes to displaying dates in your Delphi application you can do 2 
things (and I'm sure the CR grid you're using will respect these).

1: Do nothing in the way of trying to format them and allow whatever short 
date format is set up on the PC (via regional settings or whatever it's 
called) to apply throughout. This is preferable since whoever uses it will 
see the dates in a format familiar to themselves. You should be able to 
verify this by changing your own settings and re-starting your application. 
The formats will not persist once you change back to your previous regional 
setting, it will use whatever was in force when the application started. 
Often, if you change formats without closing and re-starting your 
application, you may see some dates in one format and some in another.

2: If for some reason you need to apply a certain fixed format (regardless 
of your users preference :-) ) you can apply this by setting the 
DisplayFormat property for the fields you want changed. Either define 
persistent fields and set the DisplayFormat to mm/dd/yyyy directly or, 
without persistent fields & after opening the dataset write in code:

  TDateField(MyTable.FieldByName('MY_DATE_FIELD')).DisplayFormat := 
'dd-mm-yyyy';

You need the cast since the plain TField returned does not have the 
DisplayFormat property.

3. (I lied above) A 3rd alternative, if it affects all tables with lots of 
date fields you can iterate the fields every time you open any table: e.g.

In the AfterOpen event...

SetDateFormats(DataSet);

procedure TForm3.SetDateFormats(DataSet: TDataSet);
  var
    I: Integer;
begin
  for I := 0 to Pred(DataSet.FieldCount) do
  begin
    if DataSet.Fields[I].DataType = ftDate then
      TDateField(DataSet.Fields[I]).DisplayFormat := 'dd/mm/yyyy';
  end;
end;

Regards, Paul.

Kai Inge Buseth wrote:

> I'm getting closer, but the help file did not provide a syntax
> example on this, so I get stuck in missing parameters.

Somewhere on the Embarcadero website they have a latest documentation
as wiki, you could use it to check for topics which aren't in your
local help.
 
 
> Do you have a syntax example on this one?

Quote from D2010 help:

procedure GetLocaleFormatSettings(LCID: Integer; var FormatSettings:
TFormatSettings);
GetLocaleFormatSettings populates FormatSettings based on the Windows
locale ID specified in LCID.


ain

Paul Hughes wrote:

> When it comes to displaying dates in your Delphi application you can
> do 2 things (and I'm sure the CR grid you're using will respect
> these).
> 
> 1: Do nothing in the way of trying to format them and allow whatever
> short date format is set up on the PC (via regional settings or
> whatever it's called) to apply throughout. This is preferable since
> whoever uses it will see the dates in a format familiar to
> themselves. You should be able to verify this by changing your own
> settings and re-starting your application. The formats will not
> persist once you change back to your previous regional setting, it
> will use whatever was in force when the application started. Often,
> if you change formats without closing and re-starting your
> application, you may see some dates in one format and some in another.

Seting Application.UpdateFormatSettings to False should prevent this.

And SysUtils.GetFormatSettings call could be used to force globals back
to the values chosen in regional settings.


ain

Thanks to all you supporting guys on this one.

:)
Kai Inge