Re: Default values messed up
Date field being treated like text fields means the SQL-Client is missing.
Re: Default values messed up
[QUOTE=Evertjan Dondergoor;338864]Date field being treated like text fields means the SQL-Client is missing.[/QUOTE]
With SQL client you mean?
There is a ODBC sql driver installed. 13.1 I think.
Skickat från min iPhone med Tapatalk
Re: Default values messed up
Yeah, that is what I mean. If you have issues with 'datetime2' or 'date' in 19 out of 20 cases it is because the SQL-client - which indeed isn't called SQL client anymore - is missing. If it is missing, datetime2 and date will behave like it is a string. I'm not up to date with D-AI though.
Re: Default values messed up
[QUOTE=Evertjan Dondergoor;338877]Yeah, that is what I mean. If you have issues with 'datetime2' or 'date' in 19 out of 20 cases it is because the SQL-client - which indeed isn't called SQL client anymore - is missing. If it is missing, datetime2 and date will behave like it is a string. I'm not up to date with D-AI though.[/QUOTE]
I looked, and ODBC SQL Server Driver 13 is installed. But there is also some older drivers installed.
Is Dynamic maybe choosing an older invalid driver for some reason?
Re: Default values messed up
Hi Albin,
You are correct that Dynamic AI is not seeing the 'DATE' type column as date data. It is a long-standing flaw that I have complained about to Cintac. Grouping and sorting would work just fine on the text date strings but you lose the date-picker and all the built-in date functions.
Your choices are:
1) Change the date types back to DateTime. The fact that you don't use the time portion is irrelevant -- they will all be emtpy values. This would be my choice.
2) Cast the date columns back to DateTime in either dyn-views and/or report formulas
Microsoft introduced this simpler date data type way back in 2008 so it is hard to explain why Cintac never supported. I am told it has something to do with the ADO driver they use.
Regards
Bob
Re: Default values messed up
[QUOTE=Bob Cergol;338908]Hi Albin,
You are correct that Dynamic AI is not seeing the 'DATE' type column as date data. It is a long-standing flaw that I have complained about to Cintac. Grouping and sorting would work just fine on the text date strings but you lose the date-picker and all the built-in date functions.
Your choices are:
1) Change the date types back to DateTime. The fact that you don't use the time portion is irrelevant -- they will all be emtpy values. This would be my choice.
2) Cast the date columns back to DateTime in either dyn-views and/or report formulas
Microsoft introduced this simpler date data type way back in 2008 so it is hard to explain why Cintac never supported. I am told it has something to do with the ADO driver they use.
Regards
Bob[/QUOTE]
Ok, thanks Bob.
I think I will go with option 1.
2 Attachment(s)
Re: Default values messed up
[QUOTE=Bob Cergol;338908]Hi Albin,
You are correct that Dynamic AI is not seeing the 'DATE' type column as date data. It is a long-standing flaw that I have complained about to Cintac. Grouping and sorting would work just fine on the text date strings but you lose the date-picker and all the built-in date functions.
Your choices are:
1) Change the date types back to DateTime. The fact that you don't use the time portion is irrelevant -- they will all be emtpy values. This would be my choice.
2) Cast the date columns back to DateTime in either dyn-views and/or report formulas
Microsoft introduced this simpler date data type way back in 2008 so it is hard to explain why Cintac never supported. I am told it has something to do with the ADO driver they use.
Regards
Bob[/QUOTE]
Bob or anyone else,
I have one report that refuses to play ball after these date conversions..
Maybe you coud tell me why because I just donīt get it.
It is a simple report showing the time we spent on different projects grouped by developers and date.
If I do a simple SQL View like this:
[CODE]
select User, Date, SUM(Time) as Time from MyTable where User IN ('001', '002', '003', '007', '008', '010')
GROUP BY User, Date
ORDER BY Date DESC
[/CODE]
I get a great report showing the data exactly like we want it. Like attached picture 1.
[ATTACH=CONFIG]12355[/ATTACH]
But since we also want to see this as a Graph (HC Line) we need to let Dynamic do the grouping so we change the SQL View code to:
[CODE]
select User, Date, Time from MyTable where User IN ('001', '002', '003', '007', '008', '010')
ORDER BY Date DESC
[/CODE]
Then we create a report with HC Line graph and groups on Date, User.
This results in duplicates.. I donīt get why, but if Dynamic is doing the grouping here we get a couple of duplicate rows per user. Picture below:
[ATTACH=CONFIG]12356[/ATTACH]
Have you seen anything like this?
Re: Default values messed up
Albin,
Are you saying the very same data gave you a different result [U]before[/U] you switched [U]from[/U] date-time to date type, than you are getting [U]after[/U] you switched back from date to date-time type?
See what you get if you remove the 'order by date' from your view, and made your date column a row grouper.
Also, I wonder, depending on the sequence of events with this report's design editing, relative to your changing data type on the backend, if in the metadata for the report it thinks the type is text for the date column versus date-time. Try removing the column from the report, saving it, then adding the column back.
Bob
Re: Default values messed up
[QUOTE=Bob Cergol;338983]Albin,
Are you saying the very same data gave you a different result [U]before[/U] you switched [U]from[/U] date-time to date type, than you are getting [U]after[/U] you switched back from date to date-time type?
See what you get if you remove the 'order by date' from your view, and made your date column a row grouper.
Also, I wonder, depending on the sequence of events with this report's design editing, relative to your changing data type on the backend, if in the metadata for the report it thinks the type is text for the date column versus date-time. Try removing the column from the report, saving it, then adding the column back.
Bob[/QUOTE]
Hi Bob.
Sorry for my late response.
Yes, [COLOR=#000000][I]the very same data gave me a different result [/I][/COLOR][U]before[/U][COLOR=#000000][I] I switched [/I][/COLOR][U]from[/U][COLOR=#000000][I] date-time to date type, than I was getting [/I][/COLOR][U]after[/U][COLOR=#000000][I] I switched back from date to date-time type
[/I][/COLOR]But I tried your example and put the date column as a row grouper, removed the order by date. This gave me the same result. So I also Removed the columns from my report, saved it. Put the columns back in my report and saved again.
NOW ITīS WORKING! Thanks Bob!