PDA

View Full Version : Problems with "date" types in a MSSQL database



Pepe
16-Apr-2020, 02:28 AM
Hi,

I'm having problems with "date" types in a MSSQL database. This database is being used by DataFlex 19.0 applications. We've had this problem in the past and the problem was solved modifying the MSSQLDRV.int file before the database was populated. In this case, I think, that is not possible.

In the atachments in you can see some screen shots from the Mangment Studio and the same table and records viewed in Dynamic.

Any suggestions/ideas in how this can be solved?

TIA

Salu2

Pepe

Vincent Oorsprong
16-Apr-2020, 02:43 AM
Pepe,

I assume you don't want to have the 01-01-0001 dates in the report, right? If so, you need to suppress this value somehow. I don't know enough about DAI to guide you but I bet it is possible.

Pepe
16-Apr-2020, 02:57 AM
There is no problem in setting up 0001-01-01 to blanks in DAI. The problem is that DAI isn't considering "date" columns as "date" but as "text" and thus I can't do that either.

Any further idea/suggestion is welcome, so thanks Vincent but it doesn't solve the issue.

Salu2

Bob Cergol
16-Apr-2020, 07:58 AM
Hi Pepe,

While the 'DATE' type was introduced quite a long time ago, DAI won't see it as a date unless you CAST it as DateTime.

You can choose the zero date for DAI via the system parameter:
KEY: Application
KEYVAL: ZERODATE
KEYTYPE: DATE
DISPLAYVAL: 1753-01-01

You can only specify one date value for zero date. DF wants 1753-01-01 whereas most M/S apps use 1900-01-01.

Regards,
Bob

Pepe
16-Apr-2020, 09:17 AM
Hi Bob,

The ZERODATE is already set, in this case to 0001-01-01 but as DAI considers it as TEXT it doesn't make any difference on what I'm looking for.

The question is if there is any way, method, technique ... that I could do globally for the whole database so that DAI would consider those columns as DATE or DATETIME and avoiding:
1- to do a CAST every time we want to use a table.field that is suppose to be of type Date/datetime/datetime2 in DAI report or view
2- modifying the Dtflx 19.0 apps' behaviour
3- sacrifying DAI's performance :)



Regards

Pepe

Bob Cergol
16-Apr-2020, 10:22 AM
Unfortunately, the only solution is to cast the date as datetime. (I cannot defend that DAI V6 doesn't recognize MS-SQL DATE type as such -- since Microsoft introduced that type in 2008 as I recall.) That being said, the display and sorting of that DATE type in DAI reports does work as expected. You just don't get a date-picker on a filter using it -- unless you cast it as DateTime. AFAIK, casting date to datetime when needed is not going to impact performance. Have you experienced otherwise? I don't know why you are conflating Dataflex with Dynamic AI. They have nothing to do with each other - beyond the fact that DAI is a great solution for embedding BI, operational reporting, and ad-hoc reporting within DF Windows and webapps.
-Bob

Pepe
16-Apr-2020, 11:44 AM
While I can't find a solution I've tried to cast a couple of date columns in a DYN view to datetime. So far so good: now those columns are shown as propey date.


The problem comes when on a DYN report I extract the year from one of those columns and when grouping by year I get:


The conversion of a date data type to a datetime data type resulted in an out-of-range value.


On system parameters I've tried 3 system combinations for ZERODATE: 0001-01-01, 1753-01-01 and 1900-01-01.

Any idea how to solve this?


So far the proof of concept for this client isn't going well :(....

Regards

Pepe

Pepe
16-Apr-2020, 11:52 AM
have you experienced otherwise?

no. Just asking



I don't know why you are conflating Dataflex with Dynamic AI

Just if it was of any help. As mentioned when we set the MSSQLDRV.int correctly (not in this case) then the tables with date columns (Dataflex) are converted to Datetime and it works perfectly in DAI. Even with Moose2SQL, our own "EL" (Extract and Load) DataFlex conversion tool it gets converted correctly to DateTime.

Salu2

Pepe

Garret Mott
16-Apr-2020, 12:30 PM
DateTime (as opposed to DateTime2) in MS SQL can not go earlier than 1/1/1753.

Bob Cergol
16-Apr-2020, 12:40 PM
What you're saying is that you use Dataflex driver settings with Dataflex programs to migrate DATE type data to DATETIME type data. Dynamic AI only knows what it sees in the table -- and you have provided it with the type it expects as a date. Dynamic AI doesn't write any data, it just formats it for output, and in case of dates it recognizes as date data, it offers many functions for extracting date parts or formatting the date string in output. It does actually convert any data, unless you write an SQL formula to do that and use that formula-column in the report.
Regards,
Bob

Bob Cergol
16-Apr-2020, 12:51 PM
As Garrett said, blame Microsoft for supporting different min/max values in its various date data types. (Okay, those weren't his exact words.)

Whenever I have seen the out-of-range value error as you mention, it has alerted me to the presence of bad date values in the database. Surely no modern business application should have dates in years prior to 1753? :eek:

You might have to get creative if you truly need such old dates. You can keep them. Let them be DATE type, and you can extract the YEAR from them yourself as a formula column for use in groupers, filters, etc. Maybe something like: cast(YEAR(InvoiceDate) AS char(4))

Combinations of date values are not supported for DAI's ZERODATE parameter. It can only use a single value, and '1753-01-01' works for me, as has '1900-01-01' when working with Microsoft apps that tend to that as their zero date.

Bob

Garret Mott
16-Apr-2020, 12:55 PM
Close enough Bob! MS & all their different date values makes me crazy(er).

Pepe
16-Apr-2020, 01:11 PM
Thanks for the info. I didn't know that one ;)

Yes, the database needs to be cleaned but in the process of a "proof of concept" I don't want to go that deep.

I will try to cast to datetime2.

As regarding the system parameters I didn't mix them all: I just tested one by one.

Pepe

Samuel Pizarro
16-Apr-2020, 02:25 PM
Maybe he does not have real date before 1753.
but he might have the older limit “0001” (dummy zero dates) which now is a problem when he tries to cast it do datetime, which imposes a higher limit.

You may need to use a case expression to handle that if you plan to use a sql-view to cast the column.

Something like


case
When <date-col> < ‘1753-01-01’ cast(‘1753-01-01’, datetime)
Else cast(<date-col>, datetime)
end

ps. Expression created on the fly, not tested and not sure If I got the syntax correctly. But you got the idea!

Bob Cergol
16-Apr-2020, 03:06 PM
I don't know what to tell you Pepe.
I've never seen this not work in countless deployments over the past 12 years.
13642

Pepe
20-Apr-2020, 06:16 AM
Thanks all.


I'm having a go and try to solve the problem from the tables changing the types to datetime (DAI considers datetime2 as text ). I'm also finding that the tables are populated with data not verified which brings problems in BI and some of the wrong data is in the date columns!

My other thread about converting dates is https://support.dataaccess.com/Forums/showthread.php?65800-Aplication-impact-in-changing-column-types-from-date-to-datetime&p=354432#post354432


Salu2

Pepe

Pepe
27-Apr-2020, 06:41 AM
The reason for this thread arose from the fact that Dynamic recognises "date" types as string.

We were doing a proof of concept of using Dynamic as a BI tool for a client. They are not happy of the fact that "dates" are not "dates" in Dynamic and they are at the present evaluating other tools like Power BI and the proof of concept has stoped. Also I can't assert if this will be fixed in Version 7 or when it will be available.


For the time being we won't spend more time in this issue.

I would like to thank everyone for their solutions and suggestions.

Salu2

Pepe