1

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?

Emma van de Vreugde
  • 103
  • 1
  • 10
  • Praytell, what database supports ANSI 92 syntax? You should tag with the database you are really using. – Gordon Linoff Apr 06 '21 at 13:46
  • It's not actually a database, but I am using Information Design Tool of SAP, and that uses this syntax unfortunately when using multiple databases. Therefore it's not really about a database, but only about the syntax. – Emma van de Vreugde Apr 06 '21 at 13:49
  • `substring('0' + substring( from position('-' in ) + 1 for 2) from 3 - position(substring('0' + from position('-' in ) + 1 for 2) for 2)` – shawnt00 Apr 06 '21 at 14:09
  • Honestly I don't even remember which is the proper concatenation operator. I think that gets you fairly close though. – shawnt00 Apr 06 '21 at 14:14
  • 1
    @shawnt00: that's `||` in standard SQL and pretty much every DBMS uses it (with one and a half notable exceptions of the "usual suspects") –  Apr 06 '21 at 14:18
  • @shawnt00: its concat(x,y). Thank you for your reply. Unfortuantely position() is not supported. – Emma van de Vreugde Apr 06 '21 at 14:20
  • Look for index or instr or find... Btw the above is horribly messed up but I'll leave it anyway. – shawnt00 Apr 06 '21 at 14:28

2 Answers2

2

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

shawnt00
  • 16,443
  • 3
  • 17
  • 22
  • Unfortunately the SAP solution is not working, but your suggestion and the documentation you sent helped me in getting inspired for a solution myself. Thanks a lot! – Emma van de Vreugde Apr 07 '21 at 07:59
  • Verified as valid SQL-2016 syntax, using https://developer.mimer.com/sql-2016-validator/. – jarlh Apr 23 '21 at 19:08
0

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.

Emma van de Vreugde
  • 103
  • 1
  • 10