Date format in SQL

[Jacques]

I have seen that the date format in Dynamic AI SQL is 'MM/DD/YYYY' like
select * from dos where a3 < '01/25/1993' order by a3

Is is a fixed format or is there a function to manage the date (like to_date in Oracle and SQL server) ?
The regional setting of my computer is 'DD/MM/YYYY' and the date in the report are showed like that.

Is there a help screen showing all possible functions in the SQL command for text (trim, pad, ...), number or date ?

[Bob Cergol]

Hi Jacques,

In the server edition you can define date format in the user profile as shown here.

DAI_User_Profile_Acct_Tab.png (36.87 KiB) Viewed 8963 times

The specific SQL syntax you use in views and formulas in extra columns is specific to the backend, for example MS-SQL, MY-SQL, and Sybase have variations in allowed syntax. Dynamic AI does make some adjustments and optimizations for the different back-ends, but you need to use the correct syntax when writing you own SQL in views and formulas.

In MS-SQL this would also work:
select * from dos where a3 < '1993-01-25' order by a3

The yyyy-mm-dd format is more universal among backends so it may be a better choice in case you would export your reports repository to another system using the same data structures in a different backend.

There is a "help" link in the menu bar that accesses the on-line documentation. I don't remember if that same link shows in the personal edition or not -- though I should.

DAI_Menu_Bar.png (1.86 KiB) Viewed 8963 times

Regards,
Bob

[Jacques]

In personal edition, we have the same help.
The problem is that there are no possibility to find in the help or somewhere in the select screen all format function available for the SQL command.
I have found (by trying) that it was possible to use as functions
- 'upper', 'lower', 'ltrim' for text fields but not 'trim', 'pad', 'lpad', 'mid'
- 'month' for date fields.

In personal edition, I can change the date format of the user but I don't see any effect on the report layout or the date in select. In select, the only valable date is 'MM/DD/YYYY'

[admin]

Hi Jacques,

I am not sure I understand if you have a problem that prevents you from applying the date filter you need -so you are not able to see the correct data, or if you "just" have a cosmetic problem of your dates being displayed in a different format than your preferred.

Dynamic AI does not have it's own format. The software is meant to talk to any backend database (oracle, ms-sql etc.) and the user should ideally not need to know much about the backend or it's configuration or how parameters are passed.

This is also why we can't supply a list of functions, as these differ greatly among the supported platforms. Dynamic AI provides some, and allows you to also use the database native functions (like to_date on oracle and cast/convert on sql server).

Maybe your initial problem was solved, but we'd still like to hear from you.

Best regards

Bo

[Jacques]

The problem with the date is that the format of the date isn't this of the user on windows even well if it's written the 'default' date in the parameter of the user.
It take only the date specified in the parameter of the user. That means that we don't have to forget to give the date format of the user in Dynamic AI.

Jacques

[Jacques]

Another thing to know is that in the "Free SQL condition", we have to write (for SQL server)
'A3>convert(datetime,'01/01/1998',103)' (A3>01/01/1998 doesn't work but well in the table above)

The date format, decimal symbol have always been a problem in Europe, specially in international company where the programs must run in USA and in Europe.

I think that Dynamic AI can make report with data's coming from different database and I wonder to know how the date would be selected in the "Free SQL condition". It's the reason I thought that in the report, the data's were selected after retriving and, in this way, the conditions script were database independant.

Jacques