I need help to solve a date problem in a SQL-statment.
lazarus / 16-Databases

----------------------------------------------------------------------
Hatti · 2020-09-17 10:12

Hello!

In a, existing database I have among other fields, 3 fields that
describe a date: Year (integer), Month (integer), Day(Integer)!

Now I have to limit the date period with a SQL sentence.(The day is
not important, only complete months are needed).
e.g.: all records between 10.2019 and 02.2020

Is there a simple way to do this?

I'm completely on the edge of my seat at the moment and have only very
complicated formulations in my head, where I'm not even sure they
work.

Thanks in advance.
Alain

Translated with www.DeepL.com/Translator (free version)

----------------------------------------------------------------------
RayoGlauco · 2020-09-17 10:29

If you need to select a period of several months, the expression can
be complicated, especially if you want to select a period of several
years and different months. It would be much easier if there was a
date field, instead of separate year, month, and day fields.

Examples:

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 *
FROM MyTable WHERE (YEAR=2020) SELECT * FROM MyTable WHERE (YEAR=2020)
AND (MONTH BETWEEN 3 AND 7)SELECT * FROM MyTable WHERE ((YEAR=2019)
AND (month>3)) OR ((YEAR=2020) AND (month<7))SELECT * FROM MyTable
WHERE ((YEAR=2016) AND (month>3)) OR (YEAR BETWEEN 2017 AND 2019) OR
((YEAR=2020) AND (month<7))

----------------------------------------------------------------------
eny · 2020-09-17 10:38

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 *  
FROM MyTable  WHERE (YEAR * 100 + MONTH) BETWEEN 201910 AND 202002 Ofc
check whether the limits of BETWEEN are included or excluded.

----------------------------------------------------------------------
Hatti · 2020-09-17 10:49

Many thanks eny,

Quote from: eny on September 17, 2020, 12:38:41 pmCode: 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 *  
FROM MyTable  WHERE (YEAR * 100 + MONTH) BETWEEN 201910 AND 202002 Ofc
check whether the limits of BETWEEN are included or excluded.

This looks like the solution I was looking for.
Too simple for my complicated brain to find by itself.

My thoughts went rather in the direction of RayoGlauco.

I guess my problem is solved with this. 

Thanks a lot,
Alain

----------------------------------------------------------------------
wittbo · 2020-09-27 08:22

Since many years i am using one single integer field for date values.
The structure is YYYYMMDD. Therefore
it is quite easy to use them for any date queries, since you don't
have problems with date fields containing a time component. I have a
unit with most needed conversion routines like datenumbertostr.

----------------------------------------------------------------------
winni · 2020-09-27 10:17

Quote from: wittbo on September 27, 2020, 10:22:59 amSince many years
i am using one single integer field for date values. The structure is
YYYYMMDD. Therefore
it is quite easy to use them for any date queries, since you don't
have problems with date fields containing a time component. I have a
unit with most needed conversion routines like datenumbertostr.

Hi!

This is what I also often do.
With YYYYMMDD you can easy compare dates and you can sort them.
And with converting to a string and inserting two hyphens you get the
ISO date format.

Winni