在函数SQL中获取给定月份的星期日 [英] Get Sundays For a given month Date in a function SQL

问题描述

我遇到了问题.我想使用一个函数获得一个月中的星期天，尽管我已经使用过程实现了类似的结果，但是我需要在我的选择查询中调用该函数以返回每个选择的结果.我的程序代码已给出

I am facing a problem. I want to get no of sunday in a month using a function although i have achieved a similar result using a procedure but i need to call that function in my select query to return result for each select. My code for the procedure is given

    create proc [dbo].[getSundaysandSaturdays]
(
    @Year int=2016,
    @Month int=11,
    @fdays as int output
)
as
begin
    ;with dates as
    (
        select dateadd(month,@month-1,dateadd(year,@year-1900,0)) as StartDate
        union all
        select startdate + 1 from dates where month(startdate+1) = @Month
    )
    select @fdays=count(*) from dates where datediff(dd,0,startdate)%7 in (4)
    return @fdays
end

推荐答案

我进行了如下更改，还修改了使用 datepart 函数查找星期天所需的逻辑.如果还要返回星期六的计数，则将其添加到where子句中.

I have changed like below and also modified logic required to find sunday's by using datepart function. If you want to return saturday's count also then add it in where clause.

 create function [dbo].[getSundaysandSaturdays]
    (
        @Year int,
        @Month int

    ) 
    RETURNS int
    as 
    begin
    declare     @fdays int 
        ;with dates as
        (
            select dateadd(month,@month-1,dateadd(year,@year-1900,0)) as StartDate
            union all
            select startdate + 1 from dates where month(startdate+1) = @Month
        )
        select @fdays=count(*) from dates where datepart(dw,StartDate) =1
      return @fdays
      end

和如下所示的通话功能

  select  dbo.[getSundaysandSaturdays](2015,11)

这篇关于在函数SQL中获取给定月份的星期日的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！