[Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Delphi TDateTime and MYSQL TDatetime

Posted on 2006-04-25
14
Medium Priority
?
4,419 Views
Last Modified: 2008-01-09
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
0
Comment
Question by:Fled
  • 7
  • 3
  • 3
  • +1
14 Comments
 
LVL 1

Expert Comment

by:Kristao
ID: 16536590
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
 

Author Comment

by:Fled
ID: 16536876
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
 
LVL 6

Expert Comment

by:saravananvg
ID: 16541369
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 6

Expert Comment

by:wimmeyvaert
ID: 16541404
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
 
LVL 1

Assisted Solution

by:Kristao
Kristao earned 1000 total points
ID: 16541425
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
 
LVL 1

Expert Comment

by:Kristao
ID: 16541447
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
 
LVL 6

Expert Comment

by:wimmeyvaert
ID: 16541460
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
 
LVL 6

Expert Comment

by:wimmeyvaert
ID: 16541473
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
 
LVL 6

Expert Comment

by:wimmeyvaert
ID: 16541579
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
 
LVL 6

Accepted Solution

by:
wimmeyvaert earned 1000 total points
ID: 16541742
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
 

Author Comment

by:Fled
ID: 16542529
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
 
LVL 6

Expert Comment

by:wimmeyvaert
ID: 16543195
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
 

Author Comment

by:Fled
ID: 16613545
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
 
LVL 6

Expert Comment

by:wimmeyvaert
ID: 16628258
I'm glad you get it to work.
And thanks for the points and the grade.

Best Regards,

The Mayor.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Hello everybody This Article will show you how to validate number with TEdit control, What's the TEdit control? TEdit is a standard Windows edit control on a form, it allows to user to write, read and copy/paste single line of text. Usua…
Introduction Raise your hands if you were as upset with FireMonkey as I was when I discovered that there was no TListview.  I use TListView in almost all of my applications I've written, and I was not going to compromise by resorting to TStringGrid…
this video summaries big data hadoop online training demo (http://onlineitguru.com/big-data-hadoop-online-training-placement.html) , and covers basics in big data hadoop .
Is your OST file inaccessible, Need to transfer OST file from one computer to another? Want to convert OST file to PST? If the answer to any of the above question is yes, then look no further. With the help of Stellar OST to PST Converter, you can e…
Suggested Courses
Course of the Month20 days, 14 hours left to enroll

864 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