tsql

Birthday Query in MS SQL Server

Submitted by Sergey on March 22, 2009 - 11:10

It's a common request to get a list of people whose birthday (or an anniversary date) falls in the specified date range w/o regard to the year.

AT and RAT UDFs - Search a character expression for the occurrence of another character expression

Submitted by Sergey on January 14, 2009 - 14:48

T-SQL has CHARINDEX and PATINDEX functions that return the position of one character expression in another. The ufn_AT and ufn_RAT functions add the ability to search for specified occurrences and to search from right to left.

Retrieving SQL Server Edition, Version and Service Pack Info

Submitted by Sergey on December 25, 2007 - 01:00

Week number in a Month

Submitted by Sergey on December 25, 2007 - 01:00

This is sample code. Add error handling and adjust to your requirements as necessary.

-- There's simpler and shorter version in the comments below
 
DECLARE @dt DATETIME, @WeekOfMonth TINYINT
SET @dt = '2007-07-08'
 
SET @WeekOfMonth = (DAY(@dt) + 
				(DATEPART(dw, DATEADD (MONTH, DATEDIFF (MONTH, 0, @dt), 0)) 
				  --^-- The day of the week for the first day of month
			-1) -- # of days to add to make the first week full 7 days
		-1)/7 + 1 
PRINT @WeekOfMonth

Mline UDF - Return specific line

Submitted by Sergey on November 23, 2007 - 01:00

The ufn_Mlines() UDF for SQL Server is similar to the VFP Mline() function but w/o third parameter.
It's more an example than finished UDF. I didn't do much testing on it.

IsNumericEx UDF - Data type aware

Submitted by Sergey on November 22, 2007 - 01:00

The built-in SQL Server ISNUMERIC() function determines if character expression can be converted to one of the numeric types. The ufn_IsNumericEx() function accepts the second parameter - the data type to convert to and checks also for characters illegal for that data type. It doesn't check if the numeric value is in the range for specified data type though.

Proper UDF - Capitalize String as Proper Names

Submitted by Sergey on April 5, 2007 - 00:00

The ufn_Proper() UDF for SQL Server is similar to the VFP Proper() function. It additionally allows to specify a set of delimiters.

Strextract UDF - Retrieves String Between Two Delimiters

Submitted by Sergey on April 5, 2007 - 00:00

The ufn_StrExtract() UDF for SQL Server is similar to the VFP StrExtract() function.

Alines UDF - String Split

Submitted by Sergey on March 5, 2007 - 01:00

The ufn_Alines() UDF for SQL Server is similar to the VFP Alines() function but w/o additional parameters.

Syndicate content