Board index » delphi » Problem with DateTime on Delphi 3 Client/Server

Problem with DateTime on Delphi 3 Client/Server

I can't insert data type DateTime into database using SQL Server 6.5
with MSSQL driver. I use Delphi 3 Client/Server programm.
Can somebody help?
 

Re:Problem with DateTime on Delphi 3 Client/Server


You can add a datetime in MSSQL...
Tab.FieldByName('dummy').AsDateTime := Now;
or
Tab.FieldByName('dummy').AsDateTime :=
StrToDate(FormatDateTime('dd/mm/yyyy', Now);
Quote
Boris Tkach wrote:
> I can't insert data type DateTime into database using SQL Server 6.5
> with MSSQL driver. I use Delphi 3 Client/Server programm.
> Can somebody help?

Re:Problem with DateTime on Delphi 3 Client/Server


I had the same problem with Oracle.  Then I found that Oracle expected
a date in the format dd-Mon-yy such as 28-Oct-98.  After I formatted
my date string into that format, all was well.
Quote
Boris Tkach wrote:
> I can't insert data type DateTime into database using SQL Server 6.5
> with MSSQL driver. I use Delphi 3 Client/Server programm.
> Can somebody help?

Re:Problem with DateTime on Delphi 3 Client/Server


On Wed, 28 Oct 1998 13:44:28 -0800, Jim Langston <jlang...@gte.net>
wrote:

Quote
>> I can't insert data type DateTime into database using SQL Server 6.5
>> with MSSQL driver. I use Delphi 3 Client/Server programm.
>> Can somebody help?
>I had the same problem with Oracle.  Then I found that Oracle expected
>a date in the format dd-Mon-yy such as 28-Oct-98.  After I formatted
>my date string into that format, all was well.

>Boris Tkach wrote:

...as long as your computer doesn't use any of the cool international
date/time settings, that is :-) Sybase, at least, can be persuaded to
accept dates in quite a few different formats with the CONVERT
function, ie the date is converted into a string (preferably with
century, Y2K bug and all that, and in 'numeric' format rather than
involving nationality-dependand string constants) in the desired
format in Delphi, the string is passed as a parameter to the SQL query
which then passes it to the SQL server inside a CONVERT statement
which then converts it into a proper date for storage (ouch!)

I imagine that other database servers have similar functions.

On question that comes to mind is why on earth it has to be so
convoluted... it is somewhat surprising that Delphi/BDE can't handle
this more intelligently... 8-/

St?le Sannerud

Re:Problem with DateTime on Delphi 3 Client/Server


This brings error message "Can't convert string to DateTime". This message
of SQL Server.

Any other ideas??

Quote
Facotti Stephane wrote:
> You can add a datetime in MSSQL...
> Tab.FieldByName('dummy').AsDateTime := Now;
> or
> Tab.FieldByName('dummy').AsDateTime :=
> StrToDate(FormatDateTime('dd/mm/yyyy', Now);

> Boris Tkach wrote:

> > I can't insert data type DateTime into database using SQL Server 6.5
> > with MSSQL driver. I use Delphi 3 Client/Server programm.
> > Can somebody help?

Re:Problem with DateTime on Delphi 3 Client/Server


Quote
>...as long as your computer doesn't use any of the cool international
>date/time settings, that is :-) Sybase, at least, can be persuaded to
>accept dates in quite a few different formats with the CONVERT
>function, ie the date is converted into a string (preferably with
>century, Y2K bug and all that, and in 'numeric' format rather than
>involving nationality-dependand string constants) in the desired
>format in Delphi, the string is passed as a parameter to the SQL query
>which then passes it to the SQL server inside a CONVERT statement
>which then converts it into a proper date for storage (ouch!)

>I imagine that other database servers have similar functions.

>On question that comes to mind is why on earth it has to be so
>convoluted... it is somewhat surprising that Delphi/BDE can't handle
>this more intelligently... 8-/

Is it? Do you blame the car that it would have to go more intelligently into
curve, when you drive over cliff?

This has been discussed hundreds of times: use parameters! Using CONVERT
function implies that ALL clients must have the same date format setting as
server (?smart?) and when you enter 01-03-05, how could the function handle
this intelligently?. You have no such problems, when you use parameters.

--
-------------------------
Regards,
Robert

Remove leading aa when replying.
MailTo: aarobert.ce...@eunet.si

Re:Problem with DateTime on Delphi 3 Client/Server


Check the country configuration on your server.

Maybe you should change the date mask in mm/dd/yyyy (US notation) or
something else. Date 30/11/1998 is in Europe correct (30 november), but in
US notation it means the 11th day of the 30th month...

Re:Problem with DateTime on Delphi 3 Client/Server


Quote
On Sat, 31 Oct 1998 21:27:16 GMT, "Robert Cerny" <T...@home.com> wrote:

>>On question that comes to mind is why on earth it has to be so
>>convoluted... it is somewhat surprising that Delphi/BDE can't handle
>>this more intelligently... 8-/

>Is it? Do you blame the car that it would have to go more intelligently into
>curve, when you drive over cliff?

>This has been discussed hundreds of times: use parameters! Using CONVERT
>function implies that ALL clients must have the same date format setting as
>server (?smart?) and when you enter 01-03-05, how could the function handle
>this intelligently?. You have no such problems, when you use parameters.

>--

Well, I use the SQL convert function precisely to avoid any dependence
of settings on either client or server. Let me explain: In my Delphi
code, i assign a given value to the shortdateformat system variable,
in order to get a date string that I can depend on being in a certain
format. I then convert the date that I want to a string - let us
suggest 01-03-05 (and supposing that it means March 1, 2005 :-) just
as an example. I then pass the resulting (given shortdateformat =
'dd-mm-yy') string '01-03-05'  as a parameter to my SQL function
(TQuery), this passes the string on to the SQL server inside a CONVERT
statement. The real point, however, is that Sybase's CONVERT takes
another parameter, telling it which one of a certain number of date
formats the string matches - 'dd-mm-yy' in this instance.

So, you see, I am really not dependent on the date settings of either
client or server. I am dependant on the server having a CONVERT
function with syntax similar to Sybase's, however!

Could you please give an example (with code) describing what you mean
by using parameters - I'd be interested to know any and all
alternative methods to the one I have had to resort to.  I have had
mixed results with passing dates (ie dates as dates, not dates as
strings) as parameters to TQueries; not only in terms of the date
format per se, but also related to DATE and DATETIME data types being
two slightly different beasts...

St?le Sannerud

Re:Problem with DateTime on Delphi 3 Client/Server


OK.
In SQL you write:
update The.Table set DateField=:DateField where AnID=:AnID
In code:
Query1.ParamByName('DateField').AsDate := SomeDate;
Query1.ParamByName('AnID').AsInteger := SomeID;
Query1.ExecSQL;
You can get detailed description in help file "dynamic
queries","parametrized queries" or something like that.
--
-------------------------
Regards,
Robert

Remove leading aa when replying.
MailTo: aarobert.ce...@eunet.si

Re:Problem with DateTime on Delphi 3 Client/Server


Quote
On Tue, 03 Nov 1998 13:07:00 GMT, "Robert Cerny" <T...@home.com> wrote:
>OK.
>In SQL you write:
>update The.Table set DateField=:DateField where AnID=:AnID
>In code:
>Query1.ParamByName('DateField').AsDate := SomeDate;
>Query1.ParamByName('AnID').AsInteger := SomeID;
>Query1.ExecSQL;
>You can get detailed description in help file "dynamic
>queries","parametrized queries" or something like that.
>--

Hey, give me _some_ credit 8-) , I've been there, done that, and it
didn't work...what kind of database server are you talking to?

Sybase only has a DATETIME datatype, not DATE, and something in the
BDE (or further out in the great big chain of things) starts to squeal
when I pass parameters to a DATETIME field using .AsDate! I quote:
"General SQL error. [INTERSOLV][ODBC SQL Server driver] Timestamp
parameters with zero scale must have a precision of 13, 16 or 19.
Parameter number 1, precision: 0." (This example being an attemt to
select count * from at table where records are timestamped with a
certain date) .AsDatetime would solve this problem, probably, but
might create others. Of course, there would be ways around those as
well!

But, anyway, the routine I described with string passing and CONVERT
works - at least on Sybase!

St?le Sannerud

Re:Problem with DateTime on Delphi 3 Client/Server


On Tue, 03 Nov 1998 14:22:57 GMT, sst...@nospam.sn.no (St?le Sannerud)
wrote:

Quote
>On Tue, 03 Nov 1998 13:07:00 GMT, "Robert Cerny" <T...@home.com> wrote:

>>OK.
>>In SQL you write:
>>update The.Table set DateField=:DateField where AnID=:AnID
>>In code:
>>Query1.ParamByName('DateField').AsDate := SomeDate;
>>Query1.ParamByName('AnID').AsInteger := SomeID;
>>Query1.ExecSQL;
>>You can get detailed description in help file "dynamic
>>queries","parametrized queries" or something like that.
>>--

>Hey, give me _some_ credit 8-) , I've been there, done that, and it
>didn't work...what kind of database server are you talking to?

>Sybase only has a DATETIME datatype, not DATE, and something in the
>BDE (or further out in the great big chain of things) starts to squeal
>when I pass parameters to a DATETIME field using .AsDate! I quote:
>"General SQL error. [INTERSOLV][ODBC SQL Server driver] Timestamp
>parameters with zero scale must have a precision of 13, 16 or 19.
>Parameter number 1, precision: 0."

Yes, that's a bug listed as fixed in BDE 5.0.  I think it first appeared
in one of the 4.5x releases.

Quote
>(This example being an attemt to
>select count * from at table where records are timestamped with a
>certain date) .AsDatetime would solve this problem, probably, but
>might create others. Of course, there would be ways around those as
>well!

>But, anyway, the routine I described with string passing and CONVERT
>works - at least on Sybase!

--
Report all {*word*201} mail to your Potsmaster.

Re:Problem with DateTime on Delphi 3 Client/Server


On 3 Nov 1998 15:00:04 GMT, drifk...@acm.deleteme.org (David Rifkind)
wrote:

Quote
>>Hey, give me _some_ credit 8-) , I've been there, done that, and it
>>didn't work...what kind of database server are you talking to?

>>Sybase only has a DATETIME datatype, not DATE, and something in the
>>BDE (or further out in the great big chain of things) starts to squeal
>>when I pass parameters to a DATETIME field using .AsDate! I quote:
>>"General SQL error. [INTERSOLV][ODBC SQL Server driver] Timestamp
>>parameters with zero scale must have a precision of 13, 16 or 19.
>>Parameter number 1, precision: 0."

>Yes, that's a bug listed as fixed in BDE 5.0.  I think it first appeared
>in one of the 4.5x releases.

Hmmm, I guess I'll download and install BDE 5.0, then - it might make
life a tad easier! I'm using 4.50 now.

Thanks for the tip!

St?le Sannerud

Other Threads