Delphi TDateTime and MYSQL TDatetime

Hi,

I have a problem to send Delphi TDateTime into a Mysql TDateTime Field.  The result its around 100 years more than supposed...

Any suggestion?

Thanks
FledAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

KristaoCommented:
He this problem is interesting don't realy rememmber why it is so :)

In MSSQL TDateTime differs from delphi by 2 days. You can read datetime normaly but if you ask data from database you eed to take 2 days off from TDateTime i delphi.

var
t:TdateTime;
begin
t:=now;
with ado do
beign
  with sql do
  begin
     append('select *');
     append('from users');
     append('where data='+inttostr(trunc(t)-2));
  end;
open;
end;
end;

duno in mysql but this is the problem in MSSQL mybe its the same in mysql. :)
0
FledAuthor Commented:
Well it seems not the same problem.

When i append or edit a date into mysql database,,,

instead of having "2006-04-25 14:43:00" (wich is the date i saved) , I have "2108-09-04 06:22:39" into mysql database...  Its more than handred years!



0
saravananvgCommented:
Hello Sir,

  I think when you are saving itself the date doesnt go correctly, it saves the default date of the control from where you are sending it. Check how you are saving it to the database.

with regards,
padmaja.
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

wimmeyvaertCommented:
What version of MySQL are you using ?
How do you connect to MySQL ?
What is the DataType of your field (Date,DateTime or TimeStamp) ?
Can you post the code of your insertion into the database ?

Just for a start, so that I can try to simulate your problem on my machine.
0
KristaoCommented:
Try this

Insert into table date 0. And then you will know the date from wich mysql starts counting the dates.

MSSQL start 01/01/1900 equal 0

Delphi start 30/12/1899 equal 0

thats why you need to take off 2 dates from delphi time when you make some sql statment.
0
KristaoCommented:
advise:

check reagional settigs in tab Date in group box calendar it shoud be 1930 and 2029.

Whe you do insert how do you write date it is string but do you write it like this dd/mm/yy or dd/mm/yyyy

if dd/mm/yy then you shoul look in regional settings becouse year is shown in to digi

if dd/mm/yyyy then it shoul work just fine.
0
wimmeyvaertCommented:
I just made a small app which inserts a record into a MySQL-database (Server V5.0.18, Cliznt V5.0.11), and everything works fine.

I placed 2 DateTimePickers on my form, one of Kind 'Date' and one of kind 'Time'.
I then assigned the value to a TIMESTAMP-Field as follows : ADOQuery1.Parameters.ParamByName('TSFIELD').Value    := FormatDateTime( 'yyyy/mm/dd hh:nn:ss', DT );

I then assigned the value to a DATEDTIME-Field as follows : ADOQuery1.Parameters.ParamByName('DTFIELD').Value    := FormatDateTime( 'yyyy/mm/dd', DT );

'DT' is a local var of type DateTime and assigned as follows :DT := StrToDateTime( FormatDateTime('dd/mm/yyyy', dtpDate.Date ) + ' ' + FormatDateTime('hh:nn:ss', dtpTime.Time ) );

Everything is stored correctly into the database.
0
wimmeyvaertCommented:
Remark that I use the FormatDateTime-Function in Delphi to make sure that the DateTime-Value is assigned in the correct way (yyyy/mm/dd hh:nn:ss).
0
wimmeyvaertCommented:
I even have inserted a record (using a TADOQuery) just by assigning a string-value to the Parameter of the DT-Field:
    ADOQuery1.Parameters.ParamByName('TSField').Value    := '2006-04-25 14:43:00';
    ADOQuery1.Parameters.ParamByName('DTField').Value      := '2006-04-25 14:43:00';

TSField is of type TIMESTAMP
DTField is of type DATETIME

The Date/Time is showed correctly in my DBGrid after inserting a record.

So I guess there are 3 possibilities :
1) Your code contains something buggy
2) Your version of MySQL contains bugs
3) The MYSQL-ODBC-Drivers contains bugs.

Therefor : Provide us with Delphi-Version, MySQL-Version, ODBC-Driver-Version.
Can you also post the code you are using in Delphi ?

Best regards,

The Mayor.
0
wimmeyvaertCommented:
Here my complete code of the insert into the database.
I have a TEdit om my Form which is by default filled with : '25/04/2006 14:43:00'.
So the format is 'dd/mm/yyyy hh:nn:ss'.

The code to insert a record into my MYSQL-database is :
procedure TForm1.Button1Click(Sender: TObject);
var
  DT: TDateTime;
begin
  DT := StrToDateTime( Edit1.Text );
  With ADOQuery1 do
  begin
    Close;
    Parameters.ParamByName('DTField').Value := FormatDateTime( 'yyyy/mm/dd hh:nn:ss', DT );
    Parameters.ParamByName('TSField').Value := FormatDateTime( 'yyyy/mm/dd', DT);
    ExecSQL;
    Close;
  end;
end;

This is working perfectly, but only when I respect the DateTime-Format in my TEdit (dd/mm/yyyy hh:nn:ss).

So when I use a format like dd-mm-yyyy hh:nn:ss I get an error 'Not a valid Date and Time'

Maybe it has something to do ith the Format you use.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
FledAuthor Commented:
Hi,

Well, no matter what kind of code i use, its not working (changing the type, converting the value)... The field into mysql is a TDateTime and the field into the object Apparei1 is a TDateTime to.

I use Delphi 7 with MySQL 4.1.18 and DBExpress 3.00.1 from CRLab



           With DataModule1.CDSAppareils do
             begin
                Open;
                Append;
                fieldValues['chr_IDENTIFICATEUR']       := Appareil1.chr_Identificateur;
                fieldvalues['int_AppareilID']                  := Appareil1.int_AppareilID;
                fieldvalues['chr_Manufacturier']            := Appareil1.chr_Manufacturier;
                fieldvalues['chr_NoSerie']                     := Appareil1.chr_NoSerie;
                fieldvalues['chr_NoModele']                  := Appareil1.chr_NoModele;
                fieldValues['flo_PlageBas']                   := Appareil1.flo_PlageUtilBas;
                fieldValues['flo_PlageHaut']                 := Appareil1.flo_PlageUtilHaut;
                fieldvalues['chr_Resolution']                := Appareil1.chr_Resolution;
                fieldvalues['dte_DateEtal']                   := StrToDateTime(FormatDateTime('yyyy-mm-dd hh:mm:ss', Appareil1.chr_DateEtal));
//                FieldByName('dte_DateEtal').Asstring    := DateTimeToStr(Appareil1.chr_DateEtal);
//                fieldvalues['dte_DateEtal']                    := Appareil1.chr_DateEtal;
                fieldvalues['dte_DateEcheance']              := Appareil1.chr_DateReetal;
                fieldvalues['chr_NomLogique']                := Appareil1.chr_NomLogique;
                fieldvalues['chr_Exactitude']                   := Appareil1.chr_Exactitude;
                fieldvalues['chr_APPAREILTYPE']            := Identificateur.Second;
                fieldvalues['chr_AppareilFonctionType']   := Identificateur.First;
                FieldValues['chr_AppareilInterne']          := cVALEURINTERNE;
                FieldValues['bol_Actif']                          := 1;
                Post;
                ApplyUpdates(-1);
             end;

Thanks
0
wimmeyvaertCommented:
I have tested it with a Delphi5 application using MySQL V5 and ADO-Components (using an ODBC Datasource V3.41).

And there it works fine.

If I have time later on, I will investigate it a little bit more.
0
FledAuthor Commented:
Hummm,,

After a few try, i decide to reinstall my database and restore my schema and everything works fine now... i'm suppose it was a corrupted database..

Thank you very much for both of you!
0
wimmeyvaertCommented:
I'm glad you get it to work.
And thanks for the points and the grade.

Best Regards,

The Mayor.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Delphi

From novice to tech pro — start learning today.

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.