经过月份得到前些时间有几天周末,日期和岁月函数

set @datestart =cast(SUBSTRING( @month,0,5)+’-‘+SUBSTRING(
@month,5,2)+’-01′ as datetime)
set @dateend=dateadd(ms,-3,DATEADD(mm, DATEDIFF(m,0,@datestart)+1, 0))
SET @DAYCOUNT=0
while @datestart<=@dateend
begin

(

SELECT DateName(weekday,Getdate()) –Wednesday

if(datename(weekday,@datestart)= ‘星期日’ or
datename(weekday,@datestart)=’星期六’) begin
set @DAYCOUNT=@DAYCOUNT+1
end
SET @DATESTART=DATEADD(DAY,1,@DATESTART)

create function
udf_WeekdayID(@Date datetime)
returns integer
— 返回 @Date 是 Monday 返回 1, … ,是 Sunday
返回 1
begin
必发88手机客户端,–1: Monday , … ,7: Sunday
return (@@Datefirst +
datepart(weekday,@Date))
% 7
       + case
when (@@Datefirst +
datepart(weekday,@Date))
% 7
go

   重临表示钦点日期的钦命日期部分的字符串。Datepart详见下边包车型客车列表.

CREATE FUNCTION getweekdayCount ( @month varchar(10))
RETURNS INT
AS
BEGIN
–declare @month varchar(10)
–set @month=’201802′
—通过月份获得前段日子有几天周末
DECLARE @DAYCOUNT int
DECLARE @datestart datetime
DECLARE @dateend datetime

create function
udf_DaysOfYearByDate(@Date
datetime)
RETURNS integer
— 重返年的时局 可决断 平(365)、润(36陆)

begin
return datediff(day,dateadd(year,datediff(year,0,@Date),0),dateadd(year,datediff(year,0,@Date
)+1,0))
end
go
create function
udf_DaysOfYear(@Year integer)
RETURNS integer
— 重回年的天数 可看清 平(3陆伍)、润(36陆)

begin
return datediff(day,dateadd(year,@year

(8)YEAR ( date )

end

create
function udf_PreviousWorkDate(@Date
datetime)
returns datetime
— 重回 @Date 的上2个专门的学问日
begin
/*
declare @i int
set @i = 3
declare @Date datetime
set @Date = ‘2005-01-02’
— */
return case
when (@@Datefirst +
datepart(weekday,@Date))
% 7 =
2 —
Monday
                 then
dateadd(day,-3,@Date)
            when (@@Datefirst +
datepart(weekday,@Date))
% 7 =
1 —
Sunday
                 then
dateadd(day,-2,@Date)
            else
                 dateadd(day,-1,@Date)
       end
end
go

将七天的首后天设置为从一 到七 的八个数字。

RETURN(@DAYCOUNT)
END

create function
udf_WeekOfQuarter(@Date
datetime)
— 再次来到 @Date 是所在季度的第几每一周日是当周的尾声壹天
returns int
as
begin
return datediff(week
                ,case when
(@@Datefirst +
datepart(weekday,dateadd(Quarter,datediff(Quarter,0,@Date),0))) %
7 =
1
                           then
dateadd(Quarter,datediff(Quarter,0,@Date),0) –
1
                      else
                           dateadd(Quarter,datediff(Quarter,0,@Date),0)
                 end
                ,case when
(@@Datefirst +
datepart(weekday,@Date))
% 7 =
1
                           then
@Date –
1
                      else
                           @Date
                 end
               ) + 1
end
go

回到跨多少个内定日期的日期边界数和时间界限数。

create function
udf_WeekOfYear(@Date datetime)
— 重临 @Date 是所在年的第几周周日是当周的末梢一天
returns int
as
begin
return datediff(week
                ,case when
(@@Datefirst +
datepart(weekday,dateadd(day,0,datediff(day,0,dateadd(year,datediff(year,0,@Date),0))))) %
7 =
1
                           then
dateadd(day,-1,dateadd(day,0,datediff(day,0,dateadd(year,datediff(year,0,@Date),0))))
                      else
                           dateadd(day,0,datediff(day,0,dateadd(year,datediff(year,0,@Date),0))) –date
所在年的首后天 即: 11月壹号
                 end
                ,case when
(@@Datefirst +
datepart(weekday,@Date))
% 7 =
1
                           then
dateadd(day,-1,@Date)
                      else
                           @Date
                 end
               ) + 1
end
go

(4)DATEDIFF ( datepart , startdate
, enddate )

一年的第1天 
SELECT  DATEADD(yy,  DATEDIFF(yy,0,getdate()),  0) 

(5)DAY ( date )

T-SQL: 17个与日期时间相关的自定义函数(UDF),周伍看作周的末段一天,均不受
@@DateFirst、语言版本影响
都以从老小说里搜罗或提炼出来的!
提示:
(@@Datefirst +
datepart(weekday,@Date))
% 7 判别周几是最保险的! 与 @@DateFirst 非亲非故,与语言版本毫无干系
@@DateFirst 大概会变成 datepart(weekday,@Date)
不等同!
不论是 @@DateFirst 等于几,无论是什么语言版本的 SQL Server
上面永恒恒创制!@@Datefirst +
datepart(weekday,@Date)):
二、三、四、伍、陆、0、一 分别表示 周壹 到 周5
— */
create function
udf_GetAge(@StartDate datetime,@EndDate
datetime)
returns integer
— 重回准确年龄 select
dbo.udf_GetAge(‘1949-10-01’,getdate())
begin
return datediff(year,@StartDate,@EndDate)
       – case
when datediff(day,dateadd(year,datediff(year,@StartDate,@EndDate),@StartDate),@EndDate)
>= 0
                   then 0
              else
                   1
         end
end
go

回去表示钦命日期的“年”部分的整数。

create function
udf_HalfDay(@Date datetime)
returns datetime
— 再次回到 @Date 是 早上 重返 @Date 的零点,@Date 是
深夜 重回 @Date 的拾2点
as
begin
return case
when datepart(hour,@Date)
go
create function
udf_WeekDiff(@StartDate
datetime,@EndDate
datetime)
returns integer
— 返回 [@StartDate , @EndDate] 之间周数
周四是当周的末尾一天
begin
return datediff(week,@StartDate,@EndDate)
— + 1
       +
case when
(@@Datefirst +
datepart(weekday,@StartDate)) %
7 =
1
                   then 1
              else
                   0
         end
       – case
when (@@Datefirst +
datepart(weekday,@EndDate))
% 7 =
1
                   then 1
              else 0
         end
end
go

(2)SET DATEFIRST { number |
@number_var }

create function
udf_GetStar (@ datetime)
RETURNS varchar(100)
— 重回日期所属星座
BEGIN
RETURN
(
–declare @ datetime
–set @ = getdate()
select max(star)
from
(
select ‘魔羯座’
as star,1 as
[month],1 as
[day]
union all
select ‘水瓶座’,1,20
union all
select ‘双鱼座’,2,19
union all
select ‘牡羊座’,3,21
union all
select ‘金牛座’,4,20
union all
select ‘双子座’,5,21
union all
select ‘巨蟹座’,6,22
union all
select ‘狮子座’,7,23
union all
select ‘处女座’,8,23
union all
select ‘天秤座’,9,23
union all
select ‘天蝎座’,10,24
union all
select ‘射手座’,11,22
union all
select ‘魔羯座’,12,22
) stars
where dateadd(month,[month]

(一)获得当前几天子是星期几

下二个月的末段1天  :前段时间先是天减二ms.
SELECT  dateadd(ms,-2,DATEADD(mm,  DATEDIFF(mm,0,getdate()),  0)) 

(四)怎么样得到有些月的命局

create function
udf_WeekDay(@ int,@Date
datetime)
returns datetime
— 再次回到 @Date 映射到 所在周的任何天
礼拜二是当周的最后壹天
begin
/*
–星期三正是(上壹)周的末尾一天
当 @ = 7 代表将 @Date 映射到 所在周的周日
可用来按周汇总 Group by,均帮衬跨年跨月多少
*/
return dateadd(day
               ,case when
(@@Datefirst +
datepart(weekday,@Date))
% 7 =
0 –周六
                         
then case
when @ between
1 and
6
                                         then @

(5)三个季度多少天

  • 1
    end
    return @Date
    end
    go

以datetime 值的SQL Server 200伍 标准内部格式返回当前系统日期和时间。

 

(四)日期部分的列表

当日的零时
SELECT  DATEADD(dd,  DATEDIFF(dd,0,getdate()),  0) 

贰、取特定日期

去掉时分秒 
DATEADD(day, 
DATEDIFF(day,0,getdate()),  0) 
来得星期几 
select  datename(weekday,getdate())   
哪些取得有个别月的气数 
SELECT  Day(dateadd(ms,-3,DATEADD(mm,  DATEDIFF(m,0,getdate())+1,  0))) 

3、其它

本周的星期一 
select dateadd(wk,datediff(wk,0,getdate()),0)

(3)DATEADD (datepart , number, date
)

认清是还是不是闰年: 
SELECT  case 
day(dateadd(mm,  2,  dateadd(ms,-3,DATEADD(yy,  DATEDIFF(yy,0,getdate()),  0)))) 
when  28  then 
‘平年’ 
else  ‘闰年’ 
end 
2个季度多少天 
declare  @m 
tinyint,@time 
smalldatetime 
select  @m=month(getdate()) 
select  @m=case 
when  @m 
between  1  and 
3  then 

                       when 
@m  between 
4  and 
6  then 

                       when 
@m  between 
7  and 
9  then 

                       else 
10  end 
select  @time=datename(year,getdate())+’-‘+convert(varchar(10),@m)+’-01′ 
select  datediff(day,@time,dateadd(mm,3,@time))

SELECT CONVERT(VARCHAR(10),GETDATE(),120) –返回2006-11-08

  • 1,dateadd(year,year(@)
  • year(0),0)) +
    [day]
  • 1 =
    (
    select max(dateadd(month,[month]
  • 1,dateadd(year,year(@)
  • year(0),0)) +
    [day]
  • 1)
    from (
    select ‘魔羯座’
    as star,1 as
    [month],1 as
    [day]
    union all
    select ‘水瓶座’,1,20
    union all
    select ‘双鱼座’,2,19
    union all
    select ‘牡羊座’,3,21
    union all
    select ‘金牛座’,4,20
    union all
    select ‘双子座’,5,21
    union all
    select ‘巨蟹座’,6,22
    union all
    select ‘狮子座’,7,23
    union all
    select ‘处女座’,8,23
    union all
    select ‘天秤座’,9,23
    union all
    select ‘天蝎座’,10,24
    union all
    select ‘射手座’,11,22
    union all
    select ‘魔羯座’,12,22
    ) stars
    where @ >=
    dateadd(month,[month]
  • 1,dateadd(year,year(@)
  • year(0),0)) +
    [day]
  • 1
    )
    )
    end

(2)DATEPART ( datepart , date )

季度的第壹天 
SELECT  DATEADD(qq,  DATEDIFF(qq,0,getdate()),  0) 

SET DATEFIHavalST 一 –表示七日的率后天是“星期一”
SELECT DATENAME(WEEKDAY,GETDATE()) –Wednesday
SELECT DATEPART(weekday,GETDATE()) –返回3
–查看当前设置意况
select @@DATEFIRST

  • year(0),0),dateadd(year,@year
  • year(0) +
    1,0))
    end
    go

select @m=month(getdate())
select @m=case when @m between 1 and 3 then 1
                       when @m between 4 and 6 then 4
                       when @m between 7  and 9 then 7
                       else 10 end

一周的首后天
select @@DATEFIRST

SELECT MONTH(GETDATE()) –返回11

create function
udf_WeekOfMonth(@Date datetime)
— 重回 @Date 是所在月的第几每一周日是当周的末尾一天
returns integer
as
begin
return datediff(week
                ,case when
(@@Datefirst +
datepart(weekday,dateadd(month,datediff(month,0,@Date),0))) %
7 =
1
                           then
dateadd(month,datediff(month,0,@Date),0) –
1
                      else
                           dateadd(month,datediff(month,0,@Date),0)
                      end
                ,case when
(@@Datefirst +
datepart(weekday,@Date))
% 7 =
1
                           then
@Date-1
                      else
@Date
                 end
               ) + 1
end
go

select
@time=datename(year,getdate())+’-‘+convert(varchar(10),@m)+’-01′
select datediff(day,@time,dateadd(mm,3,@time)) —返回92

  • 6
                                        else
                                             1
                                   end
                         when
    (@@Datefirst +
    datepart(weekday,@Date))
    % 7 =
    1 –周日(七)
                             
    then case
    when @ between
    1 and
    6
                                             then @
  • 7
                                        else
                                             0
                                   end
                         when
    (@@Datefirst +
    datepart(weekday,@Date))
    % 7 between
    贰 and
    ⑥ –礼拜陆至礼拜二
                             
    then case
    when @ between
    1 and
    6
                                             then @
  • 1 –
    (@@Datefirst +
    datepart(weekday,@Date))
    % 7
                                        else
                                             8 –
    (@@Datefirst +
    datepart(weekday,@Date))
    % 7
                                   end
                    end
                   ,@Date)
    end
    go

–上面两句是同等的
SELECT DATENAME(WEEKDAY,0)
SELECT DATENAME(WEEKDAY,’1900-01-01′)

create function
udf_WorkDateAdd(@i integer,@Date
datetime)
returns datetime
— 重临 @Date 加上1段 @i
个专业日的新值
begin
declare @ int
set @ =
0
while @ =
0
                         then
–dbo.udf_nextworkdate(@Date)
                             
case when
(@@Datefirst +
datepart(weekday,@Date))
% 7 =
6 —
Friday
                                       
then dateadd(day,3,@Date)
                                   when
(@@Datefirst +
datepart(weekday,@Date))
% 7 =
0 —
saturday
                                       
then dateadd(day,2,@Date)
                                   else
                                        dateadd(day,1,@Date)
                              end
                    else
                         –dbo.udf_previousworkdate(@Date)
                        
case when
(@@Datefirst +
datepart(weekday,@Date))
% 7 =
2 —
Monday
                                  
then dateadd(day,-3,@Date)
                              when
(@@Datefirst +
datepart(weekday,@Date))
% 7 =
1 —
Sunday
                                  
then dateadd(day,-2,@Date)
                              else
                                   dateadd(day,-1,@Date)
                         end
               end
               set @ = @

(7)MONTH ( date )

create function
udf_NextWorkDate(@Date datetime)
returns datetime
— 重临 @Date 的下多个专门的学业日
begin
/*
declare @i int
set @i = 3
declare @Date datetime
set @Date = ‘2005-01-02’
— */
return case
when (@@Datefirst +
datepart(weekday,@Date))
% 7 =
6 —
Friday
                 then
dateadd(day,3,@Date)
            when (@@Datefirst +
datepart(weekday,@Date))
% 7 =
0 —
saturday
                 then
dateadd(day,2,@Date)
            else
                 dateadd(day,1,@Date)
       end
end
go

(1)DATENAME ( datepart ,date )

 

再次来到表示钦定日期的“月”部分的平头。

SQL Server 日期算法

(3)SET DATEFORMAT { format |
@format_var }

贰个月的首后天 
select dateadd(mm,datediff(mm,0,getdate()),0)

SELECT GetDate()   –返回2006-11-08 13:37:56.233

下一个月的最后1天 
SELECT  dateadd(ms,-2,DATEADD(mm,  DATEDIFF(m,0,getdate())+1,  0))

SELECT Day(dateadd(ms,-3,DATEADD(mm, DATEDIFF(m,0,’2006-02-03′)+1,0))) 
—返回28

前一个月的率先个星期一    

SELECT DATEDIFF(month,’2006-10-11′,’2006-11-01′) –返回1

create function
udf_WeekdayDiff(@Weekday
integer,@StartDate
datetime,@EndDate
datetime)
returns integer
— — 返回 [@StartDate , @EndDate] 之间星期三 到
周2的个数 礼拜2是当周的结尾一天
begin
— @Weekday: 1: Monday , … ,7:
Sunday
return datediff(week,@StartDate,@EndDate)
       + case
when (@@Datefirst +
datepart(weekday,@StartDate)) %
7
                   + case
when (@@Datefirst +
datepart(weekday,@StartDate)) %
7 =
0
                               then
7
                          else
                               0
                     end
> @Weekday
% 7 +
1
                   then 0
              else 1
         end
       – case
when (@@Datefirst +
datepart(weekday,@EndDate))
% 7
                   + case
when (@@Datefirst +
datepart(weekday,@EndDate))
% 7 =
0
                               then
7
                          else
0
                     end
>= @Weekday
% 7 +
1
                   then
                        0
              else
                   1
         end
/* test:
declare @b datetime
declare @e datetime
set @b = ‘2004-01-29’
set @e = ‘2004-09-05’
select @b as BeginDate ,@e as EndDate
,dbo.udf_WeekdayDiff(1,@b,@e) as CountOfMonday
,dbo.udf_WeekdayDiff(2,@b,@e) as CountOfTuesday
,dbo.udf_WeekdayDiff(3,@b,@e) as CountOfWednesday
,dbo.udf_WeekdayDiff(4,@b,@e) as CountOfThursday
,dbo.udf_WeekdayDiff(5,@b,@e) as CountOfFriday
,dbo.udf_WeekdayDiff(6,@b,@e) as CountOfSaturday
,dbo.udf_WeekdayDiff(7,@b,@e) as CountOfSunday
*/
end
go

 

回到一个整数,表示内定日期的天datepart 部分。

SELECT DateName(day,Getdate()) –返回8

SELECT DATEADD(week,一,GetDate()) –当后天期加一周后的日子

壹、常用日期方法(上面的GetDate() = ‘200陆-1一-08 一三:3柒:5陆.23叁’)

设置用于输入datetime 或smalldatetime
数据的日期部分(月/日/年)的次第。
…有效参数包罗mdy、dmy、ymd、ydm、myd 和dym。
…该装置仅用在将字符串调换为日期值时的表达中。它不影响日期值的显示。
…SET DATEFOKugaMAT 的安装是在实践或运转时设置,而不是在条分缕析时设置。
…SET DATEFOOdysseyMAT 将覆盖SET LANGUAGE 的隐式日期格式设置。
上面是例证:
— Set date format to year, day, month.
SET DATEFORMAT ydm;
GO
DECLARE @datevar DATETIME;
SET @datevar = ‘1998/31/12’;
SELECT @datevar AS DateVar;
GO
— Set date format to year, month, day.
SET DATEFORMAT ymd;
GO
DECLARE @datevar DATETIME;
SET @datevar = ‘1998/12/31’;
SELECT @datevar AS DateVar;
GO

    再次来到表示钦点日期的钦定日期部分的整数。

(6)GETDATE()

SELECT day(GetDate()) –返回8

数据类型

范围

精确度

datetime

1753 年 1 月 1 日到 9999 年 12 月 31 日

3.33 毫秒

smalldatetime

1900 年 1 月 1 日到 2079 年 6 月 6 日

1 分钟

SELECT DATEADD(quarter, DATEDIFF(quarter,0,getdate()), 0)—返回2006-10-01
00:00:00.000

(2)总括哪1天是本周的星期1

SELECT DATEADD(week, DATEDIFF(week,’1900-01-01′,getdate()),
‘1900-01-01’)  –返回2006-11-06 00:00:00.000

SELECT DATEADD(week, DATEDIFF(week,0,getdate()),0)     

日期部分

缩写

year

yy, yyyy

quarter

qq, q

month

mm, m

dayofyear

dy, y

day

dd, d

week

wk, ww

weekday

dw

hour

hh

minute

mi, n

second

ss, s

millisecond

ms

declare @m tinyint,@time smalldatetime

(3)当前季度的第贰天

    重回给钦赐日期加上几个时光距离后的新datetime 值。

(6)获得年月日(yyyy-MM-dd)

SELECT YEAR(GETDATE()) –返回2006

SELECT DATEPART(year,Getdate()) –返回2006

(1)–上边包车型大巴言传身教将日期钦赐为数字。数据库引擎将0
解释为1903 年1 月一 日。
SELECT MONTH(0), DAY(0), YEAR(0) –返回1    1   1900