[SQL] lastig datumbereik omzetten naar 2 datums

[Bertil126]

Ik heb een kolom leverdata met daarin 2 data op een nogal bijzonder manier weergegeven, dit is bedoeld als een bereik met een verwachte leverdatum.
Bijvoorbeeld:
18-2-24-28-2-24
7-2-24-17-2-24
29-2-24-10-3-24
28-12-23-7-1-24

Ik wil hier graag 2 valide datums van maken, bijvoorblled minLeverdatum en maxLeverdatum
...

Ik gebruik een Azure database en kan daardoor niet de functie STRING_SPLIT gebruiken.
...
Ik dacht eerst met SUBSTRING en CHARINDEX stukje voor stukje de string op te knippen naar 6 verschillende kolommen zodat ik die later weer kan samenvoegen naar datums.
Het lukt mij om het eerste getal te isoleren en het startpunt van het 2e getal te vinden. Bij de lengte van het 2e getal loopt ik vast

ChatGPT kon het ook niet voor mij oplossen

Ik kan mij haast niet voorstellen dat ik de eerste ben die hier tegenaan loopt, wie kan me helpen?

De beste poging die ik heb is deze:

code:

SELECT 
  ,CASE WHEN CHARINDEX('-', verwachteLeverdatum) > 0 -- lege cellen overslaan
        THEN
            SUBSTRING(verwachteLeverdatum, 1, CHARINDEX('-', verwachteLeverdatum) - 1)
     END AS verwachteLeverdatumDag1
    ,CASE WHEN CHARINDEX('-', verwachteLeverdatum) > 0 -- lege cellen overslaan
        THEN
            SUBSTRING(HverwachteLeverdatum,CHARINDEX('-', verwachteLeverdatum+1,CHARINDEX('-', verwachteLeverdatum)+ CHARINDEX('-', verwachteLeverdatum)-CHARINDEX('-', verwachteLeverdatum)-1)
     END AS verwachteLeverdatumMaand1
FROM Bestellingen

...

[ Voor 0% gewijzigd door Bertil126 op 22-09-2023 12:49 . Reden: ook voorbeeld met 2 cijferige maand toegevoegd. ]

[Wallie]

Zet je compatibility level maar even omhoog van Azure DB:

https://learn.microsoft.c...sql?view=sql-server-ver16

Compatibility level 130

STRING_SPLIT requires the compatibility level to be at least 130. When the level is less than 130, SQL Server is unable to find the STRING_SPLIT function.

[Luit]

CHARINDEX('-', [VELD] , (CHARINDEX('-', [VELD], 1))+3)

Dit geeft de positie van de derde dash terug.

[Wallie]

Of vanaf positie 7 gaat zoeken naar de eerste '-', klopt altijd in deze gevallen.

SQL:

select *
     , charindex('-', Datum, 7)
     , substring(Datum, 1, charindex('-', Datum, 7) - 1)
     , substring(Datum, charindex('-', Datum, 7) + 1, 100)
from (
        select '18-2-24-28-2-24' As Datum
        union 
        select '7-2-24-17-2-24' As Datum
        union 
        select '29-2-24-10-3-24' As Datum
        union 
        select '28-12-23-7-1-24' As Datum
    ) A

[ Voor 14% gewijzigd door Wallie op 22-09-2023 13:02 ]

[DataGhost]

Ehhh, het datamodel omzetten naar iets wat niet achterlijk is (zegmaar twee datum-kolommen) en de vertaling in je favoriete programmeertaal doen is geen optie?

[KabouterSuper]

Heb je de mogelijkheid om functies aan te maken? Een ranzig datamodel vraagt om een hypertransparante oplossing. Maak bijvoorbeeld de functies parsestartdatum en parseeinddatum aan die je aanroept met de datumbereik-string. Is ook fijn als je de logica op meerdere plaatsen nodig hebt.

[Bertil126]

Wallie schreef op vrijdag 22 september 2023 @ 12:58:
Of vanaf positie 7 gaat zoeken naar de eerste '-', klopt altijd in deze gevallen.

SQL:

select *
     , charindex('-', Datum, 7)
     , substring(Datum, 1, charindex('-', Datum, 7) - 1)
     , substring(Datum, charindex('-', Datum, 7) + 1, 100)
from (
        select '18-2-24-28-2-24' As Datum
        union 
        select '7-2-24-17-2-24' As Datum
        union 
        select '29-2-24-10-3-24' As Datum
        union 
        select '28-12-23-7-1-24' As Datum
    ) A

Dat ik deze optie, niet zelf bedacht had , deze maakt er inderdaad 2 datums van.
Met TRY_CAST krijg ik ze nog lang niet allemaal naar een DATE, daar ga ik nog even verder op puzzelen.

[MueR]

Wallie schreef op vrijdag 22 september 2023 @ 12:51:
Zet je compatibility level maar even omhoog van Azure DB:

https://learn.microsoft.c...sql?view=sql-server-ver16

Compatibility level 130

STRING_SPLIT requires the compatibility level to be at least 130. When the level is less than 130, SQL Server is unable to find the STRING_SPLIT function.

Dit is gewoon het beste antwoord.

@Bertil126 Ik neem wel aan dat je dit als eenmalige actie wil doen, en dit niet constant live wil doen?

[Wallie]

Bertil126 schreef op vrijdag 22 september 2023 @ 13:13:
[...]


Dat ik deze optie, niet zelf bedacht had , deze maakt er inderdaad 2 datums van.
Met TRY_CAST krijg ik ze nog lang niet allemaal naar een DATE, daar ga ik nog even verder op puzzelen.

Convert gebruiken, die kan als 3de parameter een datumformaat meekrijgen:

SQL:

select *
     , charindex('-', Datum, 7)
     , convert(date, substring(Datum, 1, charindex('-', Datum, 7) - 1), 5)
     , convert(date, substring(Datum, charindex('-', Datum, 7) + 1, 100), 5)

from (
        select '18-2-24-28-2-24' As Datum
        union 
        select '7-2-24-17-2-24' As Datum
        union 
        select '29-2-24-10-3-24' As Datum
        union 
        select '28-12-23-7-1-24' As Datum
    ) A

Zie ook: https://www.mssqltips.com...ersions-using-sql-server/

[Bertil126]

Bedankt.

Ik heb nu het volgende resultaat:

code:

SELECT
   ,CASE WHEN CHARINDEX('-', verwachteLeverdatum) > 0 
        THEN
            CONVERT(date,SUBSTRING(verwachteLeverdatum, 1, CHARINDEX('-', verwachteLeverdatum,7)-1), 5)
     END AS MinverwachteLeverdatum
     ,CASE WHEN CHARINDEX('-', verwachteLeverdatum) > 0 
        THEN
          CONVERT(date,SUBSTRING(verwachteLeverdatum, CHARINDEX('-', verwachteLeverdatum,7)+1,10), 5)
     END AS MaxverwachteLeverdatum

FROM Bestellingen

[RobIII]

Even een stapje terug: Kun je die database aanpassen (m.a.w. is 't jouw / jullie software of is 't software van derden)? Want doe jezelf een lol en doe een eenmalige conversie naar 2 nieuwe date(time(2)) velden en drop deze ellende.

[Bertil126]

RobIII schreef op vrijdag 22 september 2023 @ 15:25:
Even een stapje terug: Kun je die database aanpassen (m.a.w. is 't jouw / jullie software of is 't software van derden)? Want doe jezelf een lol en doe een eenmalige conversie naar 2 nieuwe date(time(2)) velden en drop deze ellende.

Het betreft een tabel die dagelijks opgehaald wordt.
Deze omzetting bij die import toepassen is voor mij de best mogelijke oplossing die haalbaar is.

[DataGhost]

Zolang je het zelf op een normale manier opslaat, sure. Maar nogmaals een friendly reminder dat je dit hoogstwaarschijnlijk niet per se alleen maar in SQL hoeft te doen, in je import-job kan je doorgaans prima de boel as-is ophalen en verwerken in de taal waarin je je import-job schrijft, alvorens de gewijzigde rijen in je eigen datastructuur te zetten. Dat is vaak veel leesbaarder en onderhoudbaarder dan deze vieze rommel-query. In de meeste talen kan je dat hele veld gewoon in zes variabelen splitten die je direct de juiste namen geeft. En op die manier hoef je bij een volgend soortgelijk veld niet weer uren het internet op om uit te vogelen hoe je ze op de omslachtige manier in het juiste formaat krijgt.

[ Voor 13% gewijzigd door DataGhost op 22-09-2023 16:22 ]