Very Computer

by **Johan Sm** » 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:

URL:
http://www.EskiDefterler.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

by **Johan Sm** » Wed, 16 Jul 2003 13:40:48

Quote:>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?

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

by **Markku Nevalaine** » Thu, 17 Jul 2003 06:49:30

> Yet somehow, I cannot get this to work, and dates in SQL are always a
> problem. Is there a solution to this problem that will always work for dates in
> SQL, irrespective of the users settings?

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.

Quote:> Users may have their date settings differently.

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

by **Johan Sm** » Thu, 17 Jul 2003 13:39:54

>> Yet somehow, I cannot get this to work, and dates in SQL are always a
>> problem. Is there a solution to this problem that will always work for dates in
>> SQL, irrespective of the users settings?

>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.

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.

- Show quoted text -

Quote:
>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.

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

by **Markku Nevalaine** » Thu, 17 Jul 2003 17:39:50

> Thank you, yes, but then you still cannot use a utility like DBUtil32
> to directly enter SQL.

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

Quote:> Then there is no Delphi involved.

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

Markku Nevalainen

by **Johan Sm** » Fri, 18 Jul 2003 16:31:11

On Wed, 16 Jul 2003 11:39:50 +0300, Markku Nevalainen

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

Sorry, I just mentioned that utility because you can also directly
enter localsql into it as in my app.

Quote:
>> Then there is no Delphi involved.

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

You're right, of course. The params still seem to be the best
solution.
Thank you
Regards
Johan Smit

Very Computer

Date as Number in SQL

Date as Number in SQL

Date as Number in SQL

Date as Number in SQL

Date as Number in SQL

Date as Number in SQL

Date as Number in SQL