• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1587
  • Last Modified:

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

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
esoftbg
Asked:
esoftbg
  • 12
  • 12
  • 8
  • +4
1 Solution
 
MerijnBSr. Software EngineerCommented:
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
 
2266180Commented:
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
 
Steven-FernandezCommented:
Try:

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

0
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
esoftbgAuthor Commented:
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
 
rfwoolfCommented:
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
 
esoftbgAuthor Commented:
@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
 
esoftbgAuthor Commented:
@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
 
MerijnBSr. Software EngineerCommented:
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
 
esoftbgAuthor Commented:
@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
 
MerijnBSr. Software EngineerCommented:
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
 
rfwoolfCommented:
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
 
MerijnBSr. Software EngineerCommented:
0
 
MerijnBSr. Software EngineerCommented:
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
 
Gary BenadeCommented:
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
 
MerijnBSr. Software EngineerCommented:
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
 
2266180Commented:
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
 
Gary BenadeCommented:
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
 
MerijnBSr. Software EngineerCommented:
> 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
 
esoftbgAuthor Commented:
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
 
MerijnBSr. Software EngineerCommented:
the contents of esEdit_Date, where does this get from?
Is it filled in by the user, or by your software?
0
 
esoftbgAuthor Commented:
esEdit_Date is filled in by the user
0
 
MerijnBSr. Software EngineerCommented:
there is a 'weak spot'. Can't you use a TDateTime picker?
0
 
esoftbgAuthor Commented:
To be clear the software fills today into the esEdit_Date, but the user can change it if needed
0
 
Gary BenadeCommented:
esoftbg, which line of the code is generating the exception, what is the exception message?
0
 
MerijnBSr. Software EngineerCommented:
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
 
Gary BenadeCommented:
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
 
esoftbgAuthor Commented:
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
 
Gary BenadeCommented:
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
 
esoftbgAuthor Commented:
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
 
MerijnBSr. Software EngineerCommented:
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
 
Gary BenadeCommented:
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
 
Gary BenadeCommented:
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
 
MerijnBSr. Software EngineerCommented:
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
 
developmentguruPresidentCommented:
 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
 
esoftbgAuthor Commented:
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
 
Gary BenadeCommented:
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
 
rfwoolfCommented:
esoftbg:
Might be worth a shot, change date separater to a period '.'.
as in 30.1.1926

...dunno but it might work
0
 
esoftbgAuthor Commented:
@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
 
esoftbgAuthor Commented:
@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
 
rfwoolfCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

  • 12
  • 12
  • 8
  • +4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now