Convert yyyy-mm-dd to dd/mm/yy in query SQL

Ratslinger · February 9, 2022, 12:45am

Thank You. Works fine. Done previously but forgotten. Result:

Screenshot at 2022-02-08 16-51-47

create function convert_date_fmt (d date) returns
varchar(10) -- unnamed return value
 as
 declare DSTRING varchar(10);
 begin
DSTRING = lpad(extract(day from d), 2, '0') ||'/'||
   lpad(extract(month from d), 2, '0')||'/'||
   lpad(extract(year from d), 2, '0');
 return DSTRING;
end

Example deleting function:
	drop function convert_date_fmt

Example of use:
	select convert_date_format("Case DoB") From "TblYrList"

Create & Drop statements should be run from main form menu Tools->SQL.

Just execute Create once. No need to use Drop unless Create is to be redone. Can also Alter function vs Drop/Create.

papasierra · February 9, 2022, 9:01pm

Thank you for all your solutions. For some reason LPAD would not work so I went with:

SELECT CONCAT( extract(day from “Case DoB”) || ‘/’ || extract(month from “Case DoB”) || ‘/’ || extract(year from “Case DoB”) , ', ')FROM “TblEvent”

This keeps the programming on a simple basis for a simple mind.
Thanks again!

Ratslinger · February 9, 2022, 9:28pm

Should be no reason as was tested. Please post the SQL which did not work.

papasierra · February 9, 2022, 9:40pm

Good evening Ratslinger thank you for your interest. I tried:

SELECT CONCAT(lpad(extract(day from “Case DoB”), 2, ‘0’) ||’/’|| lpad(extract(month from “Case DoB”), 2, ‘0’) ||’/’|| lpad(extract(year from “Case DoB”), 2, ‘0’) , ', ’ ) FROM “TblEvent”

And had the following error:

Access is denied: LPAD in statement [SELECT CONCAT( lpad( EXTRACT( DAY FROM “Case DoB” ), 2, ‘0’ ) || ‘/’ || lpad( EXTRACT( MONTH FROM “Case DoB” ), 2, ‘0’ ) || ‘/’ || lpad( EXTRACT( YEAR FROM “Case DoB” ), 2, ‘0’ ), ', ’ ) FROM “TblEvent”]

Ratslinger · February 9, 2022, 9:57pm

Please stop posting as an answer. It does not answer the original question. Post as a comment under where you are responding to. Also your code entry causes the quotes and apostrophes to be unusable. Instead surround code with three back ticks to start, code on new line and three more back ticks on new line after code.
.
Will look further at your SQL (error indicates something may be incorrect elsewhere) but I get a different error - CONCAT unknown function.
.
Do not understand why you strayed from the original SQL- it was tested and works. Concatenation is done with pipes ||
.
If you are not going to use the function provided then from original:

Select lpad(extract(day from "Case DoB"), 2, '0') ||'/'||
   lpad(extract(month from "Case DoB"), 2, '0')||'/'||
   lpad(extract(year from "Case DoB"), 2, '0')
    From "TblEvent"

Should be able to copy/paste this.
.
And if that still doesn’t work there may be something else. In that case please post a sample Base file with the error reproducible.

papasierra · February 9, 2022, 10:26pm

Very sorry about this, I had not understood the difference between the two methods of answering.

I tried the SQL you suggested but still got the error:

Access is denied: LPAD in statement [SELECT lpad( EXTRACT( DAY FROM "Case DoB" ), 2, '0' ) || '/' || lpad( EXTRACT( MONTH FROM "Case DoB" ), 2, '0' ) || '/' || lpad( EXTRACT( YEAR FROM "Case DoB" ), 2, '0' ) FROM "TblEvent"]

Thank you for the advice about CONCAT(). I have removed all of these and replaced them with ||

Ratslinger · February 9, 2022, 10:48pm

Where is the sample I asked for if you continued to have an issue? I just tested the code in your error as have no issue with it:

Screenshot at 2022-02-09 14-54-29
and Case DOB is a Date field in the table?

Ratslinger · February 9, 2022, 11:08pm

Are you certain you are using a Firebird database? CONCAT is not in Firebird. Check the bottom center line of the opening Base screen.

papasierra · February 9, 2022, 11:27pm

Your suspicion is correct,this is an HSQLDB. I think the upgrade to LO v7 may have turned off the Experimental setting because this database was created more recently than my other databases that are Firebird ones.
I will endeavour to convert the HSQLDB into Firebird and try the LPAD on that.
Thank you again for your help.

Ratslinger · February 10, 2022, 1:30am

Possibly not. Some older LO versions had Firebird as the default. This was changed back to HSQLDB when there were too many conversion issues. Currently you need Experimental on to create a new Firebird file or convert an HSQLDB one to Firebird.

papasierra · February 15, 2022, 10:52pm

Hi Ratslinger, I feel very frustrated that I can’t work this out. I have converted the HSQLDB to Firebird. I have to update the queries because some of the functions are not the same.
Not surprisingly the LPAD is working (thanks for that advice).
However CHAR(10) is not working in Firebird and I have searched for a solution but cannot find anyone else having the same issue.
Here is what I am using in a query:

SELECT

IIF

(

    "TblEvent"."Sample ID" IS NOT NULL, 'Sample ID ' || "TblEvent"."Sample ID" || ', ' , ''

)

||

IIF

(

    "TblEvent"."PHE notify" = 'PHE Consent NOT given', 'Consent not given for ID release to PHE.',

    IIF

    (

        "TblEvent"."PHE notify" = 'PHE ID sent via RFID', 'PHE ID sent via RFID.',

            IIF

            (

                "TblEvent"."PHE notify" = 'Convalescent PCR','Convalescent PCR ID not needed.',

                IIF

                (

                    "TblEvent"."PHE notify" = 'Not resident in England', 'No need to notify PHE because not resident in England.',

                    (

                        IIF

                        (

                            "TblEvent"."Case name" IS NOT NULL, "TblEvent"."Case name" || ', ', 'Name withheld, '

                        )

                        ||

                        IIF

                        (

                             "TblEvent"."Case DoB" IS NOT NULL, LPAD(EXTRACT(DAY FROM "TblEvent"."Case DoB"), 2, '0') || '/' || LPAD(EXTRACT(MONTH FROM "TblEvent"."Case DoB"), 2, '0')|| '/' || LPAD(EXTRACT(YEAR FROM "TblEvent"."Case DoB"), 2, '0') || ', ', 'DoB withheld, '

                        )

                        ||

                        IIF

                        (

                            "TblEvent"."Case Tel" IS NOT NULL, "TblEvent"."Case Tel" || ', ', 'Tel No withheld, '

                        )

                        ||

                        IIF

                        (

                            "TblEvent"."Add1" IS NOT NULL, "TblEvent"."Add1" || ', ', 'Address withheld, '

                        )

                        ||

                        IIF

                        (

                            "TblEvent"."Postcode" IS NOT NULL, "TblEvent"."Postcode" || '. ', 'Postcode withheld. '

                        )

                    )

                    ||

                    IIF

                    (

                        "TblEvent"."PHE notify" = 'PHE Consent given', '(Consent to release ID to PHE given)', ''

                    )

                )

            )

    )

)

||

CHAR(10)

||

IIF

(

    "TblEvent"."Details" IS NOT NULL, 'Details: ' || "TblEvent"."Details", ''

)

AS "Export"

FROM "TblEvent"

WHERE "event_ID" = :ID_value

When the CHAR(10) is in the query I get an error:

firebird_sdbc error:
*Dynamic SQL Error
*SQL error code = -104
*Token unknown - line 1, column 1246
*CHAR
caused by
'isc_dsql_prepare'

And when || CHAR(10) is removed the query works as expected.
Any advice very gratefully received!

Ratslinger · February 15, 2022, 10:58pm

S/B Chr(10)

papasierra · February 15, 2022, 11:00pm

Thank you for your rapid reply. Unfortunately I get the error:

firebird_sdbc error:
*Dynamic SQL Error
*SQL error code = -804
*Function unknown
*CHR
caused by
'isc_dsql_prepare'

Ratslinger · February 15, 2022, 11:07pm

May have misunderstood. Try ASCII_CHAR( 10 )

papasierra · February 15, 2022, 11:19pm

On the button, ASCII_CHAR(10) works as expected.
While I have your attention, please could you advise if there is a neater way of compressing the multiple IIF statements please? Perhaps a different notation to IIF? The full query I am using is:

SELECT

IIF

(

    "TblEvent"."Sample ID" IS NOT NULL, 'Sample ID ' || "TblEvent"."Sample ID" || ', ' , ''

)

||

IIF

(

    "TblEvent"."PHE notify" = 'PHE Consent NOT given', 'Consent not given for ID release to PHE.',

    IIF

    (

        "TblEvent"."PHE notify" = 'PHE ID sent via RFID', 'PHE ID sent via RFID.',

            IIF

            (

                "TblEvent"."PHE notify" = 'Convalescent PCR','Convalescent PCR ID not needed.',

                IIF

                (

                    "TblEvent"."PHE notify" = 'Not resident in England', 'No need to notify PHE because not resident in England.',

                    (

                        IIF

                        (

                            "TblEvent"."Case name" IS NOT NULL, "TblEvent"."Case name" || ', ', 'Name withheld, '

                        )

                        ||

                        IIF

                        (

                             "TblEvent"."Case DoB" IS NOT NULL, LPAD(EXTRACT(DAY FROM "TblEvent"."Case DoB"), 2, '0') || '/' || LPAD(EXTRACT(MONTH FROM "TblEvent"."Case DoB"), 2, '0')|| '/' || LPAD(EXTRACT(YEAR FROM "TblEvent"."Case DoB"), 2, '0') || ', ', 'DoB withheld, '

                        )

                        ||

                        IIF

                        (

                            "TblEvent"."Case Tel" IS NOT NULL, "TblEvent"."Case Tel" || ', ', 'Tel No withheld, '

                        )

                        ||

                        IIF

                        (

                            "TblEvent"."Add1" IS NOT NULL, "TblEvent"."Add1" || ', ', 'Address withheld, '

                        )

                        ||

                        IIF

                        (

                            "TblEvent"."Postcode" IS NOT NULL, "TblEvent"."Postcode" || '. ', 'Postcode withheld. '

                        )

                    )

                    ||

                    IIF

                    (

                        "TblEvent"."PHE notify" = 'PHE Consent given', '(Consent to release ID to PHE given)', ''

                    )

                )

            )

    )

)

||

ASCII_CHAR(10)

||

IIF

(

    "TblEvent"."Details" IS NOT NULL, 'Details: ' || "TblEvent"."Details", ''

)

||

ASCII_CHAR(10)

||

IIF

(

    "TblEvent"."Vaccine status" IS NOT NULL,

        IIF

        (

            "TblEvent"."Vaccine status" = 0, 'Unvaccinated',

            IIF

            (

                "TblEvent"."Vaccine status" = 1, 'Single vacc < 2w ago',

            IIF

            (

                "TblEvent"."Vaccine status" = 2, 'Single vacc > 2w ago',

            IIF

            (

                "TblEvent"."Vaccine status" = 3, 'Double vacc < 2w ago',

            IIF

            (

                "TblEvent"."Vaccine status" = 4, 'Double vacc > 2w ago',

            IIF

            (

                "TblEvent"."Vaccine status" = 5, 'Double vacc + booster',

            IIF

            (

                "TblEvent"."Vaccine status" = 6, 'Vacc status unknown'|| ASCII_CHAR( 10 ), ''

            )

        )

||

IIF

(

    "TblEvent"."Advice" IS NOT NULL, 'Advice: ' || "TblEvent"."Advice", ''

)

AS "Export"

FROM "TblEvent"

WHERE "event_ID" = :ID_value

I haven’t quite got the following to work yet because I can’t work out how to end the nested IIF statements in the following part:

IIF

(

    "TblEvent"."Vaccine status" IS NOT NULL,

        IIF

        (

            "TblEvent"."Vaccine status" = 0, 'Unvaccinated',

            IIF

            (

                "TblEvent"."Vaccine status" = 1, 'Single vacc < 2w ago',

            IIF

            (

                "TblEvent"."Vaccine status" = 2, 'Single vacc > 2w ago',

            IIF

            (

                "TblEvent"."Vaccine status" = 3, 'Double vacc < 2w ago',

            IIF

            (

                "TblEvent"."Vaccine status" = 4, 'Double vacc > 2w ago',

            IIF

            (

                "TblEvent"."Vaccine status" = 5, 'Double vacc + booster',

            IIF

            (

                "TblEvent"."Vaccine status" = 6, 'Vacc status unknown'|| ASCII_CHAR( 10 ), ''

            )

        )

Ratslinger · February 15, 2022, 11:22pm

Have not tried to analyze your statement but here is what I see:

SELECT IIF("TblEvent"."Sample ID" IS NOT NULL,'Sample ID ' || "TblEvent"."Sample ID" || ', ','') || 
          IIF("TblEvent"."PHE notify" = 'PHE Consent NOT given','Consent not given for ID release to PHE.',
              IIF ("TblEvent"."PHE notify" = 'PHE ID sent via RFID','PHE ID sent via RFID.',
                 IIF ("TblEvent"."PHE notify" = 'Convalescent PCR','Convalescent PCR ID not needed.',
                     IIF ("TblEvent"."PHE notify" = 'Not resident in England','No need to notify PHE because not resident in England.',
                         (IIF ("TblEvent"."Case name" IS NOT NULL,"TblEvent"."Case name" || ', ','Name withheld, ') || 
                             IIF ("TblEvent"."Case DoB" IS NOT NULL,LPAD (EXTRACT(DAY FROM "TblEvent"."Case DoB"),2,'0') || '/' || LPAD (EXTRACT(MONTH FROM "TblEvent"."Case DoB"),2,'0') || '/' || LPAD (EXTRACT(YEAR FROM "TblEvent"."Case DoB"),2,'0') || ', ','DoB withheld, ') || 
                             IIF ("TblEvent"."Case Tel" IS NOT NULL,"TblEvent"."Case Tel" || ', ','Tel No withheld, ') || 
                             IIF ("TblEvent"."Add1" IS NOT NULL,"TblEvent"."Add1" || ', ','Address withheld, ') || 
                             IIF ("TblEvent"."Postcode" IS NOT NULL,"TblEvent"."Postcode" || '. ','Postcode withheld. ')) || 
                                  IIF ("TblEvent"."PHE notify" = 'PHE Consent given','(Consent to release ID to PHE given)',''))))) ||CHAR(10) || 
                                       IIF("TblEvent"."Details" IS NOT NULL,'Details: ' || "TblEvent"."Details",'') AS "Export"
FROM "TblEvent"
WHERE "event_ID" = :ID_value

papasierra · February 15, 2022, 11:29pm

Thank you for the neater display. I was also wondering if there was some sort of array arrangement I could use as an alternative to the second lot of IIF statements because the result depends on the value of “TblEvent”.“Vaccine status”. The choices of values are 1-6, and each integer needs to output a specified string (eg integer 1 → ‘Single vacc < 2w ago’).
If there is not a neater way of doing it I will keep wrestling with the nesting so it outputs the required string and not an error.
Thanks again.

Ratslinger · February 15, 2022, 11:32pm

This question is getting off topic. Please ask as a new question for others to find results. Asking here never presents this question easily visible to others seeking a similar answer.

Edit: Not much else - Case may fit in places.

papasierra · February 15, 2022, 11:34pm

No problem Ratslinger I understand, I will spend a bit more wrestling time on this and post a new question if I can’t solve it.
Thanks again for the ASCII_CHAR() answer, much appreciated.

Villeroy · February 16, 2022, 11:01pm

The number format of a value dispayed in a table or view does not matter at all if only the displayed value is correct. The grid view of a table or query is nothing more than a view on raw data.
Forms and reports constitute the user interface of a database. The controls in forms and reports allow any number format for dates, times and decimals.