Galin Iliev's blog

Software Architecture & Development

T-SQL Number Generator

Here is some more T-SQL fun: number generator Smile I found this code in comments of this blog post and it is so cools so I decided to save it here for reference:

-- use number generation
WITH Numbers3( n ) AS ( SELECT 1 UNION SELECT 0 ),
     Numbers2( n ) AS ( SELECT 1 FROM Numbers3 n1 CROSS JOIN Numbers3 n2 ),
     Numbers1( n ) AS ( SELECT 1 FROM Numbers2 n1 CROSS JOIN Numbers2 n2 ),
     Numbers0( n ) AS ( SELECT 1 FROM Numbers1 n1 CROSS JOIN Numbers1 n2 ),
     NumbersBase ( n ) AS ( SELECT 1 FROM Numbers0 n1 CROSS JOIN Numbers0 n2 ),
     Numbers ( Number ) AS 
         SELECT n
         FROM NumbersBase ) D ( n )
         WHERE n <= 50

T-SQL: Get Date and Hour

I am back in T-SQL fun and had interesting problem to solve – group rows by date and hour but eliminate minutes, seconds and milliseconds. Thus here is a handy function to do that:

DECLARE @Date datetime= getdate();

SELECT DATEADD(ms, -1*DATEPART(ms, @date), -- remove milliseconds
    DATEADD(SECOND, -1*DATEPART(SECOND, @date), -- remove seconds
    DATEADD(MINUTE, -1*DATEPART(MINUTE, @date), -- remove minutes

I hope you know how to create function from this code Smile.

Have fun!