|
|
Posted on 2007-09-03 19:56 大大毛 閱讀(2422) 評(píng)論(0) 編輯 收藏 所屬分類(lèi): SQL
這幾個(gè)小函數(shù)很早就寫(xiě)了，項(xiàng)目做完後沒(méi)想到有一天有公司的兄弟說(shuō)要用，於是就想著放在這上面來(lái)。
功能1
???現(xiàn)在公司接的活是臺(tái)灣的，數(shù)據(jù)庫(kù)中免不了要保存民國(guó)日期(公元年份 - 1911即為民國(guó)日期)，而運(yùn)算時(shí)又要轉(zhuǎn)成西元日期，因此在運(yùn)用時(shí)會(huì)要求進(jìn)行轉(zhuǎn)換，通常用兩類(lèi)。
???1. 在程序中轉(zhuǎn)換，通常用類(lèi)型轉(zhuǎn)換函數(shù)將日期轉(zhuǎn)成長(zhǎng)整型再減去19110000。
???2. 在抓資料時(shí)就完成轉(zhuǎn)換，為了方便我做了兩個(gè)小函數(shù)來(lái)完成。
???適用於MS - SQL
民國(guó)日期列轉(zhuǎn)換為西元列
'
'****************************************************************************************************************
'
*程式功能??:???????將一個(gè)民國(guó)日期列轉(zhuǎn)換為西元列
'
*開(kāi)發(fā)人員??: ?????ddm?2007/1/13
'
*開(kāi)發(fā)說(shuō)明??: ?????例如?AAABREP.ABACDT列?,使用?"Select?"?&?MG2XYCol("AAABREP.ABACDT")?&?"?As?ABACDT?From?AAABREP"?轉(zhuǎn)換
'
*傳入值????:???????strColName??????--需要轉(zhuǎn)換的列名
'
*傳入值????:???????strDefaultValue?--如果不是一個(gè)符合的日期時(shí)的值
'
*回傳值????:???????String??????????--轉(zhuǎn)換後的SQL語(yǔ)句
'
'****************************************************************************************************************
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
?
西元列轉(zhuǎn)換為民國(guó)日期列
'
'****************************************************************************************************************
'
*程式功能??:???????將一個(gè)西元列轉(zhuǎn)換為民國(guó)日期列
'
*開(kāi)發(fā)人員??: ?????ddm?2007/1/13
'
*開(kāi)發(fā)說(shuō)明??: ?????例如?AAABREP.ABACDT列?,使用?"Select?"?&?XY2MGCol("CStartDate")?&?"?As?CStartDate?From?tbContractRent"?轉(zhuǎn)換
'
*傳入值????:???????strColName??????--需要轉(zhuǎn)換的列名
'
*傳入值????:???????strDefaultValue?--如果不是一個(gè)符合的日期時(shí)的值
'
*回傳值????:???????String??????????--轉(zhuǎn)換後的SQL語(yǔ)句
'
'****************************************************************************************************************
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時(shí)數(shù)據(jù)庫(kù)中以數(shù)值類(lèi)型來(lái)存放民國(guó)日期，要做日期類(lèi)型運(yùn)算就要先轉(zhuǎn)換成日期類(lèi)型
數(shù)值型民國(guó)日期列轉(zhuǎn)換為日期列
'
'****************************************************************************************************************
'
*程式功能??:???????將一個(gè)數(shù)值型民國(guó)日期列轉(zhuǎn)換為日期列
'
*開(kāi)發(fā)人員??: ?????ddm?2007/8
'
*開(kāi)發(fā)說(shuō)明??: ?????例如?parseDate("Select?*?From?tb?Where?@Date(d1)?+?20?Days?<?@Date(d2)")
'
*傳入值????:???????strSQL????????????--需要轉(zhuǎn)換的SQL語(yǔ)句
'
*回傳值????:???????String??????????--轉(zhuǎn)換後的SQL語(yǔ)句
'
'****************************************************************************************************************
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
?
|