Recent

Author Topic: I need help to solve a date problem in a SQL-statment.  (Read 1240 times)

Hatti

  • New member
  • *
  • Posts: 7
I need help to solve a date problem in a SQL-statment.
« on: September 17, 2020, 12:12:45 pm »
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

  • Full Member
  • ***
  • Posts: 176
  • Beers: 1567
Re: I need help to solve a date problem in a SQL-statment.
« Reply #1 on: September 17, 2020, 12:29:11 pm »
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][+][-]
  1. SELECT * FROM MyTable WHERE (YEAR=2020)
  2. SELECT * FROM MyTable WHERE (YEAR=2020) AND (MONTH BETWEEN 3 AND 7)
  3. SELECT * FROM MyTable WHERE ((YEAR=2019) AND (month>3)) OR ((YEAR=2020) AND (month<7))
  4. SELECT * FROM MyTable WHERE ((YEAR=2016) AND (month>3)) OR (YEAR BETWEEN 2017 AND 2019) OR ((YEAR=2020) AND (month<7))
  5.  
To err is human, but to really mess things up, you need a computer.

eny

  • Hero Member
  • *****
  • Posts: 1634
Re: I need help to solve a date problem in a SQL-statment.
« Reply #2 on: September 17, 2020, 12:38:41 pm »
Code: SQL  [Select][+][-]
  1. SELECT *
  2.   FROM MyTable
  3.  WHERE (YEAR * 100 + MONTH) BETWEEN 201910 AND 202002
  4.  
Ofc check whether the limits of BETWEEN are included or excluded.
All posts based on: Win10 (Win64); Lazarus 2.0.10 'stable' (x64) unless specified otherwise...

Hatti

  • New member
  • *
  • Posts: 7
Re: I need help to solve a date problem in a SQL-statment.
« Reply #3 on: September 17, 2020, 12:49:49 pm »
Many thanks eny,

Code: SQL  [Select][+][-]
  1. SELECT *
  2.   FROM MyTable
  3.  WHERE (YEAR * 100 + MONTH) BETWEEN 201910 AND 202002
  4.  
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.  :D

Thanks a lot,
Alain

wittbo

  • Full Member
  • ***
  • Posts: 150
Re: I need help to solve a date problem in a SQL-statment.
« Reply #4 on: September 27, 2020, 10:22:59 am »
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.
-wittbo-
MBAir with MacOS 10.14.6 / Lazarus 2.2.4
MacStudio with MacOS 13.0.1 / Lazarus 2.2.4

winni

  • Hero Member
  • *****
  • Posts: 3197
Re: I need help to solve a date problem in a SQL-statment.
« Reply #5 on: September 27, 2020, 12:17:34 pm »
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.

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

 

TinyPortal © 2005-2018