Hunterpo's IT Vision

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[udfTaiwanDateFormat]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[udfTaiwanDateFormat]
GO

/****** Object:  UserDefinedFunction [dbo].[udfTaiwanDateFormat]    Script Date: 08/15/2010 15:41:53 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- ================================================
-- Author:		hunterpo
-- Blog:		http://www.dotblogs.com.tw/hunterpo
-- Create date: 2010-08-14
-- Description:	將日期轉中華民國曆格式
-- ================================================
CREATE FUNCTION [dbo].[udfTaiwanDateFormat] 
(
	@Date DATETIME,
	@Format VARCHAR(9) = NULL
)
RETURNS NVARCHAR(50)
AS
BEGIN
	DECLARE @Result NVARCHAR(9)
	
	IF (@Format IS NULL OR LEN(@Format) = 0)
		SELECT @Result = RIGHT('0' + CAST(CAST(CONVERT(CHAR(8), @Date, 112) AS INT) - 19110000 AS NVARCHAR(7)), 7)
	ELSE
	BEGIN
		DECLARE @YearDigits INT, @MonthDigits INT, @DayDigits INT, @Seperator CHAR(1)
		SELECT @YearDigits = 0, @MonthDigits = 0, @DayDigits = 0;

		-- 分隔符號
		SELECT @Seperator = 
		CASE
			WHEN (CHARINDEX('.', @Format) > 0) THEN '.'
			WHEN (CHARINDEX('/', @Format) > 0) THEN '/'
			WHEN (CHARINDEX('-', @Format) > 0) THEN '-'
			ELSE ''
		END;

		IF (LEN(@Seperator) > 0) SET @Format = REPLACE(@Format, @Seperator, '');

		-- 年
		WHILE (CHARINDEX('y', @Format) > 0)
		BEGIN
			SET @YearDigits = @YearDigits + 1;
			SET @Format = RIGHT(@Format, LEN(@Format) - CHARINDEX('y', @Format));
		END
		
		IF (@YearDigits > 3 OR @YearDigits < 2) RETURN N'年份格式錯誤，請指定二位數 (yy) 或三位數 (yyy)。';
		IF YEAR(@Date) > 2010 SET @YearDigits = 3;

		-- 月
		WHILE (CHARINDEX('m', @Format) > 0)
		BEGIN
			SET @MonthDigits = @MonthDigits + 1;
			SET @Format = RIGHT(@Format, LEN(@Format) - CHARINDEX('m', @Format));
		END
		
		IF (@MonthDigits > 2 OR @MonthDigits < 1) RETURN N'月份格式錯誤，請指定單位數 (m) 或雙位數 (mm)。';
		IF MONTH(@Date) > 9 SET @MonthDigits = 2;

		-- 日
		WHILE (CHARINDEX('d', @Format) > 0)
		BEGIN
			SET @DayDigits = @DayDigits + 1;
			SET @Format = RIGHT(@Format, LEN(@Format) - CHARINDEX('d', @Format));
		END
		
		IF (@DayDigits > 2 OR @DayDigits < 1) RETURN N'日期格式錯誤，請指定單位數 (d) 或雙位數 (dd)。';
		IF DAY(@Date) > 9 SET @DayDigits = 2;
		
		IF (LEN(@Seperator) > 0)
			SELECT @Result = 
				RIGHT('0' + CAST(YEAR(@Date) - 1911 AS VARCHAR(3)), @YearDigits) + 
				@Seperator + 
				RIGHT('0' + CAST(MONTH(@Date) AS VARCHAR(2)), @MonthDigits) + 
				@Seperator + 
				RIGHT('0' + CAST(DAY(@Date) AS VARCHAR(2)), @DayDigits);
		ELSE
			SELECT @Result = 
				RIGHT('0' + CAST(YEAR(@Date) - 1911 AS VARCHAR(3)), @YearDigits) + 
				RIGHT('0' + CAST(MONTH(@Date) AS VARCHAR(2)), @MonthDigits) + 
				RIGHT('0' + CAST(DAY(@Date) AS VARCHAR(2)), @DayDigits);
	END
	
	RETURN @Result
END
GO

SELECT @Date = GETDATE();
SELECT [dbo].[udfTaiwanDateFormat] (@Date, @Format) AS [Today_default];
-- 以下相同
--SELECT [dbo].[udfTaiwanDateFormat] (@Date, DEFAULT) AS [Today_default];
--SELECT [dbo].[udfTaiwanDateFormat] (@Date, '') AS [Today_default];
/*
Today_default
-------------
0990815
*/

SELECT @Date = GETDATE(), @Format = 'yyy/mm/dd';
SELECT [dbo].[udfTaiwanDateFormat] (@Date, @Format) AS [Today];
/*
Today
---------
099/08/15
*/

SELECT @Date = '20121222', @Format = 'yy/mm/dd';
SELECT [dbo].[udfTaiwanDateFormat] (@Date, @Format) AS [End of the world？];
/*
End of the world？
-----------------
101/12/22
*/

SELECT @Date = '19790323', @Format = 'yymmdd';
SELECT [dbo].[udfTaiwanDateFormat] (@Date, @Format) AS [My birthday];
/*
My birthday
680323
*/

SELECT @Date = '20070120', @Format = 'yy/m/d';
SELECT [dbo].[udfTaiwanDateFormat] (@Date, @Format) AS [Wedding Anniversary];
/*
Wedding Anniversary
-------------------
96/1/20
*/

SELECT @Date = '20100816', @Format = 'yyy.m.d';
SELECT [dbo].[udfTaiwanDateFormat] (@Date, @Format) AS [Chinese Valentine's]
/*
Chinese Valentine's
-------------------
099.8.16
*/