none
Zeiten filtern in einer SQL-Datenbank RRS feed

  • Frage

  • Hallo zusammen,

    ich habe in einer SQL-Datenbank-Tabelle in einer Spalte 'Datum' vom Typ 'datetime' und in einer Spalte 'Uhrzeit' vom Typ 'nvarchar' Zeiten von Buchungen gespeichert.

    Wie kann ich nun am bequemsten Zeiträume herausfiltern in den Alternativen:

    - Uhrzeit von / Uhrzeit bis (Datum egal)

    - Datum, Uhrzeit von / Datum, Uhrzeit bis

    - Datum von / Datum bis (Uhrzeit egal)

    - bestimmte Wochentage

    Die gefundenen Datensätze sollen dann in eine Excel-Tabelle exportiert werden.

    Ich würde mich freuen, wenn mir da jemand behilflich sein kann.

    Gruß Jürgen

    Dienstag, 31. März 2015 08:57

Alle Antworten

  • Nachtrag: in der Spalte 'Datum' ist natürlich Datum und Uhrzeit gespeichert!

    Gruß Jürgen

    Dienstag, 31. März 2015 09:33
  • Hallo Jürgen,

    ab SQL Server 2008 sollte man die Date und Time Datentypen verwenden, das macht die Vergleiche einfacher und vermeidet (bei nvarchar) Konvertierungsprobleme.

    Das eigentliche Filtern ist nichts weiter als ein Verketten von Bedingungen:

    CREATE TABLE DatumOderZeit(
    	Datum date NULL,
    	Zeit time NULL);
    	
    INSERT INTO DatumOderZeit (Datum, Zeit) VALUES 
    	('2015-03-30', '15:00'),
    	('2015-03-15', '09:00'),
    	('2015-03-01', '12:00'),
    	('2015-03-20', null),
    	(null, '18:00');
    	
    DECLARE @filterBy int = null;
    DECLARE @datumVon date = '2015-03-10';
    DECLARE @datumBis date = '2015-03-25';
    DECLARE @zeitVon time = '06:00';
    DECLARE @zeitBis time = '15:00';
    DECLARE @day int = 7;
    
    SELECT @filterBy AS DatumFilter, *, DATEPART(WEEKDAY, d.Datum) AS dayofweek 
    FROM DatumOderZeit AS d
    	WHERE @filterBy IS NULL OR @filterBy = 0
    		OR (@filterBy = 1 
    				AND d.Datum >= @datumVon AND d.Datum <= @datumBis)
    		OR (@filterBy = 2 
    				AND d.Zeit >= @zeitVon AND d.Zeit <= @zeitBis)
    		OR (@filterBy = 3
    				AND d.Datum >= @datumVon AND d.Datum <= @datumBis 
    				AND d.Zeit >= @zeitVon AND d.Zeit <= @zeitBis)
    		OR (@filterBy = 4
    				AND DATEPART(WEEKDAY, d.Datum) = @day);
    				
    

    Ob Du daraus eine Tabellen Funktion / Prozedur machst hängt von der Wiederverwendbarkeit ab - für eine einmalige Sache sollte das nicht notwendig sein.

    Bei Datetime wirst Du ggf. den Zeitanteil entfernen müssen, wenn ungleich 0. Bei der nvarchar Zeit dem Format entsprechend via CONVERT eine "richtige" Zeit daraus machen.

    Gruß Elmar

    Dienstag, 31. März 2015 09:36
  • In der Datetime Spalte ist die Uhrzeit 00:00:00 und dafür hast Du die Uhrzeit separat abgelegt?
    Warum?

    Ich würde die Zeiten nach TIME konvertieren und mit BETWEEN arbeiten.

    With Testdaten
    as(
    Select cast(N'10:20:25' as time) as t1, cast(N'11:30:25' as time) as t2
    UNION ALL
    Select cast(N'11:20:25' as time) as t1, cast(N'12:30:25' as time) as t2
    )
    Select *
    from Testdaten
    where cast(N'11:34:25' as time) between t1 and t2;

    Bei den gemischten Angaben solltest Du die DATETIME-Felder nach nvarchar(8) konvertieren und dadurch die Zeit wegfallen lassen. (CONVERT mit Option 112). Dann hängst Du den nvarchar-String mit den Zeiten dran und konvertierst wieder nach DATETIME. Dann kannst Du wieder mit BETWEEN arbeiten.

    Select cast(CONVERT(nvarchar(8), getdate(), 112) + N' ' + N'12:30:15' as
    datetime);

    Wochentage ermittelst Du über DATEPART oder DATENAME.

    Select DATEPART(WEEKDAY, getdate()), DATENAME(WEEKDAY, getdate());

    Der Export nach Excel geht dann über SSIS, aber das ist glaube ich nicht die Frage, oder?
     Einen schönen Tag noch,
    Christoph
    --
    Microsoft SQL Server MVP - http://www.insidesql.org/blogs/cmu

    Dienstag, 31. März 2015 09:40
    Beantworter
  • Hallo Christoph,

    wenn TIME kann man auch DATE verwenden, CONVERT(112) wäre nur bei Pre SQL Server 2008 notwendig, dann wird es aber auch bei Zeiten scheußlicher.

    Gruß Elmar

    Dienstag, 31. März 2015 09:48
  • Stimmt!
    Da sollte man dann schon konsequent sein!

    Einen schönen Tag noch,
    Christoph
    --
    Microsoft SQL Server MVP - http://www.insidesql.org/blogs/cmu

    Dienstag, 31. März 2015 09:54
    Beantworter
  • Hallo Elmar,

    zunächst Danke für deine prompte Hilfestellung.

    Ich benutze SQL-Server2008. In der Datumsspalte steht beispielsweise '2015-03-01 19:15:18.000'.

    Ich habe deine Hilfe mal auf meine Tabelle umgestrickt.

    Leider liefert der mir alle Datensätze zurück.

    Gruß Jürgen

    Dienstag, 31. März 2015 10:05
  • Hallo Christoph,

    erstmal Danke für deinen Lösungsansatz.

    Dass noch eine separate Spalte 'Uhrzeit' in nvarchar existiert ist einer Kompatibilität zu Excelmappen, aus denen die Daten ursprünglich importiert wurden, geschuldet! Tja, hätte ich eigentlich auch weglassen können. Das werde ich wohl bei einer Migration rausschmeißen.

    So ganz kann ich deine Lösung nicht nachvollziehen. Daher hier mal eine Beispieltabelle:

    CREATE TABLE BDaten(
     Datum date NULL,
     Zeit time NULL,
     AName nvarchar(10));
     
    INSERT INTO BDaten (Datum, Zeit, AName) VALUES
     ('2015-03-13', '10:00','A'),
     ('2015-03-10', '22:05','B'),
     ('2015-03-11', '13:18','C'),
     ('2015-03-30', '16:45','D'),
     ('2015-03-20', null,'E'),
     (null, '18:00','F');

    Dienstag, 31. März 2015 10:23
  • Hallo Jürgen,

    wenn Du SQL Server 2008 hast und Datum und Zeit in einer Spalte zu finden sind, kann man das Ganze wie folgt lösen:

    CREATE TABLE DatumMitZeit(
    	DatumZeit datetime2 NULL);
    	
    INSERT INTO DatumMitZeit (DatumZeit) VALUES 
    	('2015-03-30 15:00'),
    	('2015-03-15 09:00'),
    	('2015-03-01 12:00'),
    	('2015-03-20'),
    	('18:00');
    	
    DECLARE @filterBy int;
    DECLARE @datumZeitVon datetime2 = '2015-03-10 06:00';
    DECLARE @datumZeitBis datetime2 = '2015-03-25 15:00';
    DECLARE @day int = 7;
    
    SET @filterBy = 0;
    WHILE @filterBy <= 4
    BEGIN
    	SELECT @filterBy AS DatumFilter, *, DATEPART(WEEKDAY, d.DatumZeit) AS dayofweek 
    	FROM DatumMitZeit AS d
    	WHERE @filterBy IS NULL OR @filterBy = 0
    		OR (@filterBy = 1 
    				AND CAST(d.DatumZeit AS DATE) >= CAST(@datumZeitVon AS DATE) AND CAST(d.DatumZeit AS DATE) <= CAST(@datumZeitBis AS DATE))
    		OR (@filterBy = 2 
    				AND CAST(d.DatumZeit AS TIME) >= CAST(@datumZeitVon AS TIME) AND CAST(d.DatumZeit AS TIME) <= CAST(@datumZeitBis AS TIME))
    		OR (@filterBy = 3
    				AND CAST(d.DatumZeit AS DATE) >= CAST(@datumZeitVon AS DATE) AND CAST(d.DatumZeit AS DATE) <= CAST(@datumZeitBis AS DATE)
    				AND CAST(d.DatumZeit AS TIME) >= CAST(@datumZeitVon AS TIME)AND CAST(d.DatumZeit AS TIME) <= CAST(@datumZeitBis AS TIME))
    		OR (@filterBy = 4
    				AND DATEPART(WEEKDAY, d.DatumZeit) = @day);
    				
    	SET @filterBy += 1;
    END;

    Ich habe ein DateTime2 verwendet - was besser konvertierbar und mehr Möglichkeiten bietet. Die Bedingungsspalten sind um zwei geschrumpft - man kann dabei auch nur mit Zeit arbeiten.

    Die zusätzliche Schleife zeigt alle Varianten der Filterung - und wäre im Ergebnis identisch zum ersten Beispiel mit getrennten Spalten.

    Gruß Elmar

    Dienstag, 31. März 2015 10:36
  • Hier sind ein paar Beispiele:

    Select *
    from #BDaten
    where Zeit between cast('11:34:25' as time)    and cast('15:34:25' as time);
    
    Select *
    from #BDaten
    where Datum between '20150310' and '20150315';
    
    Select *
    from #BDaten
    where Datum between '20150310' and '20150315'
    and Zeit between '11:34:25' and '15:34:25' ;
    
    Select *
    from #BDaten
    where DATEPART(WEEKDAY, Datum) = 3
    or DATENAME(WEEKDAY, Datum) = 'Tuesday';

    Einen schönen Tag noch,
    Christoph
    --
    Microsoft SQL Server MVP - http://www.insidesql.org/blogs/cmu

    Dienstag, 31. März 2015 11:48
    Beantworter
  • Der Vollständigkeit halber auch noch ein Beispiel mit korrekten Daten:

    Select *
    from #BDaten
    where Datum between '20150310 11:34:25' and '20150315 15:34:25'
    ;

    Einen schönen Tag noch,
    Christoph
    --
    Microsoft SQL Server MVP - http://www.insidesql.org/blogs/cmu

    Dienstag, 31. März 2015 12:42
    Beantworter