asked on

DateTime problem.

My friend in another country have problem running my program. I guess that it has something to do with the regional settings Because He get:
The convertion of a char into a dateTime Value resulted in a out of range value.

I guess that it is in this code:

ThisDateTime:=DateTimeToStr(Now);
Query1.Close;
Query1.SQL.Clear;
Buf:= 'insert into q_quote (quote_no,version,alternate,project_id,quote_id,ext_id,orderno,sign1,'+
'sign2,createdby,createddate,modifiedby,modifieddate,usedby,lockstatus,tocustomer,tocustomerdate,'+
'toorder,toorderdate,language,currency,currencyrate,db_currency,vat,extra1,extra2)'+
' select ' + intToStr(max_no)+','+''''+'1'+''''+','+''''+'1'+''''+','+''''+intToStr(proj_id)+''''+','+''''+ThisQuotation.quote_id+''''+ ',ext_id,orderno,sign1,'+
'sign2,'+intToStr(CurrentUser.id)+','''+ThisDateTime+''','+IntToStr(CurrentUser.id)+','''+ThisDateTime+''',usedby,lockstatus,tocustomer,tocustomerdate,'+
'toorder,toorderdate,language,currency,currencyrate,db_currency,vat,extra1,extra2 from '+
'q_quote where quote_no=' +IntToStr(ThisQuotation.quote_no)+ ' and alternate=' + intToStr(ThisQuotation.alternate)
+ ' and version=' + intToStr(ThisQuotation.version);
Query1.SQL.Add(Buf);
Query1.ExecSQL;

How can I do to fix this?

kretzschmar

use parameters

hulken

ASKER

? How?

Motaz

use FormatDateTime instead of DateTimeToStr:

+ FormatDateTime('mm/dd/yyyy hh:nn', Now);
This will run independetly of regional settings.

hulken

ASKER

Ok I have one more question Motaz

If I have:

DateStr:=DateTimeToStr(Now);
TimeStr:=TimeToStr(Now);

and use TimeStr and DateStr in a sql statement.. how should I format the TimeStr to have this work?

Asw

Hi Hulken,

From Sql Help.

Start---------------------------------------------------
Local SQL expects date literals to be in a U.S. date format, MM/DD/YY or MM/DD/YYYY. International date formats are not supported. To prevent date literals from being mistaken by the SQL parser for arithmetic calculations, enclose them in quotation marks. This keeps 1/23/1998 from being mistaken for 1 divided by 23 divided by 1998.

SELECT *

FROM orders

WHERE (saledate <= "1/23/1998")

Leading zeros for the month and day fields are optional.

If the century is not specified for the year, the BDE setting FOURDIGITYEAR controls the century. If FOURDIGITYEAR is set to FALSE and the year is specified with only two digits, years 49 and less will be prefiex with 20 and years 50 and higher with 19. If

For example, with FOURDIGITYEAR set to FALSE, the SQL statement below returns rows where the SaleDate column contains dates of ?5/5/1980? or ?5/5/2030?.

SELECT *

FROM orders
WHERE (saledate = "5/5/30") OR

(saledate = "5/5/80")

To query using years outside these bounds, specify the century in the date literal.

SELECT *

FROM orders
WHERE (saledate = "5/5/1930") OR

(saledate = "5/5/2080")
End--------------------------------------------------

Asw

kretzschmar

about parameters
(no knowledge of the regional setting is needed)

const
ASQL = 'select soemthing from soemtable where adatefield = :ADate'

query1.sql.text := ASQL;
query1.parambyname('ADate').AsDateTime := TheDateTimeVar;
query1.open;

thats all

meikl ;-)

hulken

ASKER

A ado sql component dont have the parambyname ( as far as I can see)

ASKER CERTIFIED SOLUTION

Motaz

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

kretzschmar

a disadvantage of ado, i guess
(i don't use ado, therefore i can't further help you)

DelRunner

If you want to check the Regional settings, here's how (only tested on NT4)

You must include Registry in the uses section

var
Reg : TRegistry
DateFormat : String;

Reg:=TRegistry.Create;
Reg.RootKey:=HKEY_CURRENT_USER;
if Reg.OpenKeyReadOnly('Control Panel\International') then
DateFormat:=Reg.ReadString('sShortDate');
else
ShowMessage('Reg Key not found "');
Reg.CloseKey;
Reg.Free;
// you can now use the format on the current PC to format the desired date

DateStr:= FormatDateTime(DateFormat, Now);

duti

The whole trouble is that Delphi actually uses the Regional settings. At startup, it read those settings from the registry, and then store them in the following global variables : DecimalSeparator, ThousandSeparator, ShortDateFormat, DateSeparator, ... (see online help index : date/time formatting variables)

Whenever you call 'DateToStr(d)', Delphi is actually calling 'FormatDateTime(ShortDateFormat)' (See online help for DateToStr)

If you use '/' in FormatDateTime, then this character will be replaced by whatever character is in the variable DateSeparator. If you use ':', then it will be replaced by contents of TimeSeparator. (See online help for FormatDateTime, aand scroll down to the bottom)

To make your application independant of regional settings, assign defaultvalues to the date/time formatting variables, before the first call to any Date/Time function.

procedure TForm1.FormCreate(Sender: TObject);
begin
DateSeparator := '/';
TimeSeparator := ':';
DecimalSeparator := '.';
ThousandSeparator := ' ';
ShortDateFormat := 'dd/mm/yyyy';
. . .
end;