[MySQL] Datum omzetten naar juiste format en filteren

[DeepFreeze.NL]

Ik heb een MySQL tabel met daarin het veld datum. Bij dit veld wordt er geen gebruik gemaakt van het datatype DATETIME, maar van VARCHAR. Het ingevulde formaat is gewoon de Nederlandse notatie dd-mm-jjjj. Nu moet ik de data gaan filteren op jaar en maand.

Ik heb middels het gebruik van de MySQL handleiding (http://dev.mysql.com/doc/...html#function_date-format) de volgende query gemaakt.

MySQL:

SELECT datum FROM tabel WHERE YEAR(DATE_FORMAT(datum, '%Y-%m-%d')) = '2010'

Dit werkt niet, behalve als ik in de tabel de datum wijzig in de Amerikaanse notatie (yyyy-mm-dd).
Hoe kan ik dit werkende krijgen zonder dat ik het datatype moet aanpassen? Dat laatste wil ik in principe voorkomen doordat ik anders overal in de programmatuur het een en ander moet aanpassen.

Alvast bedankt.

[RobIII]

DeepFreeze.NL schreef op donderdag 09 september 2010 @ 15:16:
Dat laatste wil ik in principe voorkomen doordat ik anders overal in de programmatuur het een en ander moet aanpassen.

Om maar even een open deur in te trappen en te roepen wat je niet wil horen: datatype aanpassen. Dat veld had al een datetime in the first place moeten zijn. Fix je het nu (met een ranzige query of workaround) nu dan kom je het volgende week weer tegen en de week erop weer en...

[DeepFreeze.NL]

RobIII schreef op donderdag 09 september 2010 @ 15:18:
[...]

Om maar even een open deur in te trappen en te roepen wat je niet wil horen: datatype aanpassen. Dat veld had al een datetime in the first place moeten zijn. Fix je het nu (met een ranzige query of workaround) nu dan kom je het volgende week weer tegen en de week erop weer en...

Daar was ik al bang voor haha.

[Wolfboy]

Als je de tabel niet wil wijzigen en het niet snel hoeft te zijn (of er heel weinig data is) dan kan je het doen. Maar het is zeker niet aan te raden nee.

Enne.. nogal logisch dat het niet werkt, je zegt dat het datum formaat 'dd-mm-jjjj' is en je format naar 'jjjj-mm-dd'

Als je nou eventjes "DATE_FORMAT(datum, '%d-%m-%Y')" doet kan het best werken. Al is een substring pakken waarschijnlijk sneller als je toch alleen het jaartal nodig hebt.

[cariolive23]

Gebruik dan STR_TO_DATE(), die accepteert een string (bv. een VARCHAR) als input en genereert een DATE. Met die DATE kun je vervolgens verder gaan werken. Deze functie is ook ideaal voor de conversie van jouw huidige foute data naar bruikbare data, je kunt dit ook gewoon in een UPDATE gebruiken.

De functie DATE_FORMAT() heeft een DATE nodig als input en gaat deze omzetten naar een string, wat je hier nu net niet wilt. Met substring werken lijkt me ook wat overbodig.

[DeepFreeze.NL]

cariolive23 schreef op donderdag 09 september 2010 @ 15:39:
Gebruik dan STR_TO_DATE(), die accepteert een string (bv. een VARCHAR) als input en genereert een DATE. Met die DATE kun je vervolgens verder gaan werken. Deze functie is ook ideaal voor de conversie van jouw huidige foute data naar bruikbare data, je kunt dit ook gewoon in een UPDATE gebruiken.

De functie DATE_FORMAT() heeft een DATE nodig als input en gaat deze omzetten naar een string, wat je hier nu net niet wilt. Met substring werken lijkt me ook wat overbodig.

STR_TO_DATE werkt perfect, thanks! Ik ga toch nog maar even kijken of het omzetten van de datatype naar DATE geen betere oplossing is.

[P_de_B]

DeepFreeze.NL schreef op donderdag 09 september 2010 @ 15:56:
[...]

Ik ga toch nog maar even kijken of het omzetten van de datatype naar DATE geen betere oplossing is.

Daar hoef je niet naar te kijken, dat is zo. Wat als jij nu volgende week een update op een datum moet doen, of een query moet schrijven die records tussen een bepaalde ophaalt, of alle records van vorige week? Dan ga je de mist in.

Je moet het echt nu doen, of beloven dat je binnenkort hier post dat we toch gelijk hadden als je daar achter bent.

[DeepFreeze.NL]

P_de_B schreef op donderdag 09 september 2010 @ 15:58:
[...]

Daar hoef je niet naar te kijken, dat is zo. Wat als jij nu volgende week een update op een datum moet doen, of een query moet schrijven die records tussen een bepaalde ophaalt, of alle records van vorige week? Dan ga je de mist in.

Je moet het echt nu doen, of beloven dat je binnenkort hier post dat we toch gelijk hadden als je daar achter bent.

Ok ok, ik ben overtuigd . Ik ga het aanpassen..

[P_de_B]

[GlowMouse]

Je zoekt DATE ipv DATETIME. En je kunt je query beter schrijven als WHERE datum BETWEEN 2010-01-01 AND 2010-12-31, dan kan er een index gebruikt worden.

[cariolive23]

En vergeet de quotes niet, anders wordt het een rekensom... 2010 minus 01 minus 01. De uitkomst mag je zelf berekenen.

Wel of geen gebruik van een index, dat kun je controleren met EXPLAIN. Zorg voor MySQL wel uitstekende queries, MySQL is beperkt in de mogelijkheden. Jij mag dus alles gaan uitdenken en voorbewerken om het eenvoudiger te maken voor de database.