|
|
Posted on 2007-09-03 19:56 大大毛 閱讀(2422) 評論(0) 編輯 收藏 所屬分類: SQL
這幾個小函數很早就寫了，項目做完後沒想到有一天有公司的兄弟說要用，於是就想著放在這上面來。
功能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
?
|