计算指定年月的最后一天的自定义MYSQL函数
类别: 数据库教程
/*
Name : Fun_YiYueDeZuiHouYiTian
Function : 根据传入的格式如“200311”的年月值计算出当月的最后一天
Parameters :
BenYue 本月
Type : Char(6)
Format : "200311"
ReturnValue :
格式如“200310”的年月值
Type : Char(6)
Steps :
Author : Waxdoll Cheung
Date : 2004-04-04
*/
CREATE FUNCTION dbo.Fun_YiYueDeZuiHouYiTian
(
@BenYue CHAR(6)
)
RETURNS CHAR(10)
AS
BEGIN
DECLARE @ReturnValue CHAR(2)
DECLARE @YueFen CHAR(2)
DECLARE @NianFen CHAR(4)
SET @YueFen = RIGHT(@BenYue, 2)
SET @NianFen = LEFT(@BenYue, 4)
IF @YueFen IN (\'01\', \'03\', \'05\', \'07\', \'08\', \'10\', \'12\')
BEGIN
SET @ReturnValue = \'31\'
END
IF @YueFen IN (\'04\', \'06\', \'09\', \'11\')
BEGIN
SET @ReturnValue = \'30\'
END
IF @YueFen = \'02\'
BEGIN
IF ((@NianFen % 4 = 0 AND @NianFen % 100 <> 0) OR (@NianFen % 400 = 0))
BEGIN
SET @ReturnValue = \'29\'
END
ELSE
BEGIN
SET @ReturnValue = \'28\'
END
END
RETURN @NianFen + \'-\' + @YueFen + \'-\' + @ReturnValue
END
Name : Fun_YiYueDeZuiHouYiTian
Function : 根据传入的格式如“200311”的年月值计算出当月的最后一天
Parameters :
BenYue 本月
Type : Char(6)
Format : "200311"
ReturnValue :
格式如“200310”的年月值
Type : Char(6)
Steps :
Author : Waxdoll Cheung
Date : 2004-04-04
*/
CREATE FUNCTION dbo.Fun_YiYueDeZuiHouYiTian
(
@BenYue CHAR(6)
)
RETURNS CHAR(10)
AS
BEGIN
DECLARE @ReturnValue CHAR(2)
DECLARE @YueFen CHAR(2)
DECLARE @NianFen CHAR(4)
SET @YueFen = RIGHT(@BenYue, 2)
SET @NianFen = LEFT(@BenYue, 4)
IF @YueFen IN (\'01\', \'03\', \'05\', \'07\', \'08\', \'10\', \'12\')
BEGIN
SET @ReturnValue = \'31\'
END
IF @YueFen IN (\'04\', \'06\', \'09\', \'11\')
BEGIN
SET @ReturnValue = \'30\'
END
IF @YueFen = \'02\'
BEGIN
IF ((@NianFen % 4 = 0 AND @NianFen % 100 <> 0) OR (@NianFen % 400 = 0))
BEGIN
SET @ReturnValue = \'29\'
END
ELSE
BEGIN
SET @ReturnValue = \'28\'
END
END
RETURN @NianFen + \'-\' + @YueFen + \'-\' + @ReturnValue
END
- 上一篇: 在SQL中使用CONVERT函数进行日期的查询
- 下一篇: SQL交叉查询
-= 资 源 教 程 =-
文 章 搜 索