I am doing a libreoffice base report. I have a data field that can contain the numbers from 1-12 representing the months of the year. I want to display these months as their corresponding name, i.e. 6 will show as June. Is there any way to do this?
Hello,
This may be different (and easier) if not using HSQLDB embedded database. Using SQL cast the number to a Date:
Select Cast('2000-' || firstname || '-1' As Date) firstnames, id, lastname from users
Then set the report field to just display the spelled out month:
resulting in:
My original question should have been more clear. I wanted to somehow do the conversion right in the Report Editor as my SQL is already complex; but I will give this a try
I like the
SUBSTRING('JAN FEB MAR APR MAY JUN JUL AUG SEP OCT NOV DEC ', (@intMonth * 4) - 3, 3)
from Stackoverflow
but you find several solutions there and you can always use a CASE WHEN to generate the Name of the month directly in SQL.
As the using of substring is not localised, conversion to a date-field is the better solution on the long term.
J
Okay, I figured it out. I had tried this before, but must have had some syntax issues. In the Report control, set the Data Field value to :
CHOOSE([MonthNum];“January”;“February”;“March”;“April”;“May”;“June”;“July”;“August”;“September”;“October”;“November”;“December”)
where [MonthNum] is an integer field from the database
January does not show. Only displays blank.
This did work for me:
CHOOSE([month_entered]+ 1; "";January";"February";"March";"April";"May";"June";"July";"August";"September";"October";"November";"December")Your right, I assumed my data was bad; but double-checked and sure enough the data was good, but January was a problem. I am going to put your correction into place now.