Getting records from SQL between dates

[TomDuncan]

Hi All,
In my Accounting program I need to get the records which are between the selected dates. (i.e. financial year, 1st July last year to 31st June this year)
I assume I do a SQL Date>StartDate and Date<EndDate.
I need to be able to create these Start and End dates.
Any Ideas anyone.

in Delphi I use this routine

DecodeDate(Now,Y,M,Day);
Case InfoYearFilter of
0: begin
if M<7 then begin
dFrom:= EncodeDate(Y-1,7,1);
dTo := EncodeDate(Y,6,30);
end else begin
dFrom:= EncodeDate(Y,7,1);
dTo := EncodeDate(Y+1,6,30);
end;
if (DataSet.FieldByName('InvDate').asDateTime >= dFrom) and (DataSet.FieldByName('InvDate').asDateTime <= dTo) then Maybe:=True;
end;


Tom

 

[NJDude]

THIS post might give you a clue.

 

[mc73]

JulianDate is extensively used in such apps, so the post NJDude suggested should do the trick. In my case, I usually format date fields in a db, in the form yyyymmdd. This way, quering 'from - to' is quite easy since you can write something like this: SELECT listOfFields FROM myTable WHERE dateField>=startDate AND dateField<=endDate

 

[TomDuncan]

Hi tried that but got nothing to match.
Here is my code I used

Dt = DateTime.Now
Yr = DateTime.GetYear(Dt)
Mn = DateTime.GetMonth(Dt)
Dy = DateTime.GetDayOfMonth(Dt)
DateTime.DateFormat = "yyyyMMdd" 'set for sqlite
If Mn<7 Then
Sd = (Yr-1) & "0701"
Ed = Yr & "0630"
Else
Sd = Yr & "0701"
Ed = (Yr+1) & "0630"
End If
Dsd = DateTime.DateParse(Sd)
Ded = DateTime.DateParse(Ed)

S= "SELECT AutoNbr, Pcode, DspName, InvDate FROM Invoice WHERE InvDate>"&Dsd&" and InvDate<"&Ded

 

[mc73]

Hi tried that but got nothing to match.
Here is my code I used

Dt = DateTime.Now
Yr = DateTime.GetYear(Dt)
Mn = DateTime.GetMonth(Dt)
Dy = DateTime.GetDayOfMonth(Dt)
DateTime.DateFormat = "yyyyMMdd" 'set for sqlite
If Mn<7 Then
Sd = (Yr-1) & "0701"
Ed = Yr & "0630"
Else
Sd = Yr & "0701"
Ed = (Yr+1) & "0630"
End If
Dsd = DateTime.DateParse(Sd)
Ded = DateTime.DateParse(Ed)

S= "SELECT AutoNbr, Pcode, DspName, InvDate FROM Invoice WHERE InvDate>"&Dsd&" and InvDate<"&Ded

I don't see why to use dsd and ded. Did you first alter your date field in the table to integer?

 

[Mahares]

In your SQL statement you are comparing InvDate which is a table column name to ticks. InvDate should be compared to Sd and Ed.

 

[lagore]

I prefer to save my dates in sqlite as ticks then it is a simple case to query the database.

 

[Mahares]

I like to answer Ed by saying that I prefer to store my date or have a field in my table that is in this format: yyyymmdd. It is a lot few digits than ticks and not as intimidating. You can always compare dates in the yyyymmdd format without reverting to ticks. Example:
"SELECT Users FROM Forum WHERE Thread_Date BETWEEN '20120401' AND '20120504'"