查看完整版本: SQL SERVER 获得一个月有多少天 的函数例子

SQL SERVER 获得一个月有多少天 的函数例子

[color=#009900]-- 获得一个月有多少天
-- 经典算法[/color]

[color=#0000ff]CREATE  function[/color] DaysInMonth (@when[color=#0000ff] datetime[/color])
[color=#0000ff]returns int[/color]
[color=#0000ff]as
BEGIN
declare[/color] @rv [color=#0000ff]int[/color]

[color=#0000ff]if[/color] [color=#0080ff]datepart[/color](month, @when) = 2
[color=#0000ff]begin[/color]
    if [color=#0080ff]datepart[/color](year, @when) [color=#ff00ff]%[/color] 400 = 0
    [color=#0000ff]select [/color]@rv = 29
[color=#0000ff]else[/color]
    if [color=#0080ff]datepart[/color](year, @when) [color=#ff00ff]% [/color]100 = 0
    [color=#0000ff]select [/color]@rv = 28
[color=#0000ff]else[/color]
    if [color=#0080ff]datepart[/color](year, @when) [color=#ff00ff]% [/color]4 = 0
    [color=#0000ff]select[/color] @rv = 29
[color=#0000ff]else[/color]
    [color=#0000ff]select[/color] @rv = 28
[color=#0000ff]end [/color][color=#009900]-- if[/color]
[color=#0000ff]else[/color]
[color=#0000ff]begin[/color]
[color=#0000ff]select[/color] @rv = [color=#0000ff]case[/color] ([color=#0080ff]datepart[/color](month, @when))
    [color=#0000ff]when [/color]4 [color=#0000ff]then [/color]30
   [color=#0000ff] when[/color] 6 [color=#0000ff]then [/color]30
   [color=#0000ff] when[/color] 9 [color=#0000ff]then [/color]30
    [color=#0000ff]when [/color]11 [color=#0000ff]then[/color] 30
    [color=#0000ff]else [/color]31
    [color=#0000ff]end [/color][color=#009900]-- case[/color]
[color=#0000ff]end [/color][color=#009900]-- else[/color]

[color=#0000ff]return[/color] @rv

[color=#0000ff]END[/color][color=#009900] -- fn def[/color]


[color=#0000ff]GO
[/color]

[color=#009900]-- 另类算法[/color]
[color=#0000ff]create function [color=#000000]GetMonths (@Month [color=#0000ff]smallint[/color], @Year [color=#0000ff]int[/color])
[/color]returns smallint
as
begin
return([color=#0080ff]DAY[/color]([color=#0080ff]dateadd[/color]([color=#000000]dd,-1,[/color][color=#0080ff]DATEADD[/color]([color=#000000]m,1,[/color][color=#0080ff]cast[/color][color=#000000](@Year[/color] as varchar(4)) + [color=#ff80c0]'-'[/color] + cast[color=#000000](@Month[/color] as varchar(2)) + [color=#ff80c0]'-01'[/color]))))[/color]
[color=#009900]-- 下个月一号减去一天就是上个月最后一天。[/color]
[color=#0000ff]end[/color]
其实在MYSQL中有更简洁的表达：

mysql> [color=#0000ff]select[/color] [color=#ff00ff]right[/color]([color=#ff00ff]last_day[/color]([color=#ff00ff]now[/color]()),2) [color=#0000ff]as[/color] [color=#ff0000]'days in month'[/color];
+---------------+
| days in month |
+---------------+
| 31            |
+---------------+
1 row in set (0.00 sec)