Recent

Author Topic: [SOLVED] Changing date stamps  (Read 3979 times)

madref

  • Hero Member
  • *****
  • Posts: 700
  • ..... A day not Laughed is a day not Lived !!
    • Nursing With Humour
[SOLVED] Changing date stamps
« on: January 03, 2016, 11:03:17 am »
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]
  1. UPDATE tbl_Wedstrijden
  2. SET Wed_Datum
  3. WHERE STRFTIME("%J", Wed_Datum)
  4.  
« Last Edit: January 20, 2016, 03:03:33 pm by madref »
You treat a disease, you win, you lose.
You treat a person and I guarantee you, you win, no matter the outcome.

Lazarus 2.0.2 / FPC 3.0.4
Lazarus Trunc / FPC 3.0.4
Mac OS X Mojave

GetMem

  • Hero Member
  • *****
  • Posts: 3515
Re: Changing date stamps
« Reply #1 on: January 03, 2016, 11:42:58 am »
@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

  • Hero Member
  • *****
  • Posts: 700
  • ..... A day not Laughed is a day not Lived !!
    • Nursing With Humour
Re: Changing date stamps
« Reply #2 on: January 03, 2016, 11:44:55 am »
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]
  1.   cSQL := '' +
  2.     'SELECT ' +
  3.       'Seizoen, Wed_ID, Wed_Meetellen, [Scheidsrechter 1] AS Scheidsrechter, ' +
  4.       'Wed_Scheids_1 AS Scheids_ID, Wed_Datum ' +
  5.     'FROM qry_Overzicht_Wedstrijden ' +
  6.     'WHERE Wed_Scheids_1 NOT IN (1, 2, 3, 4) ' +
  7.     'GROUP BY 1, 2, 3, 4, 5, 6 ' +
  8.     'HAVING ' +
  9.       '((Wed_Datum Between STRFTIME("%Y-%m-%d", "' + sv + '") AND STRFTIME("%Y-%m-%d", "' + st +'")) OR' +
  10.       '(Wed_Datum Between STRFTIME("%J", "' + sv + '") AND STRFTIME("%J", "' + st +'"))) ' + CHR(10) + CHR(13) +
  11.     'UNION SELECT ' +
  12.       'Seizoen, Wed_ID, Wed_Meetellen, [Scheidsrechter 2] AS Scheidsrechter, ' +
  13.       'Wed_Scheids_2 AS Scheids_ID, Wed_Datum ' +
  14.     'FROM qry_Overzicht_Wedstrijden ' +
  15.     'WHERE Wed_Scheids_2 NOT IN (1, 2, 3, 4) ' +
  16.     'GROUP BY 1, 2, 3, 4, 5, 6 ' +
  17.     'HAVING ' +
  18.       '((Wed_Datum Between STRFTIME("%Y-%m-%d", "' + sv + '") AND STRFTIME("%Y-%m-%d", "' + st +'")) OR' +
  19.       '(Wed_Datum Between STRFTIME("%J", "' + sv + '") AND STRFTIME("%J", "' + st +'"))) ' + CHR(10) + CHR(13) +
  20.     'UNION SELECT ' +
  21.       'Seizoen, Wed_ID, Wed_Meetellen, [Scheidsrechter 3] AS Scheidsrechter, ' +
  22.       'Wed_Scheids_3 AS Scheids_ID, Wed_Datum ' +
  23.     'FROM qry_Overzicht_Wedstrijden ' +
  24.     'WHERE Wed_Scheids_3 NOT IN (1, 2, 3, 4) ' +
  25.     'GROUP BY 1, 2, 3, 4, 5, 6 ' +
  26.     'HAVING ' +
  27.       '((Wed_Datum Between STRFTIME("%Y-%m-%d", "' + sv + '") AND STRFTIME("%Y-%m-%d", "' + st +'")) OR' +
  28.       '(Wed_Datum Between STRFTIME("%J", "' + sv + '") AND STRFTIME("%J", "' + st +'"))) ' + CHR(10) + CHR(13) +
  29.     'UNION SELECT ' +
  30.       'Seizoen, Wed_ID, Wed_Meetellen, [Scheidsrechter 4] AS Scheidsrechter, ' +
  31.       'Wed_Scheids_4 AS Scheids_ID, Wed_Datum ' +
  32.     'FROM qry_Overzicht_Wedstrijden ' +
  33.     'WHERE Wed_Scheids_4 NOT IN (1, 2, 3, 4) ' +
  34.     'GROUP BY 1, 2, 3, 4, 5, 6 ' +
  35.     'HAVING ' +
  36.       '((Wed_Datum Between STRFTIME("%Y-%m-%d", "' + sv + '") AND STRFTIME("%Y-%m-%d", "' + st +'")) OR' +
  37.       '(Wed_Datum Between STRFTIME("%J", "' + sv + '") AND STRFTIME("%J", "' + st +'")))' +
  38.     ';';
  39.  
You treat a disease, you win, you lose.
You treat a person and I guarantee you, you win, no matter the outcome.

Lazarus 2.0.2 / FPC 3.0.4
Lazarus Trunc / FPC 3.0.4
Mac OS X Mojave

GetMem

  • Hero Member
  • *****
  • Posts: 3515
Re: Changing date stamps
« Reply #3 on: January 03, 2016, 11:56:42 am »
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]
  1.   SQLQuery1.SQL.Text := 'UPDATE tbl_Wedstrijden SET Wed_Datum = :Wed_Datum WHERE Wed_ID = :Wed_ID';
  2.   SQLQuery1.Params.BeginUpdate;
  3.   SQLQuery1.ParamByName('Wed_Datum').AsDateTime := EncodeDate(2015, 01, 03) + EncodeTime(12, 04, 05, 000);
  4.   SQLQuery1.ParamByName('Wed_ID').AsInteger := 25; //give a real Wed_Id value from database
  5.   SQLQuery1.Params.EndUpdate;
  6.   SQLQuery1.ExecSQL;
  7.   //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

  • Hero Member
  • *****
  • Posts: 700
  • ..... A day not Laughed is a day not Lived !!
    • Nursing With Humour
Re: Changing date stamps
« Reply #4 on: January 03, 2016, 02:10:40 pm »
It works...
But if i want the time also (stored in Wed_Time).?
You treat a disease, you win, you lose.
You treat a person and I guarantee you, you win, no matter the outcome.

Lazarus 2.0.2 / FPC 3.0.4
Lazarus Trunc / FPC 3.0.4
Mac OS X Mojave

GetMem

  • Hero Member
  • *****
  • Posts: 3515
Re: Changing date stamps
« Reply #5 on: January 03, 2016, 02:17:03 pm »
Quote
@madref
It works...
But if i want the time also (stored in Wed_Time).?
Code: Pascal  [Select]
  1.   SQLQuery1.SQL.Text := 'UPDATE tbl_Wedstrijden SET Wed_Datum = :Wed_Datum, Wed_Time = :Wed_Time WHERE Wed_ID = :Wed_ID';
  2.   SQLQuery1.Params.BeginUpdate;
  3.   SQLQuery1.ParamByName('Wed_Datum').AsDateTime := EncodeDate(2015, 01, 03);
  4.   SQLQuery1.ParamByName('Wed_Time').AsTime := EncodeTime(12, 04, 05, 000);
  5.   SQLQuery1.ParamByName('Wed_ID').AsInteger := 25; //give a real value from database
  6.   SQLQuery1.Params.EndUpdate;
  7.   SQLQuery1.ExecSQL;

madref

  • Hero Member
  • *****
  • Posts: 700
  • ..... A day not Laughed is a day not Lived !!
    • Nursing With Humour
Re: Changing date stamps
« Reply #6 on: January 03, 2016, 02:31:53 pm »
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.
You treat a disease, you win, you lose.
You treat a person and I guarantee you, you win, no matter the outcome.

Lazarus 2.0.2 / FPC 3.0.4
Lazarus Trunc / FPC 3.0.4
Mac OS X Mojave

GetMem

  • Hero Member
  • *****
  • Posts: 3515
Re: Changing date stamps
« Reply #7 on: January 03, 2016, 02:53:18 pm »
I though Wed_ID it's a primary key and as such it should always exist. Anyway:
Code: Pascal  [Select]
  1.   SQLMain.SQL.Text := 'select * from tbl_Wedstrijden';
  2.   SQLMain.Open;
  3.   SQLMain.First;
  4.   while not SQLMain.EOF do
  5.   begin
  6.     if (not SQLMain.FieldByName('Wed_ID').IsNull) and (SQLMain.FieldByName('Wed_ID').AsString <> '') then
  7.     begin
  8.       //do the update here      
  9.     end;
  10.     SQLMain.Next;
  11.   end;

madref

  • Hero Member
  • *****
  • Posts: 700
  • ..... A day not Laughed is a day not Lived !!
    • Nursing With Humour
Re: Changing date stamps
« Reply #8 on: January 03, 2016, 02:57:43 pm »
Yes it does but games get also deleted :)
You treat a disease, you win, you lose.
You treat a person and I guarantee you, you win, no matter the outcome.

Lazarus 2.0.2 / FPC 3.0.4
Lazarus Trunc / FPC 3.0.4
Mac OS X Mojave

GetMem

  • Hero Member
  • *****
  • Posts: 3515
Re: Changing date stamps
« Reply #9 on: January 03, 2016, 03:02:17 pm »
Quote
Yes 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?
« Last Edit: January 03, 2016, 03:04:15 pm by GetMem »

madref

  • Hero Member
  • *****
  • Posts: 700
  • ..... A day not Laughed is a day not Lived !!
    • Nursing With Humour
Re: Changing date stamps
« Reply #10 on: January 03, 2016, 05:06:22 pm »
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
You treat a disease, you win, you lose.
You treat a person and I guarantee you, you win, no matter the outcome.

Lazarus 2.0.2 / FPC 3.0.4
Lazarus Trunc / FPC 3.0.4
Mac OS X Mojave

GetMem

  • Hero Member
  • *****
  • Posts: 3515
Re: Changing date stamps
« Reply #11 on: January 03, 2016, 05:10:58 pm »
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

  • Hero Member
  • *****
  • Posts: 700
  • ..... A day not Laughed is a day not Lived !!
    • Nursing With Humour
Re: Changing date stamps
« Reply #12 on: January 04, 2016, 04:05:58 pm »
I did solve it a bit differently because it was a one time thing.
Here is my solution.
Code: Pascal  [Select]
  1.  
  2. procedure TForm_Uitbetalen.BT_Rapport_PrintClick(Sender: TObject);
  3. var d1, d2, d3, t1, t2, i: integer;
  4.     d,t,a : string;
  5. begin
  6.   TQ_Uitbetalen.DataBase := Form_Information.Connect_RefereeDB;
  7.   i := 10;
  8.   TQ_Uitbetalen.SQL.Text := 'UPDATE tbl_Wedstrijden SET Wed_Datum = :Wed_Datum, Wed_Tijd = :Wed_Tijd WHERE Wed_ID = :Wed_ID';
  9.   while i <> 601 do begin
  10.     case i of
  11.       11, 13, 58, 59, 62, 63, 64, 65, 66, 67, 68, 69, 70, 71, 72, 73, 74, 75, 79,
  12.       83, 134, 139, 141, 145, 152, 164, 177, 185, 204, 242, 255, 259, 262, 272,
  13.       274, 275, 304, 327, 353, 355, 365, 366, 367, 368, 369,
  14.       370, 371, 372, 421, 422, 423, 426, 428, 439, 443, 444, 445, 446, 447, 448, 449,
  15.       455, 466, 470, 471, 519, 537, 538, 553, 571, 579, 581, 593, 595,
  16.       599 : begin   // exceptions
  17.               // ShowMessage (IntToStr(i));
  18.               i := i;
  19.             end
  20.       else
  21.         begin
  22.           d := MLookup('Wed_Datum','tbl_Wedstrijden','Wed_ID='+ IntToStr(i));
  23.           t := MLookup('Wed_Tijd','tbl_Wedstrijden','Wed_ID='+ IntToStr(i));
  24.           d1 := StrToInt (Copy(d, 1, 2));  // maand
  25.           d2 := StrToInt (Copy(d, 4, 2));  // dag
  26.           d3 := StrToInt ('20' + Copy(d, 7, 2));  // jaar
  27.           t1 := StrToInt (Copy(t, 1, 2));  // uren
  28.           t2 := StrToInt (Copy(t, 4, 2));  // minuten   12:15:00 PM
  29.           // AM en PM berekenen.
  30.           a := Copy(t, 10,2);
  31.           if a = 'AM' then
  32.             begin
  33.               if t1 = 12 then t1 := 0;
  34.             end
  35.           else begin
  36.             if t1 <> 12 then t1 := t1 + 12;
  37.           end;  // if
  38.  
  39.  
  40.           TQ_Uitbetalen.Params.BeginUpdate;
  41.           TQ_Uitbetalen.ParamByName('Wed_Datum').AsDateTime := EncodeDate(d3, d1, d2);
  42.           TQ_Uitbetalen.ParamByName('Wed_Tijd').AsTime := EncodeTime(t1, t2, 00, 000);
  43.           TQ_Uitbetalen.ParamByName('Wed_ID').AsInteger := i; //give a real value from database
  44.           TQ_Uitbetalen.Params.EndUpdate;
  45.           TQ_Uitbetalen.ExecSQL;
  46.           TQ_Uitbetalen.ApplyUpdates;
  47.         end;
  48.     end;  // case
  49.     i := i + 1;
  50.   end;  // while
  51.   Form_Information.Trans_RefereeDB.Commit;
  52. end;
  53.  
You treat a disease, you win, you lose.
You treat a person and I guarantee you, you win, no matter the outcome.

Lazarus 2.0.2 / FPC 3.0.4
Lazarus Trunc / FPC 3.0.4
Mac OS X Mojave

GetMem

  • Hero Member
  • *****
  • Posts: 3515
Re: [SOLVED] Changing date stamps
« Reply #13 on: January 04, 2016, 04:20:51 pm »
@madref
Is a brute force solution  :D, but if it works....

madref

  • Hero Member
  • *****
  • Posts: 700
  • ..... A day not Laughed is a day not Lived !!
    • Nursing With Humour
Re: [SOLVED] Changing date stamps
« Reply #14 on: January 04, 2016, 04:27:29 pm »
I know. It's not a beauty, but gets the job done  :D
You treat a disease, you win, you lose.
You treat a person and I guarantee you, you win, no matter the outcome.

Lazarus 2.0.2 / FPC 3.0.4
Lazarus Trunc / FPC 3.0.4
Mac OS X Mojave