1 (edited by blackpearl8534 2019-09-06 15:22:12)

Topic: Date To SQL Datetime

hello Dmitriy... i am using this script to get data from sql server database in inserting in mvd database... how to change datetime data into  sqldatetime...

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=sa;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 := 'iters';
    frm_main.TableGrid1.Columns[7].Header.Caption := 'Amount';
    frm_main.TableGrid1.Columns[8].Header.Caption := 'Meter';


   // set Size
      frm_Main.TableGrid1.Columns[0].Width := 80;
      frm_Main.TableGrid1.Columns[1].Width := 80;
      frm_Main.TableGrid1.Columns[2].Width := 80;
      frm_Main.TableGrid1.Columns[3].Width := 80;
      frm_Main.TableGrid1.Columns[4].Width := 200;
      frm_Main.TableGrid1.Columns[5].Width := 80;
      frm_Main.TableGrid1.Columns[6].Width := 80;
      frm_Main.TableGrid1.Columns[7].Width := 100;
      frm_Main.TableGrid1.Columns[8].Width := 150;



   //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: Date To SQL Datetime

I'm not sure how Sql Server stores dates and times. But SQLite requires the date and time in the following format:  2019-09-06 14:35:44.000


If Sql Server does not store it like this, then you will need to extract and piece together the Sql Server date and time to look like the above format.

Re: Date To SQL Datetime

all database servers store data like YYYY-MM-DD HH:MM:SS but when we retrieve then shows according to system datetime formate... ho to convert any date time type to sql datetime type..

Re: Date To SQL Datetime

blackpearl8534,
In that case then you should be able to load the datetime from Sql Server as is into MVD SQLite. It looks like you are loading it into a string and if the format of that string value is in the YYYY-MM-DD...etc format, then you can insert it into SQLite as is. Are you getting an error or something?