sql 日期and時間

GetDate()

getdate(timestamp)，默認當前本地時間

取得日期/時間信息

键名	说明	返回值例子
"seconds"	秒的数字表示	0 到 59
"minutes"	分钟的数字表示	0 到 59
"hours"	小时的数字表示	0 到 23
"mday"	月份中第几天的数字表示	1 到 31
"wday"	星期中第几天的数字表示	0（表示星期天）到 6（表示星期六）
"mon"	月份的数字表示	1 到 12
"year"	4 位数字表示的完整年份	例如：1999 或 2003
"yday"	一年中第几天的数字表示	0 到 365
"weekday"	星期几的完整文本表示	Sunday 到 Saturday
"month"	月份的完整文本表示	January 到 December
0	自从 Unix 纪元开始至今的秒数，和 time() 的返回值以及用于 date() 的值类似。	系统相关，典型值为从 -2147483648 到 2147483647。

Datename()

datename(param,date)

param  指定要返回日期部分的參數

日期部分	缩写
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

例如：

select DATENAME(year,getdate()) as 'YEAR'

 

SQL  dateTime的默認格式：yyyy-mm-dd hh:mi:ss: mmm

如果箱得到日期，select convert(char(10),getdate(),120) as Date

 

1、DATEADD(datepart,number,date)

作用：返回给指定日期加一个时间间隔后新的datetime值

参数说明：

datepart：指定为日期的哪部分增加数值。

常用的值有：年(yy/yyyy),季度(qq/q),月份(mm/m),日(day/dd/d),周(wk/ww),小时(hh),分钟(mi,n),秒(ss/s)。

number:指定要增加的数值。如果是非整数将会舍去小数部分。负数表示减去相应的时间间隔。

date:表示要处理的日期。

用法：

-- 在指定日期上加三个月

select DATEADD(mm,3,'2020-12-30');

-- 指定日期减一天

select DATEADD(day,-1,'2020-12-30');

2、DATEDIFF(datepart,startdate,enddate)

作用：指定两个时间相差的时间数。比如天、小时、分钟等等。

datepart：指定返回的日期时间维度(天、小时、分钟)。

startdate:可以理解被减数。

enddate:可以理解减数。如果startdate大于enddate返回负值。

SELECT DATEDIFF(day,'2021-02-05','2021-02-09') AS DiffDate --返回 4天

3、DATENAME(deatpart,date)

作用：返回指定日期的指定部分的字符串。

date:日期表达式

用法:

DateName(quarter,GetDate()) as '第几季度'

DateName(week,GetDate()) as '一年中的第几周'

DateName(DAYOFYEAR,GetDate()) as '一年中的第几天'

DateName(year,GetDate()) as '年'

DateName(month,GetDate()) as '月'

DateName(day,GetDate()) as '日'

DateName(hour,GetDate()) as '时'

DateName(minute,GetDate()) as '分'

DateName(second,GetDate()) as '秒'

4、DATEPART(deatpart,date)

返回指定日期中指定部分的整数。用法和DATENAME相似，返回值为整数。比如:

SELECT DATEPART(weekday,'20210209') --返回值为2

SELECT DateName(weekday,'20210209') --返回值为星期二

5、DAY|MONTH|YEAR 函数

作用：返回指定日期所在 日/月/年的部分。可以用DATEPART替换。

SELECT DAY('2021-02-09') --返回 9

SELECT MONTH('2021-02-09') --返回 2

SELECT YEAR('2021-02-09') --返回 2021

6、EOMONTH(input[,month_to_add])

作用：该函数是在SQLServer 2012中引入的。EOMONTH函数用来返回指定日期之前或之后某个月的最后一天的日期。

语法结构：=EOMONTH(开始日期，指定起始日期前后的月份)

用法;

DECLARE @date DATETIME = '2021-02-09';

SELECT EOMONTH ( @date,7) AS Result; --加七个月的月末日期 2021-09-30

SELECT EOMONTH ( @date,-2) AS Result; -- 减两个月的月末日期 2020-12-31

7、FROMPARTS

作用：根据输入的年、月、日参数生成一个日期值，并返回。

格式：DATEFROMPARTS(year,month,day)

year:输入年份的整数值

month:输入1~12之间的月份数

day:输入年月所对应的day整数值

注意:如果上面三个参数，任意一个参数输入null,则返回null。

用法： SELECT DATEFROMPARTS (2020,2,09) AS [生成日期数据]; --2020-02-09

8、GETDATE()

这个太常见了，就是获取系统当前时间。

SELECT GETDATE() --返回结果 2021-02-09 08:28:11.307

9、GETUTCDATE()

返回当前的UTC时间。国内使用减八小时。

SELECT GETUTCDATE() --返回结果 2021-02-09 00:29:21.710

--------------------------------------------------------------------------------------------------------------------------------

一、统计语句

1、--统计当前【>当天00点以后的数据】

SELECT * FROM 表 WHERE CONVERT(Nvarchar, dateandtime, 111) = CONVERT(Nvarchar, GETDATE(), 111)   ORDER BY dateandtime DESC

2、--统计本周

SELECT * FROM 表 WHERE datediff(week,[dateadd],getdate())=0

3、--统计本月

SELECT * FROM 表 WHERE datediff(month,[dateadd],getdate())=0

4、统计当前

1.  
   SELECT * FROM 表 WHERE datediff(day,[dateadd],getdate())=0
2.  
   Select * From table with(nolock) Where Convert(varchar(10),[CreateTime],120) = Convert(varchar(10),getDate(),120)

二、时间函数

1、当前系统日期、时间

select getdate() 

2、dateadd   在向指定日期加上一段时间的基础上，返回新的 datetime 值，例如：向日期加上2天

select dateadd(day,2,'2004-10-15')   --返回：2004-10-17 00:00:00.000

3、datediff 返回跨两个指定日期的日期和时间边界数

select datediff(day,'2004-09-01','2004-09-18')    --返回：17

4、datepart 返回代表指定日期的指定日期部分的整数

SELECT DATEPART(month, '2004-10-15')   --返回 10

5、datename 返回代表指定日期的指定日期部分的字符串

SELECT datename(weekday, '2004-10-15')   --返回：星期五

6、day(), month(),year() --可以与datepart对照一下

1.  
   select 当前日期=convert(varchar(10),getdate(),120),
2.  
   select 当前时间=convert(varchar(8),getdate(),114),
3.  
   select datename(dw,'2004-10-15')
4.  
   select 本年第多少周=datename(week,'2004-10-15'),
5.  
   select 今天是周几=datename(weekday,'2004-10-15')

7、求相差天数

select   datediff(day,'2004-01-01',getdate()) 

8、一个月第一天的

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

9、本周的星期一

1.  
   SELECT DATEADD(wk, DATEDIFF(wk,0,getdate()), 0)
2.  
   select dateadd(wk,datediff(wk,0,getdate()),6)

10、一年的第一天

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

11、季度的第一天

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

12、当天的半夜

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

13、上个月的最后一天

SELECT   dateadd(ms,-3,DATEADD(mm,  DATEDIFF(mm,0,getdate()),   0))

14、去年的最后一天

SELECT   dateadd(ms,-3,DATEADD(yy,   DATEDIFF(yy,0,getdate()),   0)) 

15、本月的最后一天

SELECT   dateadd(ms,-3,DATEADD(mm,   DATEDIFF(m,0,getdate())+1,   0))

16、本年的最后一天

SELECT   dateadd(ms,-3,DATEADD(yy,   DATEDIFF(yy,0,getdate())+1,   0))

17、本月的第一个星期一

select   DATEADD(wk,  DATEDIFF(wk,0,dateadd(dd,6-datepart(day,getdate()),getdate())),   0)

18、查询本周注册人数

1.  
   select count(*) from [user]
2.  
   where datediff(week,create_day-1,getdate())=0

19、上周注册人数

1.  
   select count(*) from [user]
2.  
   where datediff(week,create_day-1,getdate())=1

20、本月注册人数

1.  
   select count(*) from [user]
2.  
   where datediff(month,create_day,getdate())=0

21、上月注册人数

1.  
   select count(*) from [user]
2.  
   where datediff(month,create_day,getdate())=1

如果要效率,用一下方式

22、查询本周注册人数

1.  
   select count(*) from [user]
2.  
   where create_day>=dateadd(day,2-datepart(weekday,getdate()),convert(varchar,getdate(),112))
3.  
   and create_day<dateadd(day,9-datepart(weekday,getdate()),convert(varchar,getdate(),112))

23、上周注册人数 

1.  
   select count(*) from [user]
2.  
   where create_day>=dateadd(day,-5-datepart(weekday,getdate()),convert(varchar,getdate(),112))
3.  
   and create_day<dateadd(day,2-datepart(weekday,getdate()),convert(varchar,getdate(),112))

24、本月注册人数

1.  
   select count(*) from [user]
2.  
   where create_day>=dateadd(day,1-day(getdate()),convert(varchar,getdate(),112))
3.  
   and create_day<dateadd(month,1,dateadd(day,1-day(getdate()),convert(varchar,getdate(),112)))

25、上月注册人数

1.  
   select count(*) from [user]
2.  
   where create_day>=dateadd(month,-1,dateadd(day,1-day(getdate()),convert(varchar,getdate(),112)))
3.  
   and create_day<dateadd(day,1-day(getdate()),convert(varchar,getdate(),112))

26、本周

1.  
   select count(*) from User
2.  
   where datediff(dd,create_day,getdate()) <= datepart(dw,getdate())

27、上周

1.  
   select count(*) from User
2.  
   where datediff(dd,create_day,(getdate() - datepart(dw,getdate()))) <= 7

28、本月

1.  
   select count(*) from User
2.  
   where datepart(mm,create_day) = datepart(mm,getdate())

29、上月

1.  
   select count(*) from User
2.  
   where datepart(mm,create_day) = datepart(mm,getdate()) - 1

30、本周注册人数

1.  
   select count(*) from [User]
2.  
   where datediff(dd,create_day,getdate()) <= datepart(dw,getdate())

31、上周注册人数

1.  
   select count(*) from [User]
2.  
   where datediff(dd,create_day,(getdate() - datepart(dw,getdate()))) <= 7

32、本月注册人数

1.  
   select count(*) from [User]
2.  
   where datepart(mm,create_day) = datepart(mm,getdate())

33、上月注册人数

1.  
   select count(*) from [User]
2.  
   where datepart(mm,create_day) = datepart(mm,getdate()) - 1

34、查询今日所有

SELECT * from feedback WHERE (DATEDIFF(d,fedtime,GETDATE())=0) ORDER BY fedid DESC

month(create_day)=month(getdate())本月  

month(create_day)=month(getdate())-1   上月

 

今天的所有数据：select * from 表名 where DateDiff(dd,datetime类型字段,getdate())=0

昨天的所有数据：select * from 表名 where DateDiff(dd,datetime类型字段,getdate())=1

7天内的所有数据：select * from 表名 where DateDiff(dd,datetime类型字段,getdate())<=7

30天内的所有数据：select * from 表名 where DateDiff(dd,datetime类型字段,getdate())<=30

本月的所有数据：select * from 表名 where DateDiff(mm,datetime类型字段,getdate())=0

本年的所有数据：select * from 表名 where DateDiff(yy,datetime类型字段,getdate())=0

 

 

系统函数：

系统函数

函数	参数/功能
GetDate( )	返回系统目前的日期与时间
DateDiff (interval,date1,date2)	以interval 指定的方式，返回date2 与date1两个日期之间的差值 date2-date1
DateAdd (interval,number,date)	以interval指定的方式，加上number之后的日期
DatePart (interval,date)	返回日期date中，interval指定部分所对应的整数值
DateName (interval,date)	返回日期date中，interval指定部分所对应的字符串名称

 

参数 interval的设定值：

值	缩写（Sql Server）	Access 和 ASP	说明
Year	Yy	yyyy	年 1753 ~ 9999
Quarter	Qq	q	季 1 ~ 4
Month	Mm	m	月1 ~ 12
Day of year	Dy	y	一年的日数,一年中的第几日 1-366
Day	Dd	d	日，1-31
Weekday	Dw	w	一周的日数，一周中的第几日 1-7
Week	Wk	ww	周，一年中的第几周 0 ~ 51
Hour	Hh	h	时0 ~ 23
Minute	Mi	n	分钟0 ~ 59
Second	Ss	s	秒 0 ~ 59
Millisecond	Ms	-	毫秒 0 ~ 999

access 和 asp 中用date()和now()取得系统日期时间；其中DateDiff,DateAdd,DatePart也同是能用于Access和asp中，这些函数的用法也类似

举例：

1.  
   1.GetDate() 用于sql server :select GetDate()
2.  
   2.DateDiff('s','2005-07-20','2005-7-25 22:56:32')返回值为 514592 秒
3.  
   DateDiff('d','2005-07-20','2005-7-25 22:56:32')返回值为 5 天
4.  
   3.DatePart('w','2005-7-25 22:56:32')返回值为 2 即星期一(周日为1，周六为7)
5.  
   DatePart('d','2005-7-25 22:56:32')返回值为 25即25号
6.  
   DatePart('y','2005-7-25 22:56:32')返回值为 206即这一年中第206天
7.  
   DatePart('yyyy','2005-7-25 22:56:32')返回值为 2005即2005年

Sql 取当天或当月的记录
表中的时间格式是这样的：2007-02-02 16:50:08.050, 如果直接和当天的时间比较，就总得不到准确数据,但是我们可以把这种格式的时间[格式化]成　2007-02-02,也就是只有年－月－日,然后把当天的时间也格式化成 年－月－日的格式.
这样,思路就出来了!
我们格式化日期要用到 Convert()这个函数,要用到3个参数,首先来格式化当天的日期,Convert(varchar(10),getDate(),120)
这样我们就可以把当天的日期格式化为: 2007-2-2,然后格式化数据库表中的日期
Convert(varchar(10),TimeFiled,120),最后我们就可以用一条Sql语句得到当天的数据了.
例如：

转自网络

程序代码

Select * From VIEW_CountBill Where Convert(varchar(10),[time],120) = Convert(varchar(10),getDate(),120)

注意:
Convert()函数中的各个参数的意义,第一个参数,varchar(10)是目标系统所提供的数据类型，包括 bigint 和 sql_variant。不能使用用户定义的数据类型。第二个参数是你要转换的字段,我这里是[time]。最后一个就是格式了，这个值是可选的:20或者120都可以,它遵循的是[ODBC 规范],输入/输出样式为:yyyy-mm-dd hh:mm:ss[.fff]
具体的可以参考Sql Server的联机帮助!

======================================================
T-Sql查找表中当月的记录
思路:将要查找的时间字段用Month()函数取出其中的月份,然后再取出当前月的月份,对比就OK了
例:

程序代码

Select * From VIEW_CountBill Where Month([time]) = Month(getDate())

=================================================================================================

增加阅读:

1.  
   Syntax for CAST:
2.  
   CAST ( expression AS data_type [ ( length ) ] )
3.  
    
4.  
   Syntax for CONVERT:
5.  
   CONVERT ( data_type [ ( length ) ] , expression [ , style ] )

参数

expression

任何有效的表达式。

data_type

目标数据类型。这包括 xml、bigint 和 sql_variant。不能使用别名数据类型。有关可用数据类型的详细信息，请参阅数据类型 (Transact-SQL)。

length

指定目标数据类型长度的可选整数。默认值为 30。

style

指定 CONVERT 函数如何转换 expression 的整数表达式。如果样式为 NULL，则返回 NULL。该范围是由 data_type 确定的。有关详细信息，请参阅“备注”部分。

返回类型

返回转换为 data_type 的 expression。

时间格式:

Date 和 Time 样式

如果 expression 为 date 或 time 数据类型，则 style 可以为下表中显示的值之一。其他值作为 0 进行处理。SQL Server 使用科威特算法来支持阿拉伯样式的日期格式。

 

不带世纪数位 (yy) (1)	带世纪数位 (yyyy)	标准	输入/输出 (3)
-	0 或 100 (1, 2)	默认	mon dd yyyy hh:miAM（或 PM）
1	101	美国	mm/dd/yyyy
2	102	ANSI	yy.mm.dd
3	103	英国/法国	dd/mm/yyyy
4	104	德国	dd.mm.yy
5	105	意大利	dd-mm-yy
6	106 (1)	-	dd mon yy
7	107 (1)	-	mon dd, yy
8	108	-	hh:mi:ss
-	9 或 109 (1, 2)	默认设置 + 毫秒	mon dd yyyy hh:mi:ss:mmmAM（或 PM）
10	110	美国	mm-dd-yy
11	111	日本	yy/mm/dd
12	112	ISO	yymmdd yyyymmdd
-	13 或 113 (1, 2)	欧洲默认设置 + 毫秒	dd mon yyyy hh:mi:ss:mmm(24h)
14	114	-	hh:mi:ss:mmm(24h)
-	20 或 120 (2)	ODBC 规范	yyyy-mm-dd hh:mi:ss(24h)
-	21 或 121 (2)	ODBC 规范（带毫秒）	yyyy-mm-dd hh:mi:ss.mmm(24h)
-	126 (4)	ISO8601	yyyy-mm-ddThh:mi:ss.mmm（无空格）
-	127(6, 7)	带时区 Z 的 ISO8601。	yyyy-mm-ddThh:mi:ss.mmmZ （无空格）
-	130 (1, 2)	回历 (5)	dd mon yyyy hh:mi:ss:mmmAM
-	131 (2)	回历 (5)	dd/mm/yy hh:mi:ss:mmmAM