Solved

SQL and date fields

Posted on 1997-05-27
5
150 Views
Last Modified: 2010-04-04
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
Comment
Question by:John Culkin
5 Comments
 
LVL 2

Expert Comment

by:javiertb
ID: 1336619
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
 

Author Comment

by:John Culkin
ID: 1336620
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
 
LVL 2

Accepted Solution

by:
vorlon earned 70 total points
ID: 1336621
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
 

Expert Comment

by:vyga
ID: 1336622
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
 

Author Comment

by:John Culkin
ID: 1336623
sorry about the delay Ive been on holidayThanksJohn
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Convert a string into a TDateTime 5 72
can't find the executable in Simulator 1 112
When i run adoquery my application freezes 26 188
Multi-layered image in FireMonkey 9 67
A lot of questions regard threads in Delphi.   One of the more specific questions is how to show progress of the thread.   Updating a progressbar from inside a thread is a mistake. A solution to this would be to send a synchronized message to the…
Have you ever had your Delphi form/application just hanging while waiting for data to load? This is the article to read if you want to learn some things about adding threads for data loading in the background. First, I'll setup a general applica…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

740 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question