Frank Kalis

SET NOCOUNT ON
CREATE TABLE #temp_table 
( 
     hm VARCHAR(5)
)
INSERT #temp_table VALUES('8:30')
INSERT #temp_table VALUES('5:01')
INSERT #temp_table VALUES('12:59')
INSERT #temp_table VALUES('7:20')
SET NOCOUNT OFF
SELECT
     CONVERT(VARCHAR, SUM(DATEDIFF(ss, '1900-01-01 00:00', CONVERT(DATETIME, hm)))/3600) +
     ':' +
     CONVERT(VARCHAR, (SUM(DATEDIFF(ss, '1900-01-01 00:00', CONVERT(DATETIME, hm)))%3600)/60)
     
FROM
     #temp_table
DROP TABLE #temp_table

                                                              
------------------------------------------------------------- 
33:50

(1 row(s) affected)

SET NOCOUNT ON
CREATE TABLE strip_t 
(
col VARCHAR(30) 
)
INSERT INTO strip_t VALUES ('Frank')
INSERT INTO strip_t VALUES ('Frank!')
INSERT INTO strip_t VALUES ('Fr^ank')
INSERT INTO strip_t VALUES ('Fran&k')
INSERT INTO strip_t VALUES ('Fran$k')
SET NOCOUNT OFF
SELECT 
     * 
FROM
     strip_t 
WHERE 
     col 
LIKE '%[^A-Za-z0-9]%'

col                            
------------------------------ 
Frank!
Fr^ank
Fran&k
Fran$k

(4 row(s) affected)

Als Gegenprobe:

SELECT 
     * 
FROM
     strip_t 
WHERE 
     col 
NOT LIKE '%[^A-Za-z0-9]%'

GO
DROP TABLE strip_t

col                            
------------------------------ 
Frank

(1 row(s) affected)

Einen Nachteil gibt es doch: Dies funktioniert nicht mit allen Collations. Also, unbedingt gründlich vorher testen!!!

DECLARE @time INT
SET @time = 7382
SELECT 
      @time / 3600 AS Stunden
     , (@time / 60)% 60 AS Minuten
     , @time % 60 AS Sekunden

Stunden     Minuten     Sekunden    
----------- ----------- ----------- 
2           3           2

(1 row(s) affected)

SELECT RIGHT(STUFF(CONVERT(CHAR(22),GETDATE(),13),21,4,' '),9)
          
--------- 
10:14:40 

(1 row(s) affected)

--Oder

SELECT CONVERT(CHAR(8), GETDATE(), 8)
         
-------- 
10:14:40

(1 row(s) affected)

--Oder

SELECT CONVERT(CHAR(8), GETDATE(), 108)
         
-------- 
10:14:40

(1 row(s) affected)

Falls man auch noch die Millisekunden braucht, kann man:

SELECT CONVERT(CHAR(12),GETDATE(),114)
             
------------ 
10:20:16:033

(1 row(s) affected)

ausführen.

USE Northwind
GO
SELECT 
 n.dow
 , ISNULL(SUM(o.Freight),0)
  FROM Orders o 
 RIGHT JOIN 
     (SELECT 1 
          dow 
     UNION ALL SELECT 2 
     UNION ALL SELECT 3 
     UNION ALL SELECT 4 
     UNION ALL SELECT 5 
     UNION ALL SELECT 6 
     UNION ALL SELECT 7) n
 ON DATEPART(w,Orderdate) = n.dow
GROUP BY n.dow

dow                               
----------- --------------------- 
1           13772.8400
2           12858.7800
3           11069.5500
4           12114.7200
5           15126.8000
6           .0000
7           .0000

(7 row(s) affected)

Warnung: NULL-Wert wird durch eine Aggregat- oder eine andere SET-Operation gelöscht.

Danke an Jonathan Van Houtte.

DECLARE @yr INT
DECLARE @jd INT
SELECT @yr = 2002, @jd = 245
SELECT DATEADD(yyyy, @yr - 1900, -1) + @jd AS Julian_Date

Julian_Date                                            
------------------------------------------------------ 
2002-09-02 00:00:00.000

(1 row(s) affected)

Danke an Jonathan Van Houtte.

DECLARE @sekunden INT
SET @sekunden = 61
SELECT
	CONVERT(VARCHAR(2), FLOOR(@sekunden/60)) + 
	':' + 
	RIGHT('0' + CONVERT(VARCHAR(2), (@sekunden%60)), 2)

      
----- 
1:01

(1 row(s) affected)

IF OBJECT_ID('lfdsum_t') IS NOT NULL
     DROP TABLE lfdsum_t
GO
CREATE TABLE lfdsum_t (col1 int)
INSERT INTO lfdsum_t values (1);
INSERT INTO lfdsum_t values (2);
INSERT INTO lfdsum_t values (3);
INSERT INTO lfdsum_t values (4);
INSERT INTO lfdsum_t values (5);

SELECT 
     a.COL1
     ,(SELECT 
          Sum(b.col1)
     FROM
          lfdsum_t b
WHERE 
          b.col1 <= a.col1) lfd_Sum
FROM 
     lfdsum_t a
DROP TABLE lfdsum_t

COL1        lfd_Sum     
----------- ----------- 
1           1
2           3
3           6
4           10
5           15

(5 row(s) affected)

Zugegebenermassen bin ich mir nicht sicher, ob der technische Begriff dafür 'laufende Summe' ist. Für Anregungen zu einem treffenderen Begriff bin ich dankbar.