[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 163
  • Last Modified:

SQL and date fields

How do I return records from table with dates less than
or greater than mm/dd/yy
the min(dd/mm/yy) or max(dd/mm/yy)
don't seem to work

thanks
0
John Culkin
Asked:
John Culkin
1 Solution
 
javiertbCommented:
You say that this code doesn't work?
Select * from table1 where table1.date>'dd/mm/yy'

The problem might be in the date format you have in your system.
You can use FormatDateTime function or you should check your
win.ini file and look for the [intl] section where the variable sShortDate must be the date format you need.

Regards,
Javi
0
 
John CulkinAuthor Commented:
I don't want to find all dates greater than a certain date
I need to find the maximum date, ie the last date in a field.
by the way my sshortdate is ok.

many thanks

john
ps last(dd/mm/yy) doesnt seem to work either
0
 
vorlonCommented:
John,
The reason why your min/max statements aren't working is because you're passing them a string of the format mm/dd/yy. If the tables contain date/time fields then the internal format is not stored as mm/dd/yy by as xx.yy where xx is the date and yy is the time. You must convert your string to that format first. Here is some sample code:

procedure TForm1.Button1Click(Sender: TObject);
var
  dt:string;
begin
   query1.close;
   query1.sql.clear;
   Str(StrToDate('05/29/97'),dt);
   dt:='select * from maxdate where datevalue > ' + dt + 'order  datevalue desc'
   query1.sql.add(dt);
   query1.open;
end;{button1click}

I've created a table with 1 field (datevalue) and placed some dates into it. Obviously the table name is maxdate. To use the max and min function, I placed the order by clause. All you have to do now is get the first record and that will be your max function. To use the min function, use the order by clause without the "desc" keyword and then get the first record as well.

Hope this helps.
0
 
vygaCommented:
SQL's like:select max(tdate) from timesliporselect min(tdate) from timeslipor select min(tdate) from timeslipwhere tdate>'mm/dd/yy'really work. All you need is to construct SQL you need runtime.Let's say this way:QueryTimeslip.SQL.Clear;QueryTimeslip.SQL.Add('select min(tdate) from timeslip');QueryTimeslip.SQL.Add('where tdate>'+DateToStr(someDate));QueryTimeslip.Open;or you may use FormatDateTime('mm/dd/yy',someDate) instead of DateToStr(). This function is safer. Using DateToStr() you must sure that global variables shortDateFormat and DateSeparator are set to correct values.vyga
0
 
John CulkinAuthor Commented:
sorry about the delay Ive been on holidayThanksJohn
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now