Date as Number in SQL 
Author Message
 Date as Number in SQL

The Following was downloaded from CodeCentral:
ID: 15090
Title:
Resolving Date Problem in SQL!
Terms:
No Special Terms
Name:

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



Sat, 31 Dec 2005 11:50:33 GMT  
 Date as Number in SQL


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


Sat, 31 Dec 2005 12:40:48 GMT  
 Date as Number in SQL

Quote:

> 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



Sun, 01 Jan 2006 05:49:30 GMT  
 Date as Number in SQL


Quote:

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


Sun, 01 Jan 2006 12:39:54 GMT  
 Date as Number in SQL

Quote:

> 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



Sun, 01 Jan 2006 16:39:50 GMT  
 Date as Number in SQL
On Wed, 16 Jul 2003 11:39:50 +0300, Markku Nevalainen

Quote:

>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


Mon, 02 Jan 2006 15:31:11 GMT  
 
 [ 6 post ] 

 Relevant Pages 

1. Number of days between two dates?

2. Date to Week Number

3. Date to Week Number

4. Number of days between 2 dates

5. searching a routine to convert system date number in common format

6. Limiting SQL Result Sets by number of Records

7. Delphi 2.0 cached SQL rounding numbers on Oracle.

8. Finding the largest number with SQL

9. dBase - SQL (number of...)

10. Problem with real numbers in SQL Server

11. Date Format in SQL

12. Date fields in the sql !!!!!!!!!!!

 

 
Powered by phpBB® Forum Software