关于这个SQL查询，如何根据用户对某个月份和年份的选择输出28/30/31天的列表 [英] Regarding this SQL query, how can I output list of 28/30/31 days according to user's choice of certain month and year

问题描述

问题是根据用户对某些月份和年份的选择，我被困在如何输出28/30/31天，这是我的代码：



我尝试过：

The problem is I am stuck at how to output 28/30/31 days according to user's choice of certain month and year, here is my code:

What I have tried:

declare @FromDate as datetime;
declare @ToDate as datetime;
declare @OperID as varchar(20) = 'OP1';
declare @Year as int = 2018
declare @Month as int = 1

set @FromDate = convert(date,convert(varchar,@Year) + '-' + convert(varchar,@Month) + '-01')
set @ToDate = dateadd(d,-1,DATEADD(m, 1, @FromDate))

select Branch_no, operid, clock_date, [I], [O]
from
( select Branch_no, operid, 
    convert(date, clock_date) as clock_date, 
    convert(time, clock_date) as clock_time, 
    clock_type, Workstation_no
  from ROSTER_TIMECLOCK
  where Clock_date >=CONVERT(DATETIME, @FromDate, 102)
    and Clock_date <=CONVERT(DATETIME, @ToDate, 102)
    and OperID=@OperID
) as TheClock
PIVOT
( min(clock_time)
  FOR clock_type in ([I],[O])
) as ThePivot 

推荐答案

declare @Year as int = 2018
declare @Month as int = 1
select day(dateadd(dd,-1,dateadd(mm,1,datefromparts(@Year,@Month,1))))

这篇关于关于这个SQL查询，如何根据用户对某个月份和年份的选择输出28/30/31天的列表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！