1 (edited by blackpearl8534 2019-09-11 14:32:40)

Topic: convert datetime

how to convert this datetime to sqldatetime

Post's attachments

Attachment icon untitled.JPG 99.68 kb, file has never been downloaded. 

2 (edited by tcoton 2019-09-11 15:14:35)

Re: convert datetime

what is your code to retrieve the datetime from SQL Server? And how do your store the MVDB data - MySQL or Sqlite?

Re: convert datetime

procedure frm_main_Button1_OnClick (Sender: TObject; var Cancel: boolean);
var
  Conn, rs : Variant;
  db_settings, id,HoseId,Price,DateTime,Volume,Value,GradeName,Meter,Pumpid: String;
  //DateTime : TDateTime;
  i,j: int;
var NxTextColumn: TNxTextColumn;
begin
   {***  create database objects ****  }
   Conn :=createoleobject('ADODB.Connection') ; //
   rs := createoleobject('ADODB.recordset');
   {** open database connection ***  }
  db_settings:='Provider=SQLOLEDB;Server=localhost;database=XXXXXXX;UID=xxx;PWD=';    //connection to database
   Conn.Open(db_settings);    //connection to database
   { query to get data}
   rs.Open  ('Select hd.Delivery_ID,hd.Hose_ID,g.Grade_ID,p.Pump_Id,(hd.Completed_TS),hd.Delivery_Volume,hd.Delivery_Value,hd.Del_Sell_Price,hd.Hose_Meter_Volume from Hose_Delivery hd inner join Hoses h on hd.Hose_ID=h.Hose_ID inner join Pumps p on p.Pump_ID=h.Pump_ID inner join Grades g on g.Grade_ID=h.Grade_ID where hd.Delivery_ID >(select Max(Delivery_ID-1) from Hose_Delivery) ORDER BY hd.Delivery_ID DESC', Conn);
    //create table information
    try frm_main.TableGrid1.Columns.Clear; except end;
    try frm_main.TableGrid1.Columns.Add(TNxTextColumn); except end;
    try frm_main.TableGrid1.Columns.Add(TNxTextColumn); except end;
    try frm_main.TableGrid1.Columns.Add(TNxTextColumn); except end;
    try frm_main.TableGrid1.Columns.Add(TNxTextColumn); except end;
    try frm_main.TableGrid1.Columns.Add(TNxTextColumn); except end;
    try frm_main.TableGrid1.Columns.Add(TNxTextColumn); except end;
    try frm_main.TableGrid1.Columns.Add(TNxTextColumn); except end;
    try frm_main.TableGrid1.Columns.Add(TNxTextColumn); except end;
    try frm_main.TableGrid1.Columns.Add(TNxTextColumn); except end;


    //header name
    frm_main.TableGrid1.Columns[0].Header.Caption := 'S/No';
    frm_main.TableGrid1.Columns[1].Header.Caption := 'HoseID';
    frm_main.TableGrid1.Columns[2].Header.Caption := 'PumpID';
    frm_main.TableGrid1.Columns[3].Header.Caption := 'GradeID';
    frm_main.TableGrid1.Columns[4].Header.Caption := 'DateTime';
    frm_main.TableGrid1.Columns[5].Header.Caption := 'Price';
    frm_main.TableGrid1.Columns[6].Header.Caption := 'Liters';
    frm_main.TableGrid1.Columns[7].Header.Caption := 'Amount';
    frm_main.TableGrid1.Columns[8].Header.Caption := 'Meter';

   


   //initialized i
   i:=0;
   //while loop to get data from db
   while not rs.Eof do
    begin
        Application.ProcessMessages;
         //retrieve data and stored them in variables
         id := trim(rs.Fields['Delivery_ID']);
         HoseId := trim(rs.Fields['Hose_ID']);
         Pumpid:=trim(rs.Fields['Pump_ID']);
         Meter:=trim(rs.Fields['Hose_Meter_Volume']);
         GradeName:=trim(rs.Fields['Grade_ID']);
         DateTime := trim((rs.Fields['Completed_TS']));
         Price:=trim(rs.Fields['Del_Sell_Price']);
         Volume:=trim(rs.Fields['Delivery_Volume']);
         Value:=trim(rs.Fields['Delivery_Value']);

         // SQL Query To insert/update data in database  and Condition to avoid duplicate data

         if (SQLExecute('Select Max(id) From tblHoseDelivery') < id) then

          begin
          SQLExecute ('INSERT INTO tblHoseDelivery'+
          '(id,id_tblHose,id_tblPump,Hose_Meter_Volume,id_tblGrade,Completed_TS,Del_Sell_Price,Delivery_Volume,Delivery_Value)' +
          'VALUES("'+id+'","'+Hoseid+'","'+Pumpid+'","'+Meter+'","'+GradeName+'","'+DateTime+'","'+Price+'","'+Volume+'","'+Value+'")');
         end ;

        //generate the data in Table Grid
        frm_main.TableGrid1.AddRow(1);
        frm_main.TableGrid1.Cells[0,i]:=id;
        frm_main.TableGrid1.Cells[1,i]:= Hoseid;
        frm_main.TableGrid1.Cells[2,i]:=Pumpid;
        frm_main.TableGrid1.Cells[3,i]:=GradeName;
        frm_main.TableGrid1.Cells[4,i]:=DateTime;
        frm_main.TableGrid1.Cells[5,i]:=Price;
        frm_main.TableGrid1.Cells[6,i]:=Volume;
        frm_main.TableGrid1.Cells[7,i]:=Value;
        frm_main.TableGrid1.Cells[8,i]:=Meter;
        i:=i+1; //increment i
        rs.movenext ;   //next record
    end;
     //close connection
     rs.Close;
     Conn.Close;
end;

Re: convert datetime

To convert the DateTime value at the top of your form to a database format you can do the following:

DB_Date := FormatDateTime('yyyy-mm-dd hh:nn:ss.zzz',Form1.DateTimePicker1.DateTime);

If you want to convert a DB date to a DateTime value then you can do the following:

Form1.DateTimePicker1.DateTime := SQLDateTimeToDateTime(DB_DateTime_Value);

Re: convert datetime

If I remember well, there might be formatting issues when importing datetime values from one database system to another. Are they stored the same way in both databases? (source-destination).
Is the destination field a datetime or text?
The weird thing is that there is no value in the column "Date Time" of your grid.

Re: convert datetime

I found way...
select convert(varchar(25),datetimefield,121) as datetimefield