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

English
#1

I am struggling to get a date expressed in the format dd/mm/yy rather than the native yyyy-mm-dd.
I am using Firebird.

The search

SELECT CONCAT( “TblEvent”.“Case DoB”, ', ’ ) FROM “TblEvent”

gives the date as yyyy-mm-dd. I would like the format to be in my locale which is dd/mm/yy or even dd-mm-yy.

The search

SELECT “TblEvent”.“Case DoB” FROM “TblEvent”

gives the date in the desired format.

I have tried using FORMAT( “TblEvent”.“Case DoB”, ‘d’, ‘en-gb’), but I get an error stating:

FORMAT is not recognised: Access is denied: FORMAT in statement [SELECT CONCAT( FORMAT( “TblEvent”.“Case DoB”, ‘d’, ‘en-gb’ ), ', ’ ) FROM “TblEvent”]

Thank you for your advice.

Version: 7.2.5.2 (x64) / LibreOffice Community
Build ID: 499f9727c189e6ef3471021d6132d4c694f357e5
CPU threads: 4; OS: Windows 10.0 Build 19043; UI render: Skia/Raster; VCL: win
Locale: en-GB (en_GB); UI: en-GB
Calc: threaded

Version: 7.2.5.2 (x64) / LibreOffice Community
Build ID: 499f9727c189e6ef3471021d6132d4c694f357e5
CPU threads: 4; OS: Windows 10.0 Build 19043; UI render: Skia/Raster; VCL: win
Locale: en-GB (en_GB); UI: en-GB
Calc: threaded

#2

Hello,

There is no FORMAT in Firebird. For SQL you should always refer to the documentation of the database you are using (Firebird embedded is v 3.x). There is a link to that documentation in my post here → Firebird Migration Expectations

Based upon → How to get date format as “yyyy mm/dd”? this should work for you:

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 "TblYrList"

You should avoid using spaces in naming of all kinds. Sooner or later it will be a headache. Use underscores in its’ place if needed.

2 Likes
#3

Perhaps creating a user function for that, allows simplifying the selects statements.

https://firebirdsql.org/file/documentation/html/en/refdocs/fblangref30/firebird-30-language-reference.html#fblangref30-ddl-func-create

1 Like
#4

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.

1 Like
#5

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!

#6

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

#7

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”]

#8

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.

#9

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 ||

#10

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?

#11

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

#12

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.

#13

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.

#14

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!

#15

S/B Chr(10)

#16

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'
#17

May have misunderstood. Try ASCII_CHAR( 10 )

#18

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 ), ''

            )

        )
#19

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
#20

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.

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.