SQL日期
操作日期的SQL语句大全
--时间段
本周：select * from table where datediff(week,C_CALLTIME,getdate())=0 --C_CALLTIME 为日期字段
本月：select * from table where datediff(Month,C_CALLTIME,getdate())=0 --C_CALLTIME 为日期字段
本季：select * from table where datediff(qq,C_CALLTIME,getdate())=0
前半年1-6，后半年7-12：select * from table where datepart(mm,C_CALLTIME)/7 =
datepart(mm,getdate())/7
--昨天
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 * From TableName Where DateDiff(dd, GetDate(), DateTimCol) <= 7
--上周
Select * From TableName Where DateDiff(wk, DateTimCol, GetDate()) = 1
--本周
Select * From TableName Where DateDiff(wk, DateTimCol, GetDate()) = 0
--下周
Select * From TableName Where DateDiff(wk, GetDate(), DateTimCol ) = 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
--------------------------------------------------------
本周
select * from tb where datediff(week , 时间字段 ,getdate()) = 0
上周
select * from tb where datediff(week , 时间字段 ,getdate()) = 1
下周
select * from tb where datediff(week , 时间字段 ,getdate()) = -1
---具体到天
1.显示本月第一天
SELECT DATEADD(mm,DATEDIFF(mm,0,getdate()),0)
select convert(datetime,convert(varchar(8),getdate(), 120)+'01',120)
2.显示本月最后一天
select dateadd(day,-1,convert(datetime,convert
(varchar(8),dateadd(month,1,getdate()),120)+'01',120))
SELECT dateadd(ms,-3,DATEADD(mm,DATEDIFF(m,0,getdate())+1,0))
3.上个月的最后一天
SELECT dateadd(ms,-3,DATEADD(mm,DATEDIFF(mm,0,getdate()),0))
4.本月的第一个星期一
select DATEADD(wk,DATEDIFF(wk,0, dateadd(dd,6-datepart( day,getdate()),getdate())),0)
5.本年的第一天
日期字符串是什么SELECT DATEADD(yy,DATEDIFF(yy,0,getdate()),0)
6.本年的最后一天
SELECT dateadd(ms,-3,DATEADD(yy,DATEDIFF(yy,0,getdate())+1,0))
7.去年的最后一天
SELECT dateadd(ms,-3,DATEADD(yy,DATEDIFF(yy,0,getdate()),0))
8.本季度的第一天
SELECT DATEADD(qq,DATEDIFF(qq,0,getdate()),0)
9.本周的星期一
SELECT DATEADD(wk,DATEDIFF(wk,0,getdate()),0)
10.查询本月的记录
select * from tableName where DATEPART(mm, theDate) =DATEPART(mm, GETDATE()) and DATEPART(yy, theDate) = DATEPART(yy, GETDATE())
11.查询本周的记录
select * from tableName where DATEPART(wk, theDate) = DATEPART (wk, GETDATE()) and DATEPART(yy, theDate) = DATEPART(yy, GETDATE())
12.查询本季的记录注：其中:GETDATE()是获得系统时间的函数。
select * from tableName where DATEPART(qq, theDate) = DATEPART (qq, GETDATE()) and DATEPART(yy, theDate) = DATEPART(yy, GETDATE())
13.获取当月总天数:
select DATEDIFF(dd,getdate(),DATEADD (mm, 1, getdate()))
select datediff(day, dateadd(mm, datediff(mm,’’,getdate()), ’’), dateadd(mm, datediff(mm,’’,getdate()), ’1900-02-01’))
14.获取当前为星期几
select DATENAME(weekday, getdate())
SQL查询年、月、周、天
1、/*查询本年的数据*/
select * from users where year(time)=year(getdate())
2、/*查询本月的数据，time是表users中代表时间的字段*/
select * from users where month(time)=month(getdate()) and year(time)=year(getdate())
3、/*查询今天的数据，time 是表中代表时间的字段*/
select * from users where day(time)=day(getdate()) and month(time)=month(getdate()) and year(time)=year(getdate())
4、/*计算今天是星期几*/
select datename(weekday,getdate())
5、/*计算那一天是星期一*/
SELECT DATEADD(wk, DATEDIFF(wk,0,getdate()), 0)
6、/*计算那一天是周末*/
select dateadd(wk,datediff(wk,0,getdate()),6)
7、/*查询本周的数据*/
select * from users where DATEPART(wk, time) = DATEPART(wk, GETDATE()) and DATEPART(yy, time) = DATEPART(yy, GETDATE())
8、/*查询本日的记录*/
select * from users where (DATEDIFF(dd, time, GETDATE()) = 0)
9、/*查询本月的记录*/
select * from users where (DATEDIFF(mm, time, GETDATE()) = 0)
10、/*查询本年的记录*/
select * from users where (DATEDIFF(yy, time, GETDATE()) = 0)
在MySql中实现：
（1）——
本年：
select * from loanInfo where year(date)=year(getdate())
（2）——
本月：
select * from loanInfo where year(date)=year(getDate()) And month(date)=mon th(getdate())
（3）——
本日：
select * from loanInfo where year(date)=year(getDate()) And month(date)=mont h(getdate()) and Day(date)=Day(getDate())
SELECT * FROM table WHERE (MONTH(字段) = MONTH(GETDATE()))
-
-查询24小时内的:
select * from info where DateDiff(hh,datetime,getDate())<=24
select sum(SumRealMoney),DATEPART (week,ordertime) ,year(ordertime) from mainorder
group by DATEPART (week,ordertime),year(ordertime)
其中： DATEPART (week,ordertime) 返回的是该时间属于本年的第几周。

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。