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
Resolving Date Problem in SQL!
No Special Terms
serkan sahinoglu email: XXXX@XXXXX.COM
Use a number (as date value) in SQL text
Use a number (as date value) in SQL text.
'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
str1=FormatFloat('#', Date) //str1='36736'
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?
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.
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
Add('Select * from Orders');
Add('where OrderDate >= : aFrom');
Add('and OrderDate <= :aTo');
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 := ''; // These settings will be in effect
LongDateFormat := ''; // 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
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
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