SQL Server常用函数汇总

1.day(date)  

 用途：获取日期是所在月的几号 

 参数：date是一个可以解析为 time、date、smalldatetime、datetime、datetime2 或 datetimeoffset 值的表达式、列表达式、用户定义的变量或字符串文字

 返回值：一个整数，该整数表示指定的 date 是该月份的哪一天

 

 eg: 

day(getdate())  --获取当前时间是当前月份的哪一天

 扩展：

select CONVERT(VARCHAR(10),DATEADD(dd,-day(getdate())+1,getdate()),120)   --获取当月的开始日期
select CONVERT(VARCHAR(10),dateadd(dd,-day(getdate()),dateadd(m,1,getdate())),120)   --获取当月的结束日期

2.DATEFIRST 

 用途：设置一周是以星期几开始

 语法：SET DATEFIRST {Num}

 参数：num是一个数值， 指示一周的第一天的一个整数。 可以是下列值之一。

值	一周的第一天是
1	星期一
2	星期二
3	星期三
4	星期四
5	星期五
6	星期六
7 （默认值，美国英语）	星期日

 eg： 

 SET DATEFIRST 1 

 注释：

 (1)若要查看 SET DATEFIRST 的当前设置，请使用 @@DATEFIRST 函数。

    eg: 

SELECT @@DATEFIRST 

 (2)SET DATEFIRST 的设置是在执行或运行时设置，而不是在分析时设置。

 (3)指定 SET DATEFIRST 对 DATEDIFF 不起作用。 DATEDIFF 始终使用星期日作为每周的第一天，以确保函数是确定性的。

3.DATEPART()

 用途: DATEPART() 函数用于返回日期/时间的单独部分，比如年、月、日、小时、分钟等等

 语法：DATEPART(datepart,date)

 参数：date 参数是合法的日期表达式。datepart 参数可以是下列的值：

datepart	缩写
年	yy, yyyy
季度	qq, q
月	mm, m
年中的日	dy, y
日	dd, d
周	wk, ww
星期	dw, w
小时	hh
分钟	mi, n
秒	ss, s
毫秒	ms
微妙	mcs
纳秒	ns

 

 eg:

SET DATEFIRST 1       --设置一周的开始为星期一
select datepart(w,getdate())

 扩展：

SET DATEFIRST 1    --设置一周的开始为星期一
select CONVERT(VARCHAR(10),getdate()-datepart(w,getdate())+1,120)   --当前日前所在周的开始日期
 select CONVERT(VARCHAR(10),getdate()-datepart(w,getdate())+7 ,120)   --当前日前所在周的结束日期

 4.SQL中的两种数据转换方式

语法：CAST ( expression AS data_type [ ( length ) ] )

       CONVERT ( data_type [ ( length ) ] , expression [ , style ] )

参数：expression：任何有效的表达式。

       data_type ：目标数据类型。 这包括 xml、bigint 和 sql_variant。 不能使用别名数据类型。

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

       style：指定 CONVERT 函数如何转换 expression 的整数表达式。 如果 style为 NULL，则返回 NULL。 该范围是由 data_type 确定的。

返回值：转换为 data_type 的 expression。
注释：convert中data_type为日期时间类型时，style的集中类型如下表

不带世纪数位 (yy) (1)	带世纪数位 (yyyy)	标准	输入/输出 (3)
-	0 或100（1、2）	默认	mon dd yyyy hh:miAM（或 PM）
1	101	美国	1 = mm/dd/yy 101 = mm/dd/yyyy
2	102	ANSI	2 = yy.mm.dd 102 = yyyy.mm.dd
3	103	英国/法国	3 = dd/mm/yy 103 = dd/mm/yyyy
4	104	德国	4 = dd.mm.yy 104 = dd.mm.yyyy
5	105	意大利	5 = dd-mm-yy 105 = dd-mm-yyyy
6	106 (1)	-	6 = dd mon yy 106 = dd mon yyyy
7	107 (1)	-	7 = Mon dd, yy 107 = Mon dd, yyyy
8	108	-	hh:mi:ss
-	9 或109（1、2）	默认格式 + 毫秒	mon dd yyyy hh:mi:ss:mmmAM（或 PM）
10	110	美国	10 = mm-dd-yy 110 = mm-dd-yyyy
11	111	日本	11 = yy/mm/dd 111 = yyyy/mm/dd
12	112	ISO	12 = yymmdd 112 = 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（无空格）  注意 毫秒 (mmm) 的值为 0 时，不显示毫秒值。 例如，值“2012-11-07T18:26:20.000”显示为“2012-11-07T18:26:20”。
-	127(6, 7)	带时区 Z 的 ISO8601。	yyyy-mm-ddThh:mi:ss.mmmZ（无空格）  注意 毫秒 (mmm) 的值为 0 时，不显示毫秒值。 例如，值“2012-11-07T18:26:20.000”显示为“2012-11-07T18:26:20”。
-	130 (1,2)	回历 (5)	dd mon yyyy hh:mi:ss:mmmAM 在此样式中，mon 表示完整月份名称的多标记回历 unicode 表示形式。 该值在 SSMS 的默认 US 安装中将不会正确呈现。
-	131 (2)	回历 (5)	dd/mm/yyyy hh:mi:ss:mmmAM

eg:

SELECT 
   GETDATE() AS UnconvertedDateTime,
   CAST(GETDATE() AS nvarchar(30)) AS UsingCast,
   CONVERT(nvarchar(30), GETDATE(), 121) AS 'ODBC 规范'  ,
   CONVERT(nvarchar(30), GETDATE(), 121) AS 'ODBC 规范（带毫秒'  ;
GO  

 5.DATEDIFF()

语法：DATEDIFF(datepart,startdate,enddate)

  参数：startdate 和 enddate 参数是合法的日期表达式。

  注释：datepart 参数可以是下列的值：

datepart	缩写
年	yy, yyyy
季度	qq, q
月	mm, m
年中的日	dy, y
日	dd, d
周	wk, ww
星期	dw, w
小时	hh
分钟	mi, n
秒	ss, s
毫秒	ms
微妙	mcs
纳秒	ns