asked on

Delphi DateTime field in the database raises error.

I have a working system that uses SQL Express as the database.
On 2 computers, window 8.1, everything is okay.
I have just installed another computer with Windows 10 and the program brings an error when assigning MyQuery.FieldByName('MyDateField').AsDateTime to a DateTime variable.
It only happens on this computer.

I have installed the program on my development computer that is also Windows 10 and using SQL Express and have no problems.

The ShortDateFormat is the same on all computers.

1. The command ShowMessage(ShortDateFormat) displays the same on all computers 'd/MM/yyyy'
2. FieldByName('MyDateField').AsString on the working computers displays the date in the ShortDateFormat, like '31/12/1999'
3. FieldByName('MyDateField').AsString on the new computer displays the date as '1999-12-31'
4. FloatToStr(FieldByName('MyDateField').AsDateTime) on the new computer raises error "'1999-12-31' is not a valie date time."

All the settings are the same

I have never seen anything like that and I need your help here.

Using Delphi 7 and SDAC

Semper Phi

Delphi 7 has issues with UAC in Windows 10. What is the UAC set to on the programming system?

Geert G

Nothing to do with UAC

Geert G

obviously the date formats are different

Have you checked LongDateFormat ?
When i insert a date in a database, and want to make sure it always works. I give a string and the format of that string

SQL Server has convert/cast for this:
https://docs.microsoft.com/en-us/sql/t-sql/functions/cast-and-convert-transact-sql?view=sql-server-2017

the problem is within Delphi: you are converting a string to a datetime field

graga

ASKER

Agree with Geert G, works okay on my W10. I had similar problem years ago with a Delphi program getting a different format from SQL Server and SQL Server Express but can't remember if / how it was resolved. I think we just upgraded to full SQL Server.
BTW, my machine has both, SQL Server and Express installed, Maybe something there?

Geert G

coming back on UAC for a sec:

When Delphi is installed under "c:\program files"
Delphi modifies it's files during use, and as such wants Elevated privilges to run
All executes thus produced require elevated privs to run.

Solution:
Don't install Delphi under "c:\program files"

Geert G

my sql server memories are somewhat rusty
i work on oracle since 2005.

on oracle i can run these statements:

alter session set nls_date_format='dd/mm/yyyy';

or 
alter session set nls_date_format='mm/dd/yyyy'; -- if you really want to use the american format

Open in new window

on sql server:
https://docs.microsoft.com/en-us/sql/t-sql/statements/set-dateformat-transact-sql?view=sql-server-2017

the problem is usually storing a date field as it's string representation in the database:
> FieldByName('MyDateField').AsString > in database

that causes the problem

graga

ASKER

Thank you for your contributions. It's getting late here in Australia so I'm off to bed. I will start early in the morning and I will check UAC

ASKER CERTIFIED SOLUTION

graga

membership

This solution is only available to members.

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

Start Free Trial

Geert G

a wrong driver
my rusty memory seems to remember the same problem on SQL 7 with some dll's to replace for the BDE

glad you found it :)

to close the question accept your own answer as the solution

graga

ASKER

Yea, it's often the case, I had the same problem years ago and solved it, I'm too old for this, time to retire ... :(