Showing posts from February, 2010

SQL random number column

Here's a quickie. If you need to have a new random number on a column, this works really well.rand(cast(cast(NEWID() as varbinary) as int)) number Of course NEWID generates a random guid. Casting it as a varbinary first, then as an int gives you a random number which can then be used as a seed for the random number generator.

Dynamically Generated List of Dates Using Common Table Expressions (CTE)

Here is a nice use for Common Table Expressions (CTEs) to generate a dates table or other list of stuff. I won't take credit for this technique since I saw it someplace else (but can't remember where). Of course my blog is for stuff I want to remember, so thanks to whoever you are...declare @loops int = 1000 declare @StartDate date = '1/1/2000'; WITH CountTable( RowNumber, [Month], [Year], FirstDay ) AS ( SELECT 1 RowNumber, MONTH(@StartDate) [Month], YEAR(@StartDate) [Year], -- compute the first day of the month from whatever day was provided DATEADD(dd,-(DAY(@StartDate)-1),@StartDate) FirstDay UNION ALL SELECT RowNumber + 1 RowNumber, MONTH(DATEADD(MONTH, 1, FirstDay)), YEAR(DATEADD(MONTH, 1, FirstDay)), DATEADD(MONTH, 1, FirstDay) FirstDay FROM CountTable t WHERE RowNumber < @loops ) SELECT * FROM CountTable OPTION (maxrecursion 32767) The results look like this: 1 1 2000 2000-01-01 2 2 2000 2000-02-01 3 3 2000 2000-03-01 4…

Recursively change unix permissions on a directory

On my freenas server I sometimes need to change the permissions of files that were uploaded via FTP. Here is the command that can be issued to make those changes:

chmod -R 0777 "/mnt/Seagate650/FTP Root"

The quotes are important because my ftp root directory contains a space.