不用函数,直接case when 即可. select case when datepart(mm,getdate()) between 1 and 3 then '01-01' when datepart(mm,getdate()) between 4 and 6 then '06-01' when datepart(mm,getdate()) between 7 and 9 then '09-01' when datepart(mm,getdate()) between 10 and 12 then '12-01' end/*
----- 09-01(所影响的行数为 1 行) */
把上面的getdate()换成你自己的实际日期即可.
declare @dt datetime set @dt='2011-10-05' select ltrim(year(@dt))+'-'+ltrim((month(@dt)-1)/3*3+1)+'-01'
create proc aa @date datetime as begin set @date=isnull(@date,0);select case when right(convert(varchar(10),@date,120),5) between '01-01' and '03-31' then left(convert(varchar(10),@date,120),5)+'01-01' when right(convert(varchar(10),@date,120),5) between '04-01' and '06-31' then left(convert(varchar(10),@date,120),5)+'04-01' when right(convert(varchar(10),@date,120),5) between '07-01' and '09-30' then left(convert(varchar(10),@date,120),5)+'07-01' when right(convert(varchar(10),@date,120),5) between '10-01' and '12-31' then left(convert(varchar(10),@date,120),5)+'10-01' end end --测试数据 exec aa '2011-08-01'
SQL Server 日期算法一周的第一天 select @@DATEFIRST一个月的第一天 select dateadd(mm,datediff(mm,0,getdate()),0)本周的星期一 select dateadd(wk,datediff(wk,0,getdate()),0)一年的第一天 SELECT DATEADD(yy, DATEDIFF(yy,0,getdate()), 0) 季度的第一天 SELECT DATEADD(qq, DATEDIFF(qq,0,getdate()), 0) 当天的零时 SELECT DATEADD(dd, DATEDIFF(dd,0,getdate()), 0) 上个月的最后一天 :本月第一天减2ms. SELECT dateadd(ms,-2,DATEADD(mm, DATEDIFF(mm,0,getdate()), 0)) 本月的最后一天 SELECT dateadd(ms,-2,DATEADD(mm, DATEDIFF(m,0,getdate())+1, 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))) 判断是否闰年: SELECT case day(dateadd(mm, 2, dateadd(ms,-3,DATEADD(yy, DATEDIFF(yy,0,getdate()), 0)))) when 28 then '平年' else '闰年' end 一个季度多少天 declare @m tinyint,@time smalldatetime 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 @time=datename(year,getdate())+'-'+convert(varchar(10),@m)+'-01' select datediff(day,@time,dateadd(mm,3,@time))
CREATE FUNCTION f (@DATE datetime) RETURNS varchar(5) AS BEGIN DECLARE @rtn varchar(5) select @rtn =( case when datepart(mm,@DATE) between 1 and 3 then '01-01' when datepart(mm,@DATE) between 4 and 6 then '06-01' when datepart(mm,@DATE) between 7 and 9 then '09-01' when datepart(mm,@DATE) between 10 and 12 then '12-01' end) RETURN(@rtn) END goSELECT dbo.f(getdate())drop function f/* ----- 09-01(所影响的行数为 1 行)*/
create function getquarterfirst (@dt datetime) returns datetime as begin declare @dt1 datetime set @dt1= ltrim(year(@dt))+'-'+ltrim((month(@dt)-1)/3*3+1)+'-01' return @dt1 end go select dbo.getquarterfirst('2011-02-03') select dbo.getquarterfirst('2011-05-30') select dbo.getquarterfirst('2011-10-05') /* ----------------------- 2011-01-01 00:00:00.000(1 行受影响) ----------------------- 2011-04-01 00:00:00.000(1 行受影响) ----------------------- 2011-10-01 00:00:00.000(1 行受影响)*/
create function getquarterfirst (@dt datetime) returns datetime as begin return ltrim(year(@dt))+'-'+ltrim((month(@dt)-1)/3*3+1)+'-01' end go select dbo.getquarterfirst('2011-02-03') select dbo.getquarterfirst('2011-05-30') select dbo.getquarterfirst('2011-10-05') drop function dbo.getquarterfirst /* ----------------------- 2011-01-01 00:00:00.000(1 行受影响) ----------------------- 2011-04-01 00:00:00.000(1 行受影响) ----------------------- 2011-10-01 00:00:00.000(1 行受影响)*/
select
case when datepart(mm,getdate()) between 1 and 3 then '01-01'
when datepart(mm,getdate()) between 4 and 6 then '06-01'
when datepart(mm,getdate()) between 7 and 9 then '09-01'
when datepart(mm,getdate()) between 10 and 12 then '12-01'
end/*
-----
09-01(所影响的行数为 1 行)
*/
set @dt='2011-10-05'
select ltrim(year(@dt))+'-'+ltrim((month(@dt)-1)/3*3+1)+'-01'
create proc aa
@date datetime
as
begin
set @date=isnull(@date,0);select case when right(convert(varchar(10),@date,120),5) between '01-01' and '03-31'
then left(convert(varchar(10),@date,120),5)+'01-01'
when right(convert(varchar(10),@date,120),5) between '04-01' and '06-31'
then left(convert(varchar(10),@date,120),5)+'04-01'
when right(convert(varchar(10),@date,120),5) between '07-01' and '09-30'
then left(convert(varchar(10),@date,120),5)+'07-01'
when right(convert(varchar(10),@date,120),5) between '10-01' and '12-31'
then left(convert(varchar(10),@date,120),5)+'10-01' end
end
--测试数据
exec aa '2011-08-01'
SET @dt=GETDATE()DECLARE @number int
SET @number=3--1.指定日期该年的第一天或最后一天
--A. 年的第一天
SELECT CONVERT(char(5),@dt,120)+'1-1'--B. 年的最后一天
SELECT CONVERT(char(5),@dt,120)+'12-31'
--2.指定日期所在季度的第一天或最后一天
--A. 季度的第一天
SELECT CONVERT(datetime,
CONVERT(char(8),
DATEADD(Month,
DATEPART(Quarter,@dt)*3-Month(@dt)-2,
@dt),
120)+'1')--B. 季度的最后一天(CASE判断法)
SELECT CONVERT(datetime,
CONVERT(char(8),
DATEADD(Month,
DATEPART(Quarter,@dt)*3-Month(@dt),
@dt),
120)
+CASE WHEN DATEPART(Quarter,@dt) in(1,4)
THEN '31'ELSE '30' END)--C. 季度的最后一天(直接推算法)
SELECT DATEADD(Day,-1,
CONVERT(char(8),
DATEADD(Month,
1+DATEPART(Quarter,@dt)*3-Month(@dt),
@dt),
120)+'1')
--3.指定日期所在月份的第一天或最后一天
--A. 月的第一天
SELECT CONVERT(datetime,CONVERT(char(8),@dt,120)+'1')--B. 月的最后一天
SELECT DATEADD(Day,-1,CONVERT(char(8),DATEADD(Month,1,@dt),120)+'1')--C. 月的最后一天(容易使用的错误方法)
SELECT DATEADD(Month,1,DATEADD(Day,-DAY(@dt),@dt))
--4.指定日期所在周的任意一天
SELECT DATEADD(Day,@number-DATEPART(Weekday,@dt),@dt)
--5.指定日期所在周的任意星期几
--A. 星期天做为一周的第1天
SELECT DATEADD(Day,@number-(DATEPART(Weekday,@dt)+@@DATEFIRST-1)%7,@dt)--B. 星期一做为一周的第1天
SELECT DATEADD(Day,@number-(DATEPART(Weekday,@dt)+@@DATEFIRST-2)%7-1,@dt)
select @@DATEFIRST一个月的第一天
select dateadd(mm,datediff(mm,0,getdate()),0)本周的星期一
select dateadd(wk,datediff(wk,0,getdate()),0)一年的第一天
SELECT DATEADD(yy, DATEDIFF(yy,0,getdate()), 0) 季度的第一天
SELECT DATEADD(qq, DATEDIFF(qq,0,getdate()), 0) 当天的零时
SELECT DATEADD(dd, DATEDIFF(dd,0,getdate()), 0) 上个月的最后一天 :本月第一天减2ms.
SELECT dateadd(ms,-2,DATEADD(mm, DATEDIFF(mm,0,getdate()), 0)) 本月的最后一天
SELECT dateadd(ms,-2,DATEADD(mm, DATEDIFF(m,0,getdate())+1, 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))) 判断是否闰年:
SELECT case day(dateadd(mm, 2, dateadd(ms,-3,DATEADD(yy, DATEDIFF(yy,0,getdate()), 0))))
when 28 then '平年' else '闰年' end
一个季度多少天
declare @m tinyint,@time smalldatetime
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 @time=datename(year,getdate())+'-'+convert(varchar(10),@m)+'-01'
select datediff(day,@time,dateadd(mm,3,@time))
RETURNS varchar(5)
AS
BEGIN
DECLARE @rtn varchar(5)
select @rtn =(
case when datepart(mm,@DATE) between 1 and 3 then '01-01'
when datepart(mm,@DATE) between 4 and 6 then '06-01'
when datepart(mm,@DATE) between 7 and 9 then '09-01'
when datepart(mm,@DATE) between 10 and 12 then '12-01'
end)
RETURN(@rtn)
END
goSELECT dbo.f(getdate())drop function f/*
-----
09-01(所影响的行数为 1 行)*/
(@dt datetime)
returns datetime
as
begin
declare @dt1 datetime
set @dt1= ltrim(year(@dt))+'-'+ltrim((month(@dt)-1)/3*3+1)+'-01'
return @dt1
end
go
select dbo.getquarterfirst('2011-02-03')
select dbo.getquarterfirst('2011-05-30')
select dbo.getquarterfirst('2011-10-05')
/*
-----------------------
2011-01-01 00:00:00.000(1 行受影响)
-----------------------
2011-04-01 00:00:00.000(1 行受影响)
-----------------------
2011-10-01 00:00:00.000(1 行受影响)*/
(@dt datetime)
returns datetime
as
begin
return ltrim(year(@dt))+'-'+ltrim((month(@dt)-1)/3*3+1)+'-01'
end
go
select dbo.getquarterfirst('2011-02-03')
select dbo.getquarterfirst('2011-05-30')
select dbo.getquarterfirst('2011-10-05')
drop function dbo.getquarterfirst
/*
-----------------------
2011-01-01 00:00:00.000(1 行受影响)
-----------------------
2011-04-01 00:00:00.000(1 行受影响)
-----------------------
2011-10-01 00:00:00.000(1 行受影响)*/