SQL result type CASE WHEN... THEN... expected as a date but is integer

[Hypno]

I have a question about the statement
CASE WHEN .... THEN ...END
in the SQL query.
I have this construction (essential SQL snippet):

case
when "osoba"."orz_kraj"='PL' and "osoba"."orz_dat_wyd" is not null then "osoba"."orz_dat_wyd"
when not("osoba"."orz_kraj"='PL' and "osoba"."orz_dat_wyd" is not null) and ("niepelnospr"."orz_kraj"='PL' and "niepelnospr"."orz_data" is not null) then "niepelnospr"."orz_data"
end as "orz_data1",
"osoba"."orz_dat_wyd", "niepelnospr"."orz_data"

The condition is based on date fields and as a result also gives a date field as a result.... theoretically....
Unfortunately, as a result, I get an INTEGER field (which somewhat corresponds to the date, but that's not what I mean). And I can't get SQL to return data as a date for anything, the CAST(...as Date) function doesn't change anything in this case. Whatever I would do, I still get the result as in the picture below. As you can see, the fields included in the condition are dates, but the result, unfortunately, is not.

The conditions work as they should, but the format of the result is incorrect. How to force SQL to return the result as a date?

[Villeroy]

When you use that integer in a form's formatted control or date control or report, you can format the number as date. Internally, Base converts the database dates into day numbers. These day numbers work like the dates in spreadsheet cells.
1899-12-29 = -1
1899-12-30 = 0
1899-12-31 = 1
1900-01-01 = 0
etc.

[Hypno]

We don't understand each other, the table in the picture is not a piece of the Calc table, it is the result of the SQL query (the important part of which is quoted above). In my tables in base all dates are in date format fields and not Integer.
Interestingly, an interesting snippet with a little addition shows the result, which looks like it should, which means that the result field is a date, but because I'm using the TO_CHAR function, I get the result in the form of text, not a date.

TO_CHAR((case when "osoba"."orz_kraj"='PL' and "osoba"."orz_dat_wyd" is not null then "osoba"."orz_dat_wyd"
when not("osoba"."orz_kraj"='PL' and "osoba"."orz_dat_wyd" is not null) and ("niepelnospr"."orz_kraj"='PL' and "niepelnospr"."orz_data" is not null) then "niepelnospr"."orz_data" end), 'YYYY-MM-DD') as "orz_dat_wyd"
..is OK

And if I want to convert this text using CAST to a date, I will get the result as an INTEGER again.

CAST((TO_CHAR((case when "osoba"."orz_kraj"='PL' and "osoba"."orz_dat_wyd" is not null then "osoba"."orz_dat_wyd"
when not("osoba"."orz_kraj"='PL' and "osoba"."orz_dat_wyd" is not null) and ("niepelnospr"."orz_kraj"='PL' and "niepelnospr"."orz_data" is not null) then "niepelnospr"."orz_data" end), 'YYYY-MM-DD')) as DATE) as "orz_dat_wyd"

Something is messed up in the SQL interpreter for me...

[UnklDonald418]

Dates are be confusing.
Dates are stored in the database as a Double Value. The whole number part is the days since (or before if negative) Dec 30, 1899
When the Base front end displays the results of a query involving a field of Date type, it converts that number into a more readable format.
When you CAST(Something as DATE) the result is the native whole number portion of the value which is what the Base front end displays, as seen on your screen shot.

Unfortunately, the version of HSQL used by Embedded databases lacks some the the date manipulation functions such as TO_DATE, so your options are more limited.

As Villeroy suggested, displaying the results on a Base form with a Date Control allows you the greatest range of formatting options, similar to the options available on a spreadsheet cell.
Another option that might work for you is to give the Base front end another crack at formatting that field

Code: Select all

SELECT * FROM (Your query)

will often reformat the numerical values to more readable dates.

Edit: Actually TO_CHAR could convert the result of the CAST into a recognizable format but it too is only available in later versions of HSQL

[Hypno]

Forms are secondary for me, the most important thing is that the queries work as they should, and for now I sometimes struggle with simple problems such as converting dates.
Thanks for the hint.
Still, it would be good to know how to "add" an empty field in the query, which will be treated as a date field but without the date entered, so that you don't have to mess with the table with an empty date field....

As I miss the ability to create directories to group tables and queries, especially queries, with a large number of them it would definitely make the work easier. A simple analyzer would also be useful, which after clicking on the table would show the path of queries and tables used by a given object and which are used by other objects...

[Villeroy]

The queries do work as they should. It is just a formatting issue (good values, poor display). Let the form wizard create a most simple form from the query and you will get the right formatting in a table grid (right-click on query, form wizard, select columns and click "Finish"). If the column is still a number, open for editing, right-click column, replace with date control.
When importing the query to Writer or Calc, you can format the numbers as date.

[chrisb]

how to "add" an empty field in the query, which will be treated as a date field

well that's simple:

Code: Select all

cast(null as date) "EmptyDateField"

did you try executing your queries in direct mode?
1) 'Create Query in SQL View...'
2) activate the SQL icon on the toolbar
3) paste your code & execute.

to_char simply formats a value as a string & is used for presentation, it does not alter underlying values.

you can format query results temporarily by right clicking the field header & selecting 'Column Format'.

query results are often displayed in forms or reports where formatting is easy.

[Cazer]

I can understand your frustration with date conversion. As @chrisb pointed out, if you want to add an empty field in your query that will be treated as a date, you can use cast(null as date) "EmptyDateField".
For managing your tables and queries, currently, there's no built-in way in SQL to create directories for grouping, but you could consider using naming conventions to help organize your work.
And about the query analyzer feature you mentioned, while it's not a part of LibreOffice, you may find third-party SQL tools helpful for this kind of analysis.
Lastly, remember that SQL is behaving as it should. It's more of a formatting issue that's confusing. You can handle the formatting in the final display of the data, such as in a form or report.