[Solved] Help with sql lazarus / 16-Databases ---------------------------------------------------------------------- magleft · 2024-03-16 17:01 Hello. On the database I have, I am trying to run the following query: SELECT CASE       WHEN EPISKEPSEIS.MERA IS NULL THEN date(EPISKEPSEIS.STARTDATE+1* FREQDAYS )       ELSE date(EPISKEPSEIS.MERA+1* FREQDAYS) END AS LASTDAY1, FROM PROJECTS but in the LASTDAY1 field I get the value -4707-06-09 the EPISKEPSEIS.MERA and EPISKEPSEIS.STARTDATE fields are TDATE , while the FREQDAYS field is Integer What am I doing wrong; How can it be fixed? Thanks in advance for any help ---------------------------------------------------------------------- rvk · 2024-03-16 17:58 Normally you can't add an integer to a date in SQL. But you might want to mention what database you use because maybe some allow it. You also don't mention if STARTDATE always has a valid date (and not 0 or null). ---------------------------------------------------------------------- paweld · 2024-03-16 18:58 It all depends on the type of RDBMS. For example, for MSSQL it will be something like thisvCode: SQL  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i SELECT dateadd(d, FREQDAYS, isnull(EPISKEPSEIS.MERA, EPISKEPSEIS.STARTDATE)) lastday1 FROM projects ---------------------------------------------------------------------- magleft · 2024-03-17 05:50 I use SQLLite. The <> field always has a valid value. Is there any way in sql to add days to a date field? Thanks everyone for your help. ---------------------------------------------------------------------- TRon · 2024-03-17 05:54 Quote from: magleft on March 17, 2024, 06:50:38 amI use SQLLite. The <> field always has a valid value. Is there any way in sql to add days to a date field? And seconds, years, weeks etc. See SQLite Date and time functions probably something like: 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 WHEN EPISKEPSEIS.MERA IS NULL THEN DATE(EPISKEPSEIS.STARTDATE, "+" || 1 * FREQDAYS || " days")      ELSE DATE(EPISKEPSEIS.MERA, "+" || 1 * FREQDAYS || " days") ---------------------------------------------------------------------- magleft · 2024-03-17 17:02 Thanks for your answer. What I want to do is add to the date field an integer number of days that comes from a table field (FREQDAYS). ---------------------------------------------------------------------- magleft · 2024-03-17 18:29 I solved the problem by converting the date field to a Julian date which is a number. Again I would like to thank everyone. ---------------------------------------------------------------------- TRon · 2024-03-18 01:57 Quote from: magleft on March 17, 2024, 06:02:19 pmThanks for your answer. What I want to do is add to the date field an integer number of days that comes from a table field (FREQDAYS). And except for the multiplication that was shown the original example, that is working as expected for me: 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   DB.Query.SQL.Text:=    'SELECT *, ' +    'CASE WHEN mera IS NULL ' +  'THEN date(startdate, "+" || freqdays || " days") ' +    'ELSE date(mera, "+" || freqdays || " days") ' +    'END AS lastday1 ' +    'FROM projects;';  result: Code: [Select]0  "12-8-20"  "28-1-21"  "1"  "2020-08-13" 1  "8-3-21"  "15-11-23"  "4"  "2021-03-12" 2  "18-4-19"  "27-5-21"  "8"  "2019-04-26" 3  "11-6-19"  "19-12-17"  "1"  "2019-06-12" 4  "26-12-24"  "14-9-17"  "9"  "2025-01-04" 5  "5-3-23"  "23-5-23"  "8"  "2023-03-13" 6  "25-10-20"  "20-12-18"  "5"  "2020-10-30" 7  "31-7-15"  "21-5-18"  "3"  "2015-08-03" 8  "16-1-15"  "17-9-18"  "9"  "2015-01-25" 9  "12-1-23"  "22-4-16"  "2"  "2023-01-14" edit: which, I forgot to mention, is the same as: 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     SELECT *,     iif(mera IS NULL,DATE(startdate, "+" || freqdays || " days"),DATE(mera, "+" || freqdays || " days")) AS lastday1    FROM projects; ---------------------------------------------------------------------- Zvoni · 2024-03-18 11:38 Quote from: TRon on March 18, 2024, 02:57:54 amQuote from: magleft on March 17, 2024, 06:02:19 pmThanks for your answer. What I want to do is add to the date field an integer number of days that comes from a table field (FREQDAYS). And except for the multiplication that was shown the original example, that is working as expected for me: 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   DB.Query.SQL.Text:=    'SELECT *, ' +    'CASE WHEN mera IS NULL ' +  'THEN date(startdate, "+" || freqdays || " days") ' +    'ELSE date(mera, "+" || freqdays || " days") ' +    'END AS lastday1 ' +    'FROM projects;';  result: Code: [Select]0  "12-8-20"  "28-1-21"  "1"  "2020-08-13" 1  "8-3-21"  "15-11-23"  "4"  "2021-03-12" 2  "18-4-19"  "27-5-21"  "8"  "2019-04-26" 3  "11-6-19"  "19-12-17"  "1"  "2019-06-12" 4  "26-12-24"  "14-9-17"  "9"  "2025-01-04" 5  "5-3-23"  "23-5-23"  "8"  "2023-03-13" 6  "25-10-20"  "20-12-18"  "5"  "2020-10-30" 7  "31-7-15"  "21-5-18"  "3"  "2015-08-03" 8  "16-1-15"  "17-9-18"  "9"  "2015-01-25" 9  "12-1-23"  "22-4-16"  "2"  "2023-01-14" edit: which, I forgot to mention, is the same as: 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     SELECT *,     iif(mera IS NULL,DATE(startdate, "+" || freqdays || " days"),DATE(mera, "+" || freqdays || " days")) AS lastday1    FROM projects;  Shortcut (untested, though) 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 SELECT *, DATE(COALESCE(mera,startdate),"+"||freqdays||" days") AS LastDay1 FROM projects ---------------------------------------------------------------------- magleft · 2024-03-18 13:35 Thank you very much. It works perfectly. ---------------------------------------------------------------------- Zvoni · 2024-03-18 16:46 Quote from: magleft on March 18, 2024, 02:35:58 pmThank you very much. It works perfectly. Which one? ---------------------------------------------------------------------- TRon · 2024-03-19 07:21 Quote from: Zvoni on March 18, 2024, 12:38:10 pmShortcut (untested, though) 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 SELECT *, DATE(COALESCE(mera,startdate),"+"||freqdays||" days") AS LastDay1 FROM projectsThat's neat. Thank you very much Zvoni as I did not know how to use that. Code: [Select]opening database :memory: creating table projects inserting data into table projects peforming query: SELECT *, CASE WHEN mera IS NULL THEN date(startdate, "+" || freqdays || " days") ELSE date(mera, "+" || freqdays || " days") END AS lastday1 FROM projects;  idx         mera    startdate     freqdays     lastday1    0    "12-8-20"    "28-1-21"     "1"      "2020-08-13"    1     "8-3-21"   "15-11-23"     "4"      "2021-03-12"    2    "18-4-19"    "27-5-21"     "8"      "2019-04-26"    3    "11-6-19"   "19-12-17"     "1"      "2019-06-12"    4           ""    "14-9-17"     "9"      "2017-09-23"    5     "5-3-23"    "23-5-23"     "8"      "2023-03-13"    6   "25-10-20"   "20-12-18"     "5"      "2020-10-30"    7    "31-7-15"    "21-5-18"     "3"      "2015-08-03"    8    "16-1-15"    "17-9-18"     "9"      "2015-01-25"    9    "12-1-23"    "22-4-16"     "2"      "2023-01-14" peforming query: SELECT *, iif(mera is NULL,date(startdate, "+" || freqdays || " days"),date(mera, "+" || freqdays || " days")) AS lastday1 FROM projects;  idx         mera    startdate     freqdays     lastday1    0    "12-8-20"    "28-1-21"     "1"      "2020-08-13"    1     "8-3-21"   "15-11-23"     "4"      "2021-03-12"    2    "18-4-19"    "27-5-21"     "8"      "2019-04-26"    3    "11-6-19"   "19-12-17"     "1"      "2019-06-12"    4           ""    "14-9-17"     "9"      "2017-09-23"    5     "5-3-23"    "23-5-23"     "8"      "2023-03-13"    6   "25-10-20"   "20-12-18"     "5"      "2020-10-30"    7    "31-7-15"    "21-5-18"     "3"      "2015-08-03"    8    "16-1-15"    "17-9-18"     "9"      "2015-01-25"    9    "12-1-23"    "22-4-16"     "2"      "2023-01-14" peforming query: SELECT *, DATE(COALESCE(mera,startdate),"+"||freqdays||" days") AS LastDay1 FROM projects;  idx         mera    startdate     freqdays     lastday1    0    "12-8-20"    "28-1-21"     "1"      "2020-08-13"    1     "8-3-21"   "15-11-23"     "4"      "2021-03-12"    2    "18-4-19"    "27-5-21"     "8"      "2019-04-26"    3    "11-6-19"   "19-12-17"     "1"      "2019-06-12"    4           ""    "14-9-17"     "9"      "2017-09-23"    5     "5-3-23"    "23-5-23"     "8"      "2023-03-13"    6   "25-10-20"   "20-12-18"     "5"      "2020-10-30"    7    "31-7-15"    "21-5-18"     "3"      "2015-08-03"    8    "16-1-15"    "17-9-18"     "9"      "2015-01-25"    9    "12-1-23"    "22-4-16"     "2"      "2023-01-14" closing database :memory: ---------------------------------------------------------------------- magleft · 2024-03-19 11:08 This sql works perfect    SELECT *, DATE(COALESCE(mera,startdate),"+"|| 1 * freqdays ||" days") AS LastDay1 FROM projects Thanks