How to get the month of a date with two digits in SQL-92?

Question

I have a date format cell in my table, of which I need to extract the month in two digits. This means that 6-4-2021 should return 04. The main problem is that this needs to be in the syntax of SQL-92. Any ideas on how to do this?

Answer 1

SQL-92

substring(
    '0' || substring(<column> from position('-' in <column>) + 1 for 3)
    from position(
        '-' in 
        '0' || substring(<column> from position('-' in <column>) + 1 for 3)
    ) - 2
    for 2
) 

I had wondered if you're were really looking for ODBC functions. If so then locate() would be the equivalent for position(), noting that the argument syntax just uses comma separators. replace() would then also be available for an alternate approach.

Looking at some reference material for your tool I would translate it this way though it does appears that there's even a dayOfMonth() function that might make this all even simpler.

SAP BusinessObjects SQL for multisource-enabled universes

substring(
    '0' || substring(<column>, pos('-', <column>) + 1, 3),
    pos('-',
        '0' || substring(<column>, pos('-', <column>) + 1, 3)
    ) - 2,
    2
)

https://help.sap.com/doc/4667b9486e041014910aba7db0e91070/4.2.4/en-US/sbo42sp4_info_design_tool_en.pdf

See it in action with PostgreSQL: http://sqlfiddle.com/#!17/9eecb/72962

Answer 2

SAP BusinessObjects SQL for multisource-enabled universes

substring(concat('0', toString(month(<date>))), length(concat('0', toString(month(<date>))))-1, 2)

I put a 0 in front of my month number converted to a string, and took the last two digits of the string.