Date as Number in SQL

Date as Number in SQL

Post 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

 
 
 

Date as Number in SQL

Post 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

 
 
 

Date as Number in SQL

Post 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

 
 
 

Date as Number in SQL

Post 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
 
 
 

Date as Number in SQL

Post 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

 
 
 

Date as Number in SQL

Post 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
 
 
 

1. Calculating Number of Days and Number of Months between 2 dates

Hi all!

I'm looking for an easy way to calculate the number of days and
number of months between 2 dates.  For example: 1/10/96 to 3/10/96
would give me the response of

January 21 Days
February 29 Days (needs to account for leap years)
March 10 Days

I need this info for calculating annualized values over a period of
time.  Another example:  If I have a project that runs from 01/01/96
to 12/31/96 and project cost is $100,000 I know that:

project_begin-project_end=365
project_cost=100000

I know if I spread that cost out I'm averaging 273.97/day.  I have
hundreds of these projects going on at one time and would like to know
how much I'm spending over a given period of time and how much/month
currently and projected.  So if I ask how much am I spending over the
period 5/01/96 to 10/31/96 it will give me how much I'm spending each
month and if I have odd days it will calculate that correctly as well
and will also project anticipated spending for the next n months.

I have all the formulas for this stuff but don't know how to get the
number of days and months.  Does Foxpro 2.6 have an easy way of
calculating days and months?

Thanks for any help that can be offered.  

Chris

2. USA-IL-Oracle Clinical Developer

3. Date - Date To Find Number Of Days?

4. Make msxml3 default, instead msxml2.

5. Dates and Random Numbers in PL/SQL?

6. Merge replication w/o snapshot

7. SQL/Server Version Numbers/Dates

8. sql performance

9. SQL: Select some data with date criteria, but only by Date part of date field

10. ((Current Date - Start Date)/(End Date - Start Date))*Contract Amt

11. NUMBER(9) UNION NUMBER(9) results in NUMBER(32,32)

12. Number of Days Between 2 Dates

13. Number of full days between 2 dates