?
Solved

An exception when the date format is MM.dd.yyyy

Posted on 2007-10-16
40
Medium Priority
?
1,337 Views
Last Modified: 2013-12-09
My program raised an exception when it is under Windows using date format is MM.dd.yyyy
It does not raise any exception when it is under Windows using date format is dd.MM.yyyy
So my question is: Is any way by Delphi code to change Windows date format to dd.MM.yyyy if it is not dd.MM.yyyy

( Delphi 7.0, Windows XP Professional )
thanks,
Emil
0
Comment
Question by:esoftbg
  • 12
  • 12
  • 8
  • +4
40 Comments
 
LVL 19

Expert Comment

by:MerijnB
ID: 20089008
I think the question is: why does your program raise an exception when the date format is MM.dd.yyyy
do you have any code where this happens?
0
 
LVL 28

Expert Comment

by:2266180
ID: 20089165
the correct approach to datetime conversion is:
1) change the date format string (shortdate globasl variable) to desired string like this:
-  backup old value
- set new value
- convert
- restore old value
thsi method is NOT threadsafe
2) use the TFormatSettings record like:
- declare a variable fo type TFormatSettings
- populate the variable with getformatsettings
- modify the shortdate element to desired value
- covnert using the overloaded version of strtodate or datetostr that takes a TFormatSettings as a second parameter

changing the system format strings is a BAD idea. the suer has set his format strings in order for them to stay like that, not so that any application modifies them as they wish ;)
0
 
LVL 5

Expert Comment

by:Steven-Fernandez
ID: 20089499
Try:

SetLocaleInfo( LOCALE_USER_DEFAULT, LOCALE_SSHORTDATE, 'dd/MM/yyyy' );

0
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 
LVL 12

Author Comment

by:esoftbg
ID: 20090483
I did not tested in depth the problem, but I gues it is in the Firebird database.
My programm sends the date of birth into the Firebird database, and database calculates the age of the patient. If the day of birth is over 12, because Firebird perceives the day as a month, Firebird generates an exception and returns the exception back to the Delphi programm ....
So I need to change the Windows Regional Settings about date Format .../.
So I don't need to change the Delphi's global variable ....
0
 
LVL 13

Expert Comment

by:rfwoolf
ID: 20090609
There are a few places the problem could take place.
For example in SQL, your Firebird has its own implementation of SQL -- you could look at how Firebird's SQL handles timeformats. Then your dataformat itself - the field type would be Date, but you'd have to check with your database language to see how it wants its date format for a field of type Date.
It's possible it might say "Whatever the server's regional settings dictate" But that's no good because all your client PCs might be on a different regional setting. So you have to insist on a uniform date format.

One method is to always convert dates into an integer .
Another method is to always store the date as a string - because in Delphi you can say DateTimeToStr and use its thread-safe format syntax (see delphi's helpfile on DateTimeToStr) and thereby you control the conversion of date to data.

To echo what someone else said - changing the date settings on someone's PC is not best-practise and not ideal for the user either.
0
 
LVL 12

Author Comment

by:esoftbg
ID: 20090642
@Steven-Fernandez,
I guess I need to check the current Date Format may be using:
GetLocaleInfo( .... );
and only if the date format is different from 'dd/MM/yyyy', to execute:
SetLocaleInfo( LOCALE_USER_DEFAULT, LOCALE_SSHORTDATE, 'dd/MM/yyyy' );
0
 
LVL 12

Author Comment

by:esoftbg
ID: 20090687
@rfwoolf,
My Delphi programm is for dentists practiced in Bulgaria under the Bulgarian health law ....
The Firebird database calculates the age of the patient because the patients under 18 years have different health rights than the patients over 18 years age under Bulgarian health law ....
So I think my Delphi programm is only for Bulgaria, and it is possible to change Date settings, may be after ask the PC owner ....
0
 
LVL 19

Expert Comment

by:MerijnB
ID: 20091075
esoftbg, changing the regional settings because your program 'needs' it is not a good idea.
Instead of solving the problem, you are trying to solve side-effects of the problem.

You are now probably using a DateToStr() or DateTimeToStr() when you pass a date value to the FireFox database, is this correct?
0
 
LVL 12

Author Comment

by:esoftbg
ID: 20091132
@MerijnB,
it is not correct, that you think I am using a DateToStr() or DateTimeToStr(), below is a row of my Delphi code:
              IBModule.IBDS_Patients_Manipuls.FIeldByName('DATE_OF_MANIPUL').AsDateTime := vDate;

I have no time yet to find the real cause, but I continue to believe it is internal into Firebird database ....
0
 
LVL 19

Expert Comment

by:MerijnB
ID: 20091143
if it's internal in Firebird, changing your internal date format won't fix it.

Since you are setting the date as a TDateTime (which is a double), the format doesn't matter at all.
0
 
LVL 13

Expert Comment

by:rfwoolf
ID: 20091149
esoftbg:
Forgive me if I'm mistaken, but...
 I thought that AsDateTime was a readonly value that cannot be assigned.
Just as you cannot say FieldByName('MyField').AsString := 'Hello' ,
so too you should not be able to say
FieldByName('DATE_OF_MANIPUL').AsDateTime := vDate;

Is that rasing any errors?
0
 
LVL 19

Expert Comment

by:MerijnB
ID: 20091195
0
 
LVL 19

Expert Comment

by:MerijnB
ID: 20091199
from: http://www.firebirdsql.org/doc/contrib/FirebirdDateLiterals.html

"Note that Firebird does not honor the Windows or Linux date locale format when interpreting date literals. Its interpretation of all-number date formats is decided by the separator character. When dot (.) is used as separator, Firebird interprets it as the non-U.S. notation DD.MM, whereas with any other separator it assumes the U.S. MM/DD notation."


Your problem seems to be something else.
0
 
LVL 19

Expert Comment

by:Gary Benade
ID: 20091293
Your problem has nothing to do with firebird, it has to do with the TDateTime class.
When you call .AsDateTime you are asking delphi to set a DateTime object to whatever type you supply. If you supply a TDateTime, float or integer there will be no problem because these types are absolute date values that require no conversion, the problem starts when you supply a string value because that has to be parsed into a date and since that could be in any format you have to tell delphi what to expect first, this is done by setting these global variables before calling the AsDateTime function:
ShortDateFormat:='dd.mm.yyyy';
DateSeperator:='.';
Im not a pascal programmer so there may be syntax errors above, searching delphis help for ShortDateFormat should sort that out.
You could also use a proper calendar component and then supply its Date property as a parameter to the function
0
 
LVL 19

Expert Comment

by:MerijnB
ID: 20091425
sorry hobbit72, but that is utter nonsense :)

.AsDateTime will only accept either a string or a TDateTime, not both. Seeing the name, it will only accept a TDateTime and thus no conversion is done at all.

Besides, as explained above, changing the shortdateformat so your application will work is proof of not being able to design good applications.

_if_ there is a datetime to string conversion somewhere, it's done by essoftbg self, and then _that_ is the place to solve this problem, _not_ by changing the date format.
0
 
LVL 28

Expert Comment

by:2266180
ID: 20091488
actually a datetime to string is beeing done when the database component is generating the sql. only that it is a variant to string, which variant is a datetime and hence the datetime to string conversion.

but in any case, you will get to the same conclusion I said initially: make a correct conversion.
now, I never worked with firebird so I can't say exactly when the conversion will occure or how it does it, but I suspect that it will use the single parameter form of the function and hence, for this scenario, the ugly method of setting the delphi global variable needs to be used.
the laternativ solution, which I preffer, is to configure the parameter to be of type string and use asstring and do the conversion via the 2 parameter form of the function using a tformatsettings variable modified accordingly.
I can say that my preffered method is to work with variants all over the place and use string to represent non-standard datatypes (like datetime) and convert to string as needed. and I prefer this, because I alredy have a system written which works without modifications on any db I tried: ms access, mysql, ms sql.
0
 
LVL 19

Expert Comment

by:Gary Benade
ID: 20091588
Merlin, thanks for pointing that out,  I meant to refer to TDateTime and not AsDateTime when I was explaining the initialisation process, need more coffee.

In esoftbgs example: FieldByName('DATE_OF_MANIPUL').AsDateTime := vDate; I assume that vDate is a datetime variable so that can't be the problem, it must be when a DateTime object is getting initialised with a string in a format it doesnt understand. ie vDate = StrToDate('10.12.2007');

Setting shortdateformat is valid and will solve the problem.
0
 
LVL 19

Expert Comment

by:MerijnB
ID: 20091671
> Setting shortdateformat is valid and will solve the problem.

I think we just disagree here, in my opinion changing the ShortDateFormat will fix a side effect of the real problem, where the real problem is that the date time format provided (in your case 10.12.2007) is wrong. Changing ShortDateFormat might as well break something somewhere else in the application if that part also assumes the shortdateformat is set to it's own needs.
0
 
LVL 12

Author Comment

by:esoftbg
ID: 20091735
function Check_Date(S: string; var vDate: TDateTime): Boolean;
begin
  Result := True;
  try
    vDate := StrToDate(S);
  except
    Result := False;
  end;
end;

procedure TForm_PATIENTS_MANIPULS.spb_PostClick(Sender: TObject);
var
  vDate:  TDateTime;
  ....
begin
  ....
  if Check_Date(esEdit_Date.Text, vDate) then
  begin
    ....
        IBModule.IBDS_Patients_Manipuls.Append;
    ....
            IBModule.PROC_CHECK_ALLOW_MANIPUL_.ParamByName('I_DATE_OF_MANIPUL').AsDateTime := vDate;
        IBModule.Do_Before_Post;
    ....
  end;
  ....
end;
0
 
LVL 19

Expert Comment

by:MerijnB
ID: 20091747
the contents of esEdit_Date, where does this get from?
Is it filled in by the user, or by your software?
0
 
LVL 12

Author Comment

by:esoftbg
ID: 20091761
esEdit_Date is filled in by the user
0
 
LVL 19

Expert Comment

by:MerijnB
ID: 20091768
there is a 'weak spot'. Can't you use a TDateTime picker?
0
 
LVL 12

Author Comment

by:esoftbg
ID: 20091772
To be clear the software fills today into the esEdit_Date, but the user can change it if needed
0
 
LVL 19

Expert Comment

by:Gary Benade
ID: 20091784
esoftbg, which line of the code is generating the exception, what is the exception message?
0
 
LVL 19

Expert Comment

by:MerijnB
ID: 20091788
but this means that the user can 'mess up' the date :)
Take a look at the TDateTime picker, using this you at least won't need StrToDate(), which is a risc to use.
0
 
LVL 19

Expert Comment

by:Gary Benade
ID: 20091797
If edEdit_Date was a proper calendar component, you could just do:
IBModule.PROC_CHECK_ALLOW_MANIPUL_.ParamByName('I_DATE_OF_MANIPUL').AsDateTime := esEdit_Date.Date
0
 
LVL 12

Author Comment

by:esoftbg
ID: 20091798
This programm works since 2006 witout problems, but the HDD of the computer of my customer stopped to work correctly, and my customer replacet the HDD with a new one.
It was installed a new Windows XP with Regional settings not for Bulgaria .... Date Format different from dd.MM.yyyy
0
 
LVL 19

Expert Comment

by:Gary Benade
ID: 20091811
Since your pressed for time, in the Application constructor code set
ShortDateFormat:='dd.mm.yyyy';
DateSeperator:='.';
and your code will work fine as is regardless of the users locale settings.
0
 
LVL 12

Author Comment

by:esoftbg
ID: 20091833
My problem is not with Delphi
The Firebird database returns an exception and the Delphi program can not make the post into the Database
I just like to check current ShortDate Format into the Windows and if it is different from dd.MM.yyyy, to change Regional Settings of the Windows XP with Bulgarian Date Format dd.MM.yyyy

ShortDateFormat:='dd.mm.yyyy'; would not correct the problem.
0
 
LVL 19

Expert Comment

by:MerijnB
ID: 20091845
Changing the global date format is upmost evil :) That is something your really really should never do!
Besides that, I can not believe that this is actually your problem, see from my post above:


from: http://www.firebirdsql.org/doc/contrib/FirebirdDateLiterals.html

"Note that Firebird does not honor the Windows or Linux date locale format when interpreting date literals. Its interpretation of all-number date formats is decided by the separator character. When dot (.) is used as separator, Firebird interprets it as the non-U.S. notation DD.MM, whereas with any other separator it assumes the U.S. MM/DD notation."
0
 
LVL 19

Expert Comment

by:Gary Benade
ID: 20091914
The date is getting checked before passing it as a parameter so if the code gets as far as a the post we can assume the date is valid, the only place a date related exeption should occur is in the date check function and thats being caught. If its a firebird error you'll get an ISC error, what is the error message you are getting?
0
 
LVL 19

Expert Comment

by:Gary Benade
ID: 20091955
Merlin, I know that shortdateformat is the devils linux login password and that calls to it should be auto commented out by compilers as soon as possible ;), but it only changes the date format the exe sees meaning that if your code is less than perfect it does lend some predictability IMO.  Personally, I havent used it in years, I just saw it again when I was reading the help file on TDateTime and StrToDate in BCB.
0
 
LVL 19

Expert Comment

by:MerijnB
ID: 20091981
in your own application something you should avoid because it mostly means a design flaw somewhere else, but changing the global format, as esoftbg suggest is just foolish.
0
 
LVL 21

Expert Comment

by:developmentguru
ID: 20093090
 I am late to this conversation, but I can tell you that (from my experience on this site) I tend to like what I am hearing from both MerijnB and ciuly.  I personally bypass this problem entirely by writing the update SQL statement myself (having the code generate it).  Here is some pseudo code.

if RecordExists(Dataset, IDFieldName)  then
  SQL := GetUpdateQuery(Dataset, IDFieldName)
else
  SQL := GetInsertQuery(Dataset, IDFieldName)

Query.SQL.Text := SQL;
Query.ExecSQL;

  The three routines (RecordExists, GetUpdateQuery, GetInsertQuery) you would need to provide.  As long as your tables have unique ID fields this should be no real chore.  One of the benefits of doing things this way is that any error comes directly from the database (no confusion as to what is causing the issue when there is one).  Another benefit is that updating entire business objects ( I.E. a PO with associated comments, line items, line item comments, etc) can be sent as a batch of SQL commands surrounded by Begin Transaction and Commit Transaction (so it is either all or none).  If you want I can post some code I use.  Let me know.
0
 
LVL 12

Author Comment

by:esoftbg
ID: 20093941
The error message is:
''.... raised exception class EIBInterBaseError with message 'conversation error from string "30/1/1926"'. Process stopped. Use Step or Run to continue."
This exception raises only when I set the Date Format of my computer to 'MM/dd/yyyy' or 'dd/MM/yyyy'.
Otherwise when I set the Date Format of my computer to 'dd.MM.yyyy' or 'MM.dd.yyyy' there is not exception.
So obviously the cause for the exception is the DateSeparator '/'
DateSeparator '.' does not raise any exception into FireBird Database ....
0
 
LVL 19

Accepted Solution

by:
Gary Benade earned 1000 total points
ID: 20094323
If you add this code to your main forms oncreate event, does anything change?

procedure TForm1.FormCreate(Sender: TObject);
begin
  ShortDateFormat:='dd.mm.yyyy';
  DateSeparator:='.';
end;
0
 
LVL 13

Expert Comment

by:rfwoolf
ID: 20094469
esoftbg:
Might be worth a shot, change date separater to a period '.'.
as in 30.1.1926

...dunno but it might work
0
 
LVL 12

Author Comment

by:esoftbg
ID: 20094539
@hobbit72,
I kno that perfectly, but the problem is outside Delphi code ( outside executable file created with Delphi compilation ).
Delphi executable sends to FireBird Database some data to be stored into the Database - but into Database is some StoredProcedure, and some Triggers. Tere is some manipulation of the data which raises an exception - and data is not stored.
For now I can not debug the processes into the Database, and don't know the cause for the exception.
I only can avoid the exception from the FireBird Database using DateFormat 'dd.MM.yyyy', or DateFormat 'MM.dd.yyyy', but GLOBAL for WINDOWS, not only for Delphi executable.
0
 
LVL 12

Author Comment

by:esoftbg
ID: 20094699
@hobbit72,
you are right.
I am a fool.
The problem is not into Delphi code, but when Delphi sends into the Database DateSeparator := '.' instead of '/' there is no problem.
Thank you very much !
Emil.
0
 
LVL 13

Expert Comment

by:rfwoolf
ID: 22592789
Ahuh... I am having this problem now. The Firebird server deals with dates in its SQL according to the date-separator. 12/01/2008 uses '/' which tells it to use US date format so that is 1st December 2008. If you send 12.01.2008 which uses '.' tells it to use DD/MM/YYYY format which will be interpretted as 12th January 2008
What's very relevant here are SQL statements (and indeed if you use certain Firebird DataComponents (such as ZEOS)) everything gets sent as SQL statements.
In my case sending a manual SQL statement of

ParamByName('MYTODAY').AsDate := today;
Select * from "events" WHERE events.StartTime < :MYTODAY ORDER by StartTime;
will use DELPHI APPLICATION's dateformat, in my case '/' separator which tells it to use US format.
0

Featured Post

Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This post contains step-by-step instructions for setting up alerting in Percona Monitoring and Management (PMM) using Grafana.
In this blog post, we’ll look at how using thread_statistics can cause high memory usage.
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …
Suggested Courses

862 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question