Date as Number in SQL

Date as Number in SQL

Post by smitj » Wed, 16 Jul 2003 12:50:33


The Following was downloaded from CodeCentral:
ID: 15090
Title:
Resolving Date Problem in SQL!
Terms:
No Special Terms
Name:
serkan sahinoglu email: XXXX@XXXXX.COM
URL:
http://www.yqcomputer.com/
Summary:
Use a number (as date value) in SQL text
Description:
Use a number (as date value) in SQL text.
Example:
'SELECT * FROM Country WHERE Date1=36736'
Actually, a date value is a number.
So, #07.29.2000# = 36736.
If you find a date value as a number then can use FormatFloat
function:
str1=FormatFloat('#', Date) //str1='36736'
Finally,
Query1.SQL.Text= 'SELECT * FROM Country WHERE Date1=' +
FormatFloat('#', Date);
Note: This solution is properly work BDE, VB, MS-Access, MS-SQL.

Yet somehow, I cannot get this to work, and dates in SQL are always a
problem. Users may have their date settings differently.
Is there a solution to this problem that will always work for dates in
SQL, irrespective of the users settings?
Thanks
Johan Smit
 
 
 

Date as Number in SQL

Post by smitj » Wed, 16 Jul 2003 13:40:48

On Tue, 15 Jul 2003 03:50:33 GMT, XXXX@XXXXX.COM (Johan Smit)


Sorry, I should have said LocalSQL/BDE/Paradox.
Thanks
Johan Smit

 
 
 

Date as Number in SQL

Post by Markku Nev » Thu, 17 Jul 2003 06:49:30


I also struggled with that years ago, but then I started to always use
Parameters (ParamByName) in SQL, when ever there was Dates involved.

with SQL do
begin
Clear;
Add('Select * from Orders');
Add('where OrderDate >= : aFrom');
Add('and OrderDate <= :aTo');
end;
ParamByName('aFrom').AsDateTime := StrToDate(Edit1.Text);
ParamByName('aTo').AsDateTime := StrToDate(Edit2.Text);

This should pretty much keep you away from SQL Date difficulties.


Another tip is to dictate the user's Date, Time, Decimal separator etc.
values to conform your own settings. Beside SQL usage, it will be a great
help for all the Date manipulations you do in your code.

I regulary have this kind of lines in Project.DPR or in MainForm:

ShortDateFormat := 'dd.mm.yyyy'; // These settings will be in effect
LongDateFormat := 'dd.mm.yyyy'; // throughout the application
ShortTimeFormat := 'hh:mm';
LongTimeFormat := 'hh:mm:ss';
DateSeparator := '.';
ThousandSeparator := '.';
DecimalSeparator := ',';

These settings will be valid only within your app, and all the Windows
Date etc. settings outside your app remain intact.

Of course, this kind of Date constraining may cause difficulties if
you intend to sell your application internationally, to different
countries.
It will work also there, but the users may not be very happy with
the new style of Dates etc.

Markku Nevalainen
 
 
 

Date as Number in SQL

Post by smitj » Thu, 17 Jul 2003 13:39:54

On Tue, 15 Jul 2003 23:49:30 +0200, Markku Nevalainen < XXXX@XXXXX.COM >



Thanks Markku,
Thank you, yes, but then you still cannot use a utility like DBUtil32
to directly enter SQL. Then there is no Delphi involved.
Most frustrating.
Even here, some users are so computer illeterate that they never set
the date and time formats correctly for the country preference.
The other day I got an invoice from a bank, and so help me, they used
american style. Awful!
Thank you
Regards
Johan Smit
 
 
 

Date as Number in SQL

Post by Markku Nev » Thu, 17 Jul 2003 17:39:50


And what's that then? Google found me a freeware utility DBUtil32 from
year 1997, that seems not be worth much.


Well, this is a Delphi newsgroup... Can't solve all those "other's"
problems also :)

Markku Nevalainen