Database

case when bitfield = 1 then 'yes'<br />
else 'no end

<br />
SET QUOTED_IDENTIFIER ON <br />
GO<br />
SET ANSI_NULLS ON <br />
GO<br />
<br />
<br />
create  FUNCTION dbo.udf_FormatDate (@date datetime, @format varchar(50))  <br />
RETURNS VARCHAR(50) AS  <br />
BEGIN <br />
-------------------------------------------------------------------------------------<br />
--<br />
-- Name: FormatDate<br />
--<br />
-- Purpose: Mimics the VB Format routine for dates<br />
--<br />
-- Parameters: <br />
--		@date - Date, Date to be formatted<br />
--		@format - String, Template to format the date to<br />
--<br />
-- Returns: String, Date formatted to user requested template<br />
--<br />
-- Notes: <br />
--		1. Time information is not accounted for in this routine<br />
--<br />
--		2. @format accepts the following values for each section of the date.<br />
--			Day<br />
--				dddd 	- Full Day Name<br />
--				ddd 	- Abbreviated Day Name<br />
--				dd 		- Zero Padded Day Number<br />
--				d 		- Day Number<br />
--<br />
--			Month<br />
--				mmmm	- Full Month Name<br />
--				mmm	- Abbreviated Month Name<br />
--				mm		- Zero Padded Month Number<br />
--				m		- Month Number<br />
--<br />
--			Year<br />
--				yyyy	- Full 4 digit year<br />
--				yy		- 2 digit year<br />
--<br />
--		3. Any unexpected characters will be returned in the string<br />
--<br />
--		4. Assumes database was set up with case-insensitive collation<br />
--<br />
-- Example Usage:<br />
--			All examples use the following date 2003-07-13 00:00:00<br />
--<br />
--			'dddd, mmmm dd, yyyy' 	--> Sunday, July 13, 2003<br />
--			'mmddyyyy' 				--> 07132003<br />
--			'm-d-yy'					--> 7-13-03<br />
--			'mm/dd/yyyy'			--> 07/13/2003<br />
--<br />
-------------------------------------------------------------------------------------<br />
DECLARE @pos AS INTEGER<br />
DECLARE @char AS VARCHAR(1)<br />
--<br />
-- Replace Year<br />
--<br />
	SET @pos = CHARINDEX('yyyy', @format)<br />
	WHILE @pos > 0<br />
	BEGIN<br />
		SET @format = STUFF(@format, @pos, 4, DATENAME(yyyy, @date))<br />
		--PRINT @format<br />
		SET @pos = CHARINDEX('yyyy', @format)<br />
	END<br />
	<br />
	SET @pos = CHARINDEX('yy', @format)<br />
	WHILE @pos > 0<br />
	BEGIN<br />
		SET @format = STUFF(@format, @pos, 2, RIGHT(DATENAME(yyyy, @date) ,2))<br />
		--PRINT @format<br />
		SET @pos = CHARINDEX('yy', @format)<br />
	END<br />
--<br />
-- Replace Month<br />
--<br />
	SET @pos = CHARINDEX('mmmm', @format)<br />
	WHILE @pos > 0<br />
	BEGIN<br />
		SET @format = STUFF(@format, @pos, 4, DATENAME(month, @date))<br />
		--PRINT @format<br />
		SET @pos = CHARINDEX('mmmm', @format)<br />
	END<br />
	<br />
	SET @pos = CHARINDEX('mmm', @format)<br />
	WHILE @pos > 0<br />
	BEGIN<br />
		SET @format = STUFF(@format, @pos, 3, LEFT(DATENAME(month, @date), 3))<br />
		--PRINT @format<br />
		SET @pos = CHARINDEX('mmm', @format)<br />
	END<br />
	SET @pos = CHARINDEX('mm', @format)<br />
	WHILE @pos > 0<br />
	BEGIN<br />
		SET @format = STUFF(@format, @pos, 2, RIGHT(('0' + CAST(DATEPART(month, @date) AS VARCHAR(2))), 2))<br />
		--PRINT @format<br />
		SET @pos = CHARINDEX('mm', @format)<br />
	END<br />
	<br />
	SET @pos = CHARINDEX('m', @format)<br />
	WHILE @pos > 0<br />
	BEGIN<br />
		-- account for MArch and deceMBer<br />
		SET @char = SUBSTRING(@format, @pos + 1, 1)<br />
		IF (@char <> 'a') AND (@char <> 'b')<br />
			BEGIN<br />
				SET @format = STUFF(@format, @pos, 1, CAST(DATEPART(month, @date) AS VARCHAR(2)))<br />
				--PRINT @format<br />
				SET @pos = CHARINDEX('m', @format)<br />
			END<br />
		ELSE<br />
			BEGIN<br />
				SET @pos = CHARINDEX('m', @format, @pos + 1)<br />
			END<br />
	END<br />
--<br />
-- Replace Day<br />
--<br />
	SET @pos = CHARINDEX('dddd', @format)<br />
	WHILE @pos > 0<br />
	BEGIN<br />
		SET @format = STUFF(@format, @pos, 4, DATENAME(weekday, @date))<br />
		--PRINT @format<br />
		SET @pos = CHARINDEX('dddd', @format)<br />
	END<br />
	<br />
	SET @pos = CHARINDEX('ddd', @format)<br />
	WHILE @pos > 0<br />
	BEGIN<br />
		SET @format = STUFF(@format, @pos, 3, LEFT(DATENAME(weekday, @date), 3))<br />
		--PRINT @format<br />
		SET @pos = CHARINDEX('ddd', @format)<br />
	END<br />
	SET @pos = CHARINDEX('dd', @format)<br />
	WHILE @pos > 0<br />
	BEGIN<br />
		SET @format = STUFF(@format, @pos, 2, RIGHT(('0' + DATENAME(day, @date)), 2))<br />
		--PRINT @format<br />
		SET @pos = CHARINDEX('dd', @format)<br />
	END<br />
	SET @pos = CHARINDEX('d', @format)<br />
	WHILE @pos > 0<br />
	BEGIN<br />
		-- account for DEcember, sunDAy --> saturDAy, weDNesday<br />
		SET @char = SUBSTRING(@format, @pos + 1, 1)<br />
		IF (@char <> 'e') AND (@char <> 'a') AND (@char <> 'n')<br />
			BEGIN<br />
				SET @format = STUFF(@format, @pos, 1, CAST(DATEPART(day, @date) AS VARCHAR(2)))<br />
				--PRINT @format<br />
<br />
<br />
				SET @pos = CHARINDEX('d', @format)<br />
			END<br />
		ELSE<br />
			BEGIN<br />
				SET @pos = CHARINDEX('d', @format, @pos + 1)<br />
			END<br />
	END<br />
IF @format = '//' BEGIN<br />
	SET @format = ''<br />
END<br />
RETURN @format<br />
END<br />
<br />
<br />
<br />
<br />
<br />
<br />
GO<br />
SET QUOTED_IDENTIFIER OFF <br />
GO<br />
SET ANSI_NULLS ON <br />
GO<br />
<br />

My: Blog | Photos

"Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucious