SQL语句获取系统⽇期
获取当前⽇期：
--获取当前⽇期(如：yyyymmdd)
select CONVERT (nvarchar(12),GETDATE(),112)
--获取当前⽇期(如：yyyymmdd hh:MM:ss)
select GETDATE()
--获取当前⽇期(如：yyyy-mm-dd)
Select Datename(year,GetDate())+'-'+Datename(month,GetDate())+'-'+Datename(day,GetDate())
--获取当前⽇期(如：yyyy/mm/dd)
select DATENAME(YEAR,GETDATE())+'/'+DATENAME(MONTH,GETDATE())+'/'+DATENAME(DAY,GETDATE())
--获取⼏种⽇期
select DATENAME(YEAR,GETDATE()) --年份(YYYY)
select DATENAME(YY,GETDATE())
select DATENAME(MM,GETDATE()) --⽉份
select DATENAME(DD,GETDATE()) --⽇期
select dateName(hh,getdate()) --获取⼩时
select DATENAME(MI,GETDATE()) --获取分钟
select DATENAME(SECOND,GETDATE()) --获取秒
select DATENAME(WEEK,GETDATE()) --获取当前星期(周)是这⼀年中的第⼏个星期(周)
select DATENAME(WEEKDAY,GETDATE()) --星期⼏
--⽇期转换
--例如2011-10-24 15:57:16
select CONVERT(varchar, getdate(), 120 )
select CONVERT(varchar,GETDATE(),111)+' '+DATENAME(HH,GETDATE())+':'+DATENAME(MI,GETDATE())+':'+DATENAME(SECOND,GETDATE())
--例如20111024155657
select replace(replace(replace(CONVERT(varchar, getdate(), 120 ),'-',''),' ',''),':','')
--例如2011/10/24
select CONVERT(varchar(12) , getdate(), 111 )
--例如20111024
select CONVERT(varchar(12) , getdate(), 112 )
--例如111024 (取⽇期后6位)
select CONVERT(varchar(12) , getdate(), 12 )
--例如2011.10.24
select CONVERT(varchar(12) , getdate(), 102 )
--例如10/24/2011
select CONVERT(varchar(12) , getdate(), 101 )
--例如24/10/2011
select CONVERT(varchar(12) , getdate(), 103 )
--例如24.10.2011
select CONVERT(varchar(12) , getdate(), 104 )
--例如24-10-2011
select CONVERT(varchar(12) , getdate(), 105 )
--例如24 10 2011
select CONVERT(varchar(12) , getdate(), 106 )
select CONVERT(varchar(12) , getdate(), 106 )
--例如10 24, 2011
select CONVERT(varchar(12) , getdate(), 107 )
--例如16:01:18
select CONVERT(varchar(12) , getdate(), 108 )
--例如10 24 2011
select CONVERT(varchar(12) , getdate(), 109 )
--例如10-24-2011
select CONVERT(varchar(12) , getdate(), 110 )
--例如24 10 2011 1
select CONVERT(varchar(23) , getdate(), 113 )
-
-例如16:04:27:590(590为毫秒)
select CONVERT(varchar(12) , getdate(), 114 )
--⽇期格式为：yyyy/mm/dd hh:mm:ss
select CONVERT(varchar(12),GETDATE(),111)+' '+DATENAME(HH,GETDATE())+':'+DATENAME(MI,GETDATE())+':'+ DATENAME(SECOND,GETDATE())
--⽇期格式为：yyyy-mm-dd hh:mm:ss
select CONVERT(varchar(30),GETDATE(),120)+':'+DATENAME(MILLISECOND,GETDATE())+':'+DATENAME(MCS,GETDATE())
select CONVERT(varchar(30),GETDATE(),120)+':' + DATENAME(MILLISECOND,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 * 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语句查询日期select * from tb where datediff(week , 时间字段 ,getdate()) = 1
下周
select * from tb where datediff(week , 时间字段 ,getdate()) = -1
--------------------------------------------------------
select convert(varchar(10),getdate(),120)
昨天
select convert(varchar(10),getdate() - 1,120)
明天
select convert(varchar(10),getdate() + 1,120)
最近七天
select * from tb where 时间字段 >= convert(varchar(10),getdate() - 7,120)
随后七天
select * from tb where 时间字段 <= convert(varchar(10),getdate() + 7,120) and 时间字段 >= 时间字段
上⽉
select * from tb where month(时间字段) = month(getdate()) - 1
本⽉
select * from tb where month(时间字段) = month(getdate())
下⽉
select * from tb where month(时间字段) = month(getdate()) + 1
本周
select * from tb where datediff(week , 时间字段 ,getdate()) = 0
本周周⼀开始
select * from u_user_Current_month_Score_Got_Log where
convert(char(10),insertTime,120)>=convert(char(10),DATEADD(wk, DATEDIFF(wk,0,getdate()), 0),120)
and convert(char(10),insertTime,120)<=convert ( char(10),DATEADD( wk, DATEDIFF(wk,0,getdate()), 6 ),120 )
第⼀种本周周⼀开始
set datefirst 1 --以周1为第⼀天
select * From u_user_Current_month_Score_Got_Log
where datepart(week,inserttime) = datepart(week,dateadd(day,-1,getdate()))
第⼆种本周周⼀开始
select * From u_user_Current_month_Score_Got_Log
where datepart(week,dateadd(day,-1,inserttime)) = datepart(week,dateadd(day,-1,getdate())) where datepart(week,dateadd(day,-1,inserttime)) = datepart(week,dateadd(day,-1,getdate()))
上周周⼀开始
select * From u_user_Current_month_Score_Got_Log
where datepart(week,dateadd(day,-1,inserttime)) = datepart(week,dateadd(day,-1,getdate()))-1
上周
select * from tb where datediff(week , 时间字段 ,getdate()) = 1
下周
select * from tb where datediff(week , 时间字段 ,getdate()) = -1
--------------------------------------------------------
昨天：dateadd(day,-1,getdate())
明天：dateadd(day,1,getdate())
上⽉：month(dateadd(month, -1, getdate()))
本⽉：month(getdate())
下⽉：month(dateadd(month, 1, getdate()))
本⽂来⾃CSDN博客，转载请标明出处：blog.csdn/shikun187/archive/2008/11/24/3360275.aspx
发表评论