d1-日期1
d2-日期2
y-年份
m-月份
功能:年份/月份是否在两个日期d1/d2之间,返回bit(0/1)
by airzen 20071109
www.haixiaIT.com
*/
create function dbo.isInDate
(
@d1 dateTime, --日期1(min)
@d2 dateTime,--日期2(max)
@y int, --年份
@m int --月份
)
returns bit
as
begin
declare @retV bit
declare @t1 int
declare @t2 int
declare @t3 int
set @t1 = year(@d1) * 12 + month(@d2)
set @t2 = year(@d2) * 12 + month(@d2)
set @t3 = @y * 12 + @m
if( @t3 >= @t1 AND @t3 <= @t2 )
set @retV = 1
else
set @retV = 0
return @retV
END
函数原理解析:可以将年份月份(yyyyMM)当做成一个分段式的12进制数据来处理即yyyy * 12 + MM ,那么如果两个 dateTime型日期数据 @d1,@d2 要判断(@y,@m)是否在d1与d2之间的一个日期 就可以如上算法.
如果是三个都是dateTime型变量比较就比较简单了,可以直接用< 或>进行比较,
附eg
具体程序调用 (调用2007,10月份的请假信息,同时计算出当月请假时长)
Select WorkNo AS 工号,
EmpName AS 姓名,
DBO.F_GetDepart(GroupID) AS 部室,
Station AS 职位,
StationGrade AS 职位级别,
DBO.F_GetJiaQiType(type) AS 请假类别,
totalDays AS 时长,
dbo.getLeaveDaysInMonth(id,2007,10) AS 当月时长,
ratifier AS 审批人,
StartDate AS 开始时间,
EndDate AS 结束时间
FROM V_Leave_All
Where agree = 4
AND status = 3
AND dbo.isInDate(StartDate,EndDate,2007,10)=1
ORDER BY id DESC
海峡IT网原创文章,转载请指明出处.谢谢!
