這幾個小函數很早就寫了，項目做完後沒想到有一天有公司的兄弟說要用，於是就想著放在這上面來。

功能1
???現在公司接的活是臺灣的，數據庫中免不了要保存民國日期(公元年份 - 1911即為民國日期)，而運算時又要轉成西元日期，因此在運用時會要求進行轉換，通常用兩類。
???1. 在程序中轉換，通常用類型轉換函數將日期轉成長整型再減去19110000。
???2. 在抓資料時就完成轉換，為了方便我做了兩個小函數來完成。
???適用於MS - SQL

民國日期列轉換為西元列
' '****************************************************************************************************************
' *程式功能??:???????將一個民國日期列轉換為西元列
' *開發人員??:　?????ddm?2007/1/13
' *開發說明??:　?????例如?AAABREP.ABACDT列?,使用?"Select?"?&?MG2XYCol("AAABREP.ABACDT")?&?"?As?ABACDT?From?AAABREP"?轉換
' *傳入值????:???????strColName??????--需要轉換的列名
' *傳入值????:???????strDefaultValue?--如果不是一個符合的日期時的值
' *回傳值????:???????String??????????--轉換後的SQL語句
' '****************************************************************************************************************
Public ? Function ?MG2XYCol(strColName? As ? String ,? Optional ?strDefaultValue? As ? String ? = ? " '' " )? As ? String
???? Dim ?strSql? As ? String
????strSql? = ? " ?CASE? "
????strSql? = ?strSql? & ? " ?WHEN?LEN(LTRIM(RTRIM(ISNULL(@ColName,''))))>=6?THEN "
????strSql? = ?strSql? & ? " ?CAST((CAST(LEFT(LTRIM(@ColName),?LEN(LTRIM(RTRIM(@ColName)))-4)?As?int)?+?1911)?As?varchar(4)) "
????strSql? = ?strSql? & ? " +LEFT(RIGHT(LTRIM(RTRIM(@ColName)),4),2) "
????strSql? = ?strSql? & ? " +RIGHT(LTRIM(RTRIM(@ColName)),2) "
????strSql? = ?strSql? & ? " ?ELSE "
????strSql? = ?strSql? & ? " ?@DefaultValue "
????strSql? = ?strSql? & ? " ?END? "
????strSql? = ? Replace (strSql,? " @ColName " ,?strColName)
????MG2XYCol? = ? Replace (strSql,? " @DefaultValue " ,?strDefaultValue)
End?Function

?

西元列轉換為民國日期列
' '****************************************************************************************************************
' *程式功能??:???????將一個西元列轉換為民國日期列
' *開發人員??:　?????ddm?2007/1/13
' *開發說明??:　?????例如?AAABREP.ABACDT列?,使用?"Select?"?&?XY2MGCol("CStartDate")?&?"?As?CStartDate?From?tbContractRent"?轉換
' *傳入值????:???????strColName??????--需要轉換的列名
' *傳入值????:???????strDefaultValue?--如果不是一個符合的日期時的值
' *回傳值????:???????String??????????--轉換後的SQL語句
' '****************************************************************************************************************
Public ? Function ?XY2MGCol(strColName? As ? String ,? Optional ?strDefaultValue? As ? String ? = ? " '' " )? As ? String
???? Dim ?strSql? As ? String
????strSql? = ? " ?CASE? "
????strSql? = ?strSql? & ? " ?WHEN?LEN(LTRIM(RTRIM(ISNULL(@ColName,''))))=8?THEN "
????strSql? = ?strSql? & ? " ?CAST((CAST(LEFT(LTRIM(@ColName),4)?As?int)?-?1911)?As?varchar(4)) "
????strSql? = ?strSql? & ? " +LEFT(RIGHT(LTRIM(RTRIM(@ColName)),4),2) "
????strSql? = ?strSql? & ? " +RIGHT(LTRIM(RTRIM(@ColName)),2) "
????strSql? = ?strSql? & ? " ELSE "
????strSql? = ?strSql? & ? " ?@DefaultValue "
????strSql? = ?strSql? & ? " ?END? "
????strSql? = ? Replace (strSql,? " @ColName " ,?strColName)
????XY2MGCol? = ? Replace (strSql,? " @DefaultValue " ,?strDefaultValue)
End?Function

功能2
???在用DB2時數據庫中以數值類型來存放民國日期，要做日期類型運算就要先轉換成日期類型

數值型民國日期列轉換為日期列
' '****************************************************************************************************************
' *程式功能??:???????將一個數值型民國日期列轉換為日期列
' *開發人員??:　?????ddm?2007/8
' *開發說明??:　?????例如?parseDate("Select?*?From?tb?Where?@Date(d1)?+?20?Days?<?@Date(d2)")
' *傳入值????:???????strSQL????????????--需要轉換的SQL語句
' *回傳值????:???????String??????????--轉換後的SQL語句
' '****************************************************************************************************************
Public ? Function ?parseDate(strSQL)
???? Dim ?s? As ? String ,?fieldName? As ? String ,?i? As ? Long ,?j? As ? Long ,?result? As ? String
????result? = ?strSQL
????s? = ? ""
????i? = ? InStr ( 1 ,?result,? " @Date( " )
???? Do ? While ?i? > ? 0
????????j? = ? InStr (i,?result,? " ) " )
???????? If ?i? > ? 0 ? And ?j? > ?i? Then
????????????fieldName? = ? Mid (result,?i? + ? 6 ,?j? - ?i? - ? 6 )
????????????fieldName? = ? " Varchar( " ? & ?fieldName? & ? " +19110000) "
????????????s? = ? " Date(Substring( " ? & ?fieldName? & ? " ,1,4) "
????????????s? = ?s? & ? " ?||?'-'?||?Substring( " ? & ?fieldName? & ? " ,5,2) "
????????????s? = ?s? & ? " ?||?'-'?||?Substring( " ? & ?fieldName? & ? " ,7,2)) "
???????? End ? If
????????fieldName? = ? Mid (result,?i,?j? - ?i? + ? 1 )
????????result? = ? Replace (result,?fieldName,?s)
????????i? = ? InStr ( 1 ,?result,? " @Date( " )
???? Loop
????parseDate? = ?result
End?Function

?