[SOLVED] Changing date stamps
lazarus / 16-Databases

----------------------------------------------------------------------
madref · 2016-01-03 10:03

I am rewriting my database from Access to a Lazarus program with
SQLite.
In Access my dates are stored as '2016-01-03' and now lazarus stores
them as a number (2453288.5).
How can i change all my dates to numbers?


I tried something like this
Code: SQL  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i UPDATE
tbl_WedstrijdenSET Wed_DatumWHERE STRFTIME("%J", Wed_Datum)

----------------------------------------------------------------------
balazsszekely · 2016-01-03 10:42

@madref
SQLite can store DateTime as string, like this: "YYYY-MM-DD
HH:MM:SS.SSS" so  "2016-01-03" it's a perfectly valid date. Why do you
want to change it?

----------------------------------------------------------------------
madref · 2016-01-03 10:44

i want to have a uniform database it's either normal time stamp or
julian timestamp.
And Lazarus stores them as julian's. Thats why i want to change them.
It's the easiest way.


See how one of my queries is now:
Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i   cSQL :=
'' +    'SELECT ' +      'Seizoen, Wed_ID, Wed_Meetellen,
[Scheidsrechter 1] AS Scheidsrechter, ' +      'Wed_Scheids_1 AS
Scheids_ID, Wed_Datum ' +    'FROM qry_Overzicht_Wedstrijden ' +   
'WHERE Wed_Scheids_1 NOT IN (1, 2, 3, 4) ' +    'GROUP BY 1, 2, 3, 4,
5, 6 ' +    'HAVING ' +      '((Wed_Datum Between STRFTIME("%Y-%m-%d",
"' + sv + '") AND STRFTIME("%Y-%m-%d", "' + st +'")) OR' +     
'(Wed_Datum Between STRFTIME("%J", "' + sv + '") AND STRFTIME("%J", "'
+ st +'"))) ' + CHR(10) + CHR(13) +    'UNION SELECT ' +     
'Seizoen, Wed_ID, Wed_Meetellen, [Scheidsrechter 2] AS Scheidsrechter,
' +      'Wed_Scheids_2 AS Scheids_ID, Wed_Datum ' +    'FROM
qry_Overzicht_Wedstrijden ' +    'WHERE Wed_Scheids_2 NOT IN (1, 2, 3,
4) ' +    'GROUP BY 1, 2, 3, 4, 5, 6 ' +    'HAVING ' +     
'((Wed_Datum Between STRFTIME("%Y-%m-%d", "' + sv + '") AND
STRFTIME("%Y-%m-%d", "' + st +'")) OR' +      '(Wed_Datum Between
STRFTIME("%J", "' + sv + '") AND STRFTIME("%J", "' + st +'"))) ' +
CHR(10) + CHR(13) +    'UNION SELECT ' +      'Seizoen, Wed_ID,
Wed_Meetellen, [Scheidsrechter 3] AS Scheidsrechter, ' +     
'Wed_Scheids_3 AS Scheids_ID, Wed_Datum ' +    'FROM
qry_Overzicht_Wedstrijden ' +    'WHERE Wed_Scheids_3 NOT IN (1, 2, 3,
4) ' +    'GROUP BY 1, 2, 3, 4, 5, 6 ' +    'HAVING ' +     
'((Wed_Datum Between STRFTIME("%Y-%m-%d", "' + sv + '") AND
STRFTIME("%Y-%m-%d", "' + st +'")) OR' +      '(Wed_Datum Between
STRFTIME("%J", "' + sv + '") AND STRFTIME("%J", "' + st +'"))) ' +
CHR(10) + CHR(13) +    'UNION SELECT ' +      'Seizoen, Wed_ID,
Wed_Meetellen, [Scheidsrechter 4] AS Scheidsrechter, ' +     
'Wed_Scheids_4 AS Scheids_ID, Wed_Datum ' +    'FROM
qry_Overzicht_Wedstrijden ' +    'WHERE Wed_Scheids_4 NOT IN (1, 2, 3,
4) ' +    'GROUP BY 1, 2, 3, 4, 5, 6 ' +    'HAVING ' +     
'((Wed_Datum Between STRFTIME("%Y-%m-%d", "' + sv + '") AND
STRFTIME("%Y-%m-%d", "' + st +'")) OR' +      '(Wed_Datum Between
STRFTIME("%J", "' + sv + '") AND STRFTIME("%J", "' + st +'")))' +   
';';

----------------------------------------------------------------------
balazsszekely · 2016-01-03 10:56

Quote@madref
i want to have a uniform database it's either normal time stamp or
julian timestamp.Ok. Now try this:
1. Backup your database
2.
Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i  
SQLQuery1.SQL.Text := 'UPDATE tbl_Wedstrijden SET Wed_Datum =
:Wed_Datum WHERE Wed_ID = :Wed_ID';  SQLQuery1.Params.BeginUpdate; 
SQLQuery1.ParamByName('Wed_Datum').AsDateTime := EncodeDate(2015, 01,
03) + EncodeTime(12, 04, 05, 000); 
SQLQuery1.ParamByName('Wed_ID').AsInteger := 25; //give a real Wed_Id
value from database  SQLQuery1.Params.EndUpdate;  SQLQuery1.ExecSQL; 
//ApplyUpdate, Commit, whatever...Now check what is the format of the
date at Wed_ID 25. If the format is correct then you have to loop
through all the records and update the Wed_Datum with the above
method.

----------------------------------------------------------------------
madref · 2016-01-03 13:10

It works...
But if i want the time also (stored in Wed_Time).?

----------------------------------------------------------------------
balazsszekely · 2016-01-03 13:17

Quote@madref
It works...
But if i want the time also (stored in Wed_Time).?
Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i  
SQLQuery1.SQL.Text := 'UPDATE tbl_Wedstrijden SET Wed_Datum =
:Wed_Datum, Wed_Time = :Wed_Time WHERE Wed_ID = :Wed_ID'; 
SQLQuery1.Params.BeginUpdate; 
SQLQuery1.ParamByName('Wed_Datum').AsDateTime := EncodeDate(2015, 01,
03);  SQLQuery1.ParamByName('Wed_Time').AsTime := EncodeTime(12, 04,
05, 000);  SQLQuery1.ParamByName('Wed_ID').AsInteger := 25; //give a
real value from database  SQLQuery1.Params.EndUpdate; 
SQLQuery1.ExecSQL;

----------------------------------------------------------------------
madref · 2016-01-03 13:31

Small final question.
Not all records exist between 1 and 700.
How to make sure that the records where Wed_ID does not exist get
skipped.

----------------------------------------------------------------------
balazsszekely · 2016-01-03 13:53

I though Wed_ID it's a primary key and as such it should always exist.
Anyway:
Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i  
SQLMain.SQL.Text := 'select * from tbl_Wedstrijden';  SQLMain.Open; 
SQLMain.First;  while not SQLMain.EOF do  begin    if (not
SQLMain.FieldByName('Wed_ID').IsNull) and
(SQLMain.FieldByName('Wed_ID').AsString <> '') then    begin      //do
the update here          end;    SQLMain.Next;  end;

----------------------------------------------------------------------
madref · 2016-01-03 13:57

Yes it does but games get also deleted

----------------------------------------------------------------------
balazsszekely · 2016-01-03 14:02

QuoteYes it does but games get also deleted
I'm not following you...if the id does not exist then: "select * from
from tbl_Wedstrijden" will not return anything on non existing id's.
Anyway it works now?

----------------------------------------------------------------------
madref · 2016-01-03 16:06

For instance Wed_id 1 through 9 don't exist an id 237, 315 and a few
more don't exist.
So if i make a for-loop from 1 to 700 i get errors because the
wed_id's dont exist
Hope it's clear now

----------------------------------------------------------------------
balazsszekely · 2016-01-03 16:10

Then Wed_id is not primary key. You have two solution, the one I
mentioned above or:
SQLMain.SQL.Text := 'select * from tbl_Wedstrijden where Wed_id is not
null';

----------------------------------------------------------------------
madref · 2016-01-04 15:05

I did solve it a bit differently because it was a one time thing.
Here is my solution.
Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i  procedure
TForm_Uitbetalen.BT_Rapport_PrintClick(Sender: TObject);var d1, d2,
d3, t1, t2, i: integer;    d,t,a : string;begin 
TQ_Uitbetalen.DataBase := Form_Information.Connect_RefereeDB;  i :=
10;  TQ_Uitbetalen.SQL.Text := 'UPDATE tbl_Wedstrijden SET Wed_Datum =
:Wed_Datum, Wed_Tijd = :Wed_Tijd WHERE Wed_ID = :Wed_ID';  while i <>
601 do begin    case i of      11, 13, 58, 59, 62, 63, 64, 65, 66, 67,
68, 69, 70, 71, 72, 73, 74, 75, 79,      83, 134, 139, 141, 145, 152,
164, 177, 185, 204, 242, 255, 259, 262, 272,      274, 275, 304, 327,
353, 355, 365, 366, 367, 368, 369,      370, 371, 372, 421, 422, 423,
426, 428, 439, 443, 444, 445, 446, 447, 448, 449,      455, 466, 470,
471, 519, 537, 538, 553, 571, 579, 581, 593, 595,      599 : begin  
// exceptions              // ShowMessage (IntToStr(i));             
i := i;            end      else        begin          d :=
MLookup('Wed_Datum','tbl_Wedstrijden','Wed_ID='+ IntToStr(i));       
 t := MLookup('Wed_Tijd','tbl_Wedstrijden','Wed_ID='+ IntToStr(i));  
      d1 := StrToInt (Copy(d, 1, 2));  // maand          d2 :=
StrToInt (Copy(d, 4, 2));  // dag          d3 := StrToInt ('20' +
Copy(d, 7, 2));  // jaar          t1 := StrToInt (Copy(t, 1, 2));  //
uren          t2 := StrToInt (Copy(t, 4, 2));  // minuten   12:15:00
PM          // AM en PM berekenen.          a := Copy(t, 10,2);      
  if a = 'AM' then            begin              if t1 = 12 then t1 :=
0;            end          else begin            if t1 <> 12 then t1
:= t1 + 12;          end;  // if           
TQ_Uitbetalen.Params.BeginUpdate;         
TQ_Uitbetalen.ParamByName('Wed_Datum').AsDateTime := EncodeDate(d3,
d1, d2);          TQ_Uitbetalen.ParamByName('Wed_Tijd').AsTime :=
EncodeTime(t1, t2, 00, 000);         
TQ_Uitbetalen.ParamByName('Wed_ID').AsInteger := i; //give a real
value from database          TQ_Uitbetalen.Params.EndUpdate;         
TQ_Uitbetalen.ExecSQL;          TQ_Uitbetalen.ApplyUpdates;       
end;    end;  // case    i := i + 1;  end;  // while 
Form_Information.Trans_RefereeDB.Commit;end;

----------------------------------------------------------------------
balazsszekely · 2016-01-04 15:20

@madref
Is a brute force solution  , but if it works....

----------------------------------------------------------------------
madref · 2016-01-04 15:27

I know. It's not a beauty, but gets the job done