MONTHNAME problem

Creating tables and queries

MONTHNAME problem

Postby arfgh » Wed Apr 20, 2016 2:45 pm

using this:

Code: Select all   Expand viewCollapse view
MONTHNAME( CURDATE() )


we can obtain 'April', that is fine, but how to obtain the name translated to the system language ?

thx in advance
OpenOffice last version | Mageia Linux x64 | Windows 8.1 Enterprise x64 | Windows XP x64 SP2 | Java 1.8.0_231
arfgh
 
Posts: 496
Joined: Tue Mar 05, 2013 6:44 pm

Re: MONTHNAME problem

Postby Villeroy » Wed Apr 20, 2016 2:59 pm

Format the form control or report field to show the month name (number format code MMMM)
Or create a table with numbers from 1 to 12 and month names. SELECT "Name" FROM "Months" WHERE "Num"=MONTH(Current_Date)
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.4
User avatar
Villeroy
Volunteer
 
Posts: 27729
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: MONTHNAME problem

Postby arfgh » Wed Apr 20, 2016 3:18 pm

explain the first way with an example, Villeroy.
OpenOffice last version | Mageia Linux x64 | Windows 8.1 Enterprise x64 | Windows XP x64 SP2 | Java 1.8.0_231
arfgh
 
Posts: 496
Joined: Tue Mar 05, 2013 6:44 pm

Re: MONTHNAME problem

Postby Villeroy » Wed Apr 20, 2016 3:49 pm

1) In a form:
1a) Use a Writer text field to show current date with number format MMMM
1b) Use a form control of type "formatted field" and apply number format MMMM

2) In a report:
2a) same as 1a)
2b) The old school report consists of a Writer table. In design mode you can modify the number format of the table.
2c) Report builder extension creates reports made of form controls. Format a formatted field.
2d) Use Calc for your reports (which could also solve your other problem with dateadd and HSQL 1.8)
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.4
User avatar
Villeroy
Volunteer
 
Posts: 27729
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: MONTHNAME problem

Postby arfgh » Wed Apr 20, 2016 4:08 pm

it is just a query command, so no forms or reports related.
OpenOffice last version | Mageia Linux x64 | Windows 8.1 Enterprise x64 | Windows XP x64 SP2 | Java 1.8.0_231
arfgh
 
Posts: 496
Joined: Tue Mar 05, 2013 6:44 pm

Re: MONTHNAME problem

Postby charlie.it » Wed Apr 20, 2016 4:16 pm

Ciao, you can use this code, although a bit long:

Code: Select all   Expand viewCollapse view
CASE WHEN MONTH( NOW( ) ) = 1 THEN ' Gennaio' WHEN MONTH( NOW( ) ) = 2 THEN ' Febbraio'  WHEN MONTH( NOW( ) ) = ……. ELSE ' ' END
charlie
macOS 10.12 Sierra: Open Office 4.1.7 - LibreOffice 6.2.8

http://www.charlieopenoffice.altervista.org
User avatar
charlie.it
Volunteer
 
Posts: 331
Joined: Wed Aug 21, 2013 2:12 pm
Location: Italy

Re: MONTHNAME problem

Postby Villeroy » Wed Apr 20, 2016 6:29 pm

arfgh wrote:it is just a query command, so no forms or reports related.


Dump it into a simple grid form and replace the date field with a formatted field with format code MMMM
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.4
User avatar
Villeroy
Volunteer
 
Posts: 27729
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: MONTHNAME problem

Postby Villeroy » Wed Apr 20, 2016 6:36 pm

charlie.it wrote:Ciao, you can use this code, although a bit long:

Code: Select all   Expand viewCollapse view
CASE WHEN MONTH( NOW( ) ) = 1 THEN ' Gennaio' WHEN MONTH( NOW( ) ) = 2 THEN ' Febbraio'  WHEN MONTH( NOW( ) ) = ……. ELSE ' ' END

In direct SQL mode you can shorten this:
Code: Select all   Expand viewCollapse view
CASE MONTH( "D" ) WHEN 1 THEN 'Gennaio'' WHEN 2 THEN 'Febbraio'  WHEN 3 THEN 'Marzo' ELSE 'posteriomente' END AS "Mese"
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.4
User avatar
Villeroy
Volunteer
 
Posts: 27729
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany


Return to Tables & Queries

Who is online

Users browsing this forum: No registered users and 5 guests