Issue selecting records between dates

Hallo!

I’m selecting records between two dates (InitialDate and FinalDate as new Date)

Example:

SELECT UUID FROM emitted_invoices WHERE EMITTER='SAPI568296W' AND 'InitialDate.SQLDate + 00:00:00.000' and 'FinalDate.SQLDate + 23:59.59.997'
If I set InitialDate and FinalDate as these way, everything its ok It reads all the Year.

InitialDate as String = "2016"

[code] InitialDate.GMTOffset=-7
InitialDate.SQLDate= YearSelected + “-01-01”

FinalDate.GMTOffset=-7
FinalDate.SQLDate= YearSelected + “-12-31”[/code]
It loads successfully the total of records that are “23” records.

But then I wondered that a Month doesn’t have the same amount of days, sometimes may have 30, 31, in case of Februarys it got 28 or 29.
So I do that:

  InitialDate.GMTOffset = -7
  InitialDate.Year = Val(YearSelected)
  InitialDate.Month = 01
  InitialDate.day = DaysInaMonth(InitialDate)
  
  FinalDate.GMTOffset = -7
  FinalDate.Year = Val(YearSelected)
  FinalDate.Month = 12
  FinalDate.day = DaysInaMonth(FinalDate)

But when I do this I only got “21 Records”, instead of 23.
Taking a quick look closer, I realized that the Records made in January its not being loaded.

Otherwise If I do it at the other way It loads January records without problem, the issue here is that I don’t wanna do it as this way, cuz I previously explained the days in a month.

What Am I doing wrong.
Am I misunderstanding something?
Regards

Perhaps InitialDate.day = DaysInaMonth(InitialDate) should be InitialDate.day = 1? Or even just leave this line out, but using DaysInaMonth implies you’re getting January 31.

LOL, jajajajajajajaja, ooooh men, Yeah, you make me laugh!!!

What a Dumb I am, LOL, Obiously doesn’t catch January records cuz the first days starts in 31 instead of 1

:smiley: :smiley: :smiley:

Thanks Wayne!

If you’re looking for an entire year (and not an arbitrary date range), then I would code it as

sql = "SELECT ... WHERE datecolumn LIKE '" + str(YearSelected" + "%'"

eg.,

SELECT ... WHERE datecolumn LIKE '2016%'