SQL取前⼀天、⼀⽉、⼀年的时间
SELECT DATEADD(DAY,-1,'20121212')
SELECT DATEADD(DAY,-1,GETDATE())
SELECT DATEADD(MONTH,-1,'20121212')
SELECT DATEADD(MONTH,-1,GETDATE())isnull的用法
SELECT DATEADD(YEAR,-1,'20121212')
SELECT DATEADD(YEAR,-1,GETDATE())
SQL 取前⼀天、⼀⽉、⼀年的时间
_______________________________________
丛星期⼀⾄星期⽇为⼀周的收款
ASA:
set DATEFIRST 1  --设置每⼀周的第⼀天是星期⼀
select sum(isnull(cash.act_amt,0)) as 本期收款 ,  cash.customer_id as 客户代号from cash where  cash.approved='Y' ans_date between
convert(varchar(10),dateadd(day, 1-datepart(weekday,getdate()),getdate()),120) and convert(varchar(10),dateadd(day, 7-datepart(weekday,getdate()),getdate()),120)--取第⼀天与最后⼀天
SQL 如下：
取出本周的最后⼀天
select DATEADD(WK,DATEDIFF(WK,0,GETDATE()),6)
select sum(isnull(cash.act_amt,0)) as 本期收款 ,  cash.customer_id as 客户代号from cash where  cash.approved='Y' and datediff(week ,ans_date-1,getdate()) = 0 group by 客户代号
本周 周⽇开始⾄周六为⼀周
select * from tb where datediff(week , 时间字段 ,getdate()) = 0
上周
select * from tb where datediff(week , 时间字段 ,getdate()) = 1
下周
select * from tb where datediff(week , 时间字段 ,getdate()) = -1
----------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------上⽉
Select * From TableName Where DateDiff(mm, DateTimCol, GetDate()) = 1 --本⽉
Select * From TableName Where DateDiff(mm, DateTimCol, GetDate()) = 0 --下⽉
Select * From TableName Where DateDiff(mm, GetDate(), DateTimCol ) = 1
昨天：dateadd(day,-1,getdate())
明天：dateadd(day,1,getdate())
上⽉：month(dateadd(month, -1, getdate()))
本⽉：month(getdate())
下⽉：month(dateadd(month, 1, getdate()))
---------------------------------------------------------------------------------
--昨天　
Select * From TableName Where DateDiff(dd, DateTimCol, GetDate()) = 1 --明天
Select * From TableName Where DateDiff(dd, GetDate(), DateTimCol) = 1 --最近七天
Select * From TableName Where DateDiff(dd, DateTimCol, GetDate()) <= 7 --随后七天
-
--------------------------------------------------------------------------
当前年
select  提出⽇期, datepart(year,getdate()) as  当前年 from 供⽅资料表
前⼀年
select  提出⽇期, datepart(year,getdate())-1 as  当前年 from 供⽅资料表
后⼀年
select  提出⽇期, datepart(year,getdate())+1 as  当前年 from 供⽅资料表