文章目录
常用的函数
1.convert函数和cast函数
cast函数:
将一种数据类型的表达式转换为另一种数据类型的表达式。
表达式:
CAST(
任何可以转化为数据类型的表达式
AS
系统提供的目标数据类型。 该类型必须为基元(标量)类型)
注明:
所谓标量数据类型,指的是值只有一个。例如,
整数类型、字符类型、枚举类型、小数类型、布尔类型
convert函数
将一种数据类型的表达式转换为另一种数据类型的表达式
。
CONVERT(
目标数据类型(目标数据类型的长度),
要转换的数据类型,是由该表达式决定的,
[可选字段]当将时间类型转化为字符类型的时候
输出字符类型的形式style
)
以下是输出字符串类型的形式:
两者比较
2.3 CAST和CONVERT比较
(1)cast一般更容易使用,convert的优点是可以格式化日期和数值;
(2)convert一般用于日期和时间值,小数之间转换,cast一般用于小数转数值和字符型;
(3)converk显示转换,cast是强制转换;
引用自:
【Sqlserver系列】CAST和CONVERT
2.ISNULL函数和IFNULL函数
首先,这是两个不同数据库中的函数。。。。
isnull是sqlserver的函数
ifnull是mysql中的函数
isnull
isnull(参数1,参数2)
解释:
这个函数就是判断给定的参数1是否为null的,如果参数1是null,那么就返回参数2;如果参数1不为null,那么就返回参数1.
注意:
1.参数1和参数2的数据类型需要保持一致。
2.写入’'也就是单引号不是null。
如下示例:
ifnull
ifnull(参数1,参数2)
解释:
也是一样的,如果参数1为null,那么就返回参数2;
如果参数1不为null,则返回参数1;
参考:
https://www.cnblogs.com/Rainbow-G/p/4080022.html
3.COALESCE函数
coalesce(参数1,参数2,参数3,参数4…)
解释:
这个函数返回参数中第一个非空参数。
经常使用在查询出来的表信息中,有些字段值为空的情况,那么使用这个,将为空的字段值改为另一个自定义的值,例如:
如果有一个表示student,含有一个字段s_hobby,
当这个s_hobby的值为null的时候,
我们将它设置为:暂时不晓得
select coalesce(s_hobby,N'暂时不晓得') from student;
值得注意的是,跟sqlserver中的isnull函数是一样的,都是对于' '判断为非null。
参考:
COALESCE字符函数的用法
4.日期函数
名称和缩写
以下是日期对应的单位名称和缩写:
日期部分 | 缩写 |
---|---|
year(年) | yy, yyyy |
quarter | (季度) q,qq |
month(月份) | m,mm |
dayofyear(年中的日) | y,dy |
day (日) | d, dd |
week (周) | wk, ww |
hour (时) | hh |
minute(分) | n, mi |
second (秒) | s, ss |
millisecond (毫秒) | ms |
microsecond(微秒) | mcs |
nanosecond(纳秒) | ns |
格式
给定日期的格式一般都是采用如下形式:
使用yyyy-mm-dd hh:mm:ss格式:
1.getdate使用
--1.getdate系统当前时间
SELECT GETDATE() AS N'系统当前日期时间'; --2021-07-08 17:39:43.693
2.dateadd使用
--2.dateadd 在指定日期或者时间基础上往后或者往前加上一段指定部分时间,返回新日期时间值
SELECT DATEADD(DAY,-2,GETDATE()) AS N'系统当前日期天数减两天'; --2021-07-06 17:39:43.693
SELECT DATEADD(DAY,2,GETDATE()) AS N'系统当前日期天数加两天'; --2021-07-10 17:39:43.693
SELECT DATEADD(DAYOFYEAR,-2,GETDATE()) AS N'同样获取当前时间的天数减两天'; --2021-07-06 17:39:43.693
SELECT DATEADD(DAYOFYEAR,2,GETDATE()) AS N'同样获取当前时间的天数加两天'; --2021-07-10 17:39:43.693
SELECT DATEADD(WEEK,-1,GETDATE()) AS N'系统当前日期周数减一周'; --2021-07-01 17:39:43.693
SELECT DATEADD(WEEK,1,GETDATE()) AS N'系统当前日期周数加一周'; --2021-07-15 17:39:43.693
SELECT DATEADD(HOUR,2,GETDATE()) AS N'系统当前时间小时数加两小时'; --2021-07-08 19:39:43.693
SELECT DATEADD(HOUR,-2,GETDATE()) AS N'系统当前时间小时数减两小时'; --2021-07-08 15:39:43.693
SELECT DATEADD(MINUTE,10,GETDATE()) AS N'系统当前时间分钟数加十分钟'; --2021-07-08 17:49:43.693
SELECT DATEADD(MINUTE,-10,GETDATE()) AS N'系统当前时间分钟数减十分钟'; --2021-07-08 17:29:43.693
SELECT DATEADD(SECOND,20,GETDATE()) AS N'系统当前时间秒数加二十秒'; --2021-07-08 17:40:03.693
SELECT DATEADD(SECOND,-20,GETDATE()) AS N'系统当前时间秒数减二十秒'; --2021-07-08 17:40:03.693
SELECT GETDATE() AS N'系统当前日期时间'; --2021-07-08 17:39:43.693
SELECT DATEADD(MILLISECOND,30,GETDATE()) AS N'系统当前时间毫秒数加三十秒'; --2021-07-08 17:39:43.723
SELECT DATEADD(MILLISECOND,-30,GETDATE()) AS N'系统当前时间毫秒数减三十秒'; --2021-07-08 17:39:43.723
SELECT DATEADD(MONTH,2,GETDATE()) AS N'系统当前时间月份加两个月'; --2021-09-08 17:39:43.693
SELECT DATEADD(MONTH,-2,GETDATE()) AS N'系统当前时间月份减两个月'; --2021-05-08 17:39:43.693
SELECT DATEADD(QUARTER,1,GETDATE()) AS N'系统当前日期加上一个季度'; --2021-04-08 17:39:43.693
SELECT DATEADD(QUARTER,-1,GETDATE()) AS N'系统当期日期减去一个季度'; --2021-04-08 17:39:43.693
SELECT DATEADD(YEAR,1,GETDATE()) AS N'系统当前年份加上一年'; --2022-07-08 17:39:43.693
SELECT DATEADD(YEAR,-1,GETDATE()) AS N'系统当前年份减去一年'; --2020-07-08 17:39:43.693
注意点:
1.这个函数的格式是:dateadd(partdate,partdate的数量,给定的时间);
2.这个函数的第一个参数partdate是表示给定的日期时间的部分:年,月,日,时,分,秒,毫秒;
注意不能获取微妙和纳米秒!
3.第二个参数partdate的数量,表示第一个参数给定日期时间单位的数量。
其中,第一个参数除了能够写完整的英文单词之外,还可以写缩写,如下:
3.day,month,year使用
这个三个函数是获取给定时间的部分,分贝为年、月、日;
SELECT YEAR(GETDATE()) AS N'获取当前时间的年份';
SELECT MONTH(GETDATE()) AS N'获取当前时间的月份';
SELECT DAY(GETDATE()) AS N'获取当前日期的号数';
参考:
SQL 日期函数 day() 、month()、year() 各种使用方法
4.datepart使用
同样是获取日期时间的一部分,但是这个获取的范围更广;
其表达式为:
DATEPART(datepart,date)
其中:
第一个参数是指定要获取日期的某部分;
第二个参数是指定日期时间;
SELECT DATEPART(YEAR,GETDATE()) AS N'获取当前时间的年份';
SELECT DATEPART(MONTH,GETDATE()) AS N'获取当前时间的月份';
SELECT DATEPART(DAY,GETDATE()) AS N'获取当前时间的日';
SELECT DATEPART(dw,GETDATE()-1) AS N'获取当前时间日期的星期几?'
SELECT DATEPART(WK,GETDATE())-1 AS N'获取当前时间日期是第几周?';
SELECT DATEPART(dy,GETDATE()) AS N'获取当前时间日期的第多少天?'
SELECT DATEPART(dd,GETDATE()) AS N'获取当前时间日期的号数'
SELECT DATEPART(hh,GETDATE()) AS N'获取当前时间日期的时?'
SELECT DATEPART(mi,GETDATE()) AS N'获取当前时间日期的分?'
SELECT DATEPART(ss,GETDATE()) AS N'获取当前时间日期的秒?'
SELECT DATEPART(ms,GETDATE()) AS N'获取当前时间日期的毫秒?'
SELECT DATEPART(ms,GETDATE()) AS N'获取当前时间日期的微秒?'
SELECT DATEPART(mcs,GETDATE()) AS N'获取当前时间日期的纳秒?'
注意这个跟dateadd比较的话,能够获取到某部分的范围更广,因为这个可以获取到微妙和纳秒;
还有一点需要注意,就是此处获取的周数和星期数是有差别的。。。都差了一。。。。。
以下是差别处:
至于为啥,看到网上不少人说,是因为在国外是将星期天当做周一,但是国内是将周一作为周一的。
5.datediff使用
此函数是返回指定的日期时间的一部分中的差;
其表达式为:
DATEDIFF(datepart,startdate,enddate)
其中:
第一个参数:指定求日期时间的某部分
第二个参数:指定求差的减数;
第三个参数:指定求差的被减数;
也就是:
enddate-startdate
SELECT DATEDIFF(YEAR,GETDATE(),DATEADD(YEAR,2,GETDATE())) AS N'获取两个时间的年份差';
SELECT DATEDIFF(MONTH,GETDATE(),DATEADD(MONTH,2,GETDATE())) AS N'获取两个时间的月份差';
SELECT DATEDIFF(MONTH,GETDATE(),DATEADD(MONTH,5,GETDATE())) AS N'获取两个时间的天数差';
SELECT DATEDIFF(MONTH,GETDATE(),DATEADD(MONTH,10,GETDATE())) AS N'获取两个时间的分钟数差';
注意:
这个同样使用毫秒,微秒,纳秒;
6.datename使用
此函数同样是返回日期时间的一部分,但是数据类型是以字符串的数据类型返回,如下可以看出:
declare @time varchar;
select @time=DATENAME(YEAR,GETDATE());
print @time;
SELECT DATENAME(YEAR,GETDATE()) AS N'获取当前时间的年份';
SELECT DATENAME(MONTH,GETDATE()) AS N'获取当前时间的月份';
SELECT DATENAME(DAY,GETDATE()) AS N'获取当前时间的日';
SELECT DATENAME(dw,GETDATE()) AS N'获取当前时间日期的星期几?'
SELECT DATENAME(dy,GETDATE()) AS N'获取当前时间日期的第多少天?'
SELECT DATENAME(dd,GETDATE()) AS N'获取当前时间日期的号数'
SELECT DATENAME(hh,GETDATE()) AS N'获取当前时间日期的时?'
SELECT DATENAME(mi,GETDATE()) AS N'获取当前时间日期的分?'
SELECT DATENAME(ss,GETDATE()) AS N'获取当前时间日期的秒?'
SELECT DATENAME(ms,GETDATE()) AS N'获取当前时间日期的毫秒?'
SELECT DATENAME(ms,GETDATE()) AS N'获取当前时间日期的微秒?'
SELECT DATENAME(mcs,GETDATE()) AS N'获取当前时间日期的纳秒?'
常用获取时间
--上周周一
SELECT DATEADD(WK,DATEDIFF(wk,0,GETDATE()),-7);
--上周周天
SELECT DATEADD(wk, DATEDIFF(wk,0,getdate()),-1)
--.本周周一
SELECT DATEADD(wk, DATEDIFF(wk,0,getdate()),0)
--.本周周天
select dateadd(wk,datediff(wk,0,getdate()),6)
--下周周一
select dateadd(wk,datediff(wk,0,getdate()),7)
--下周周天
select dateadd(wk,datediff(wk,0,getdate()),13)
--本月第一天
SELECT DATEADD(mm, DATEDIFF(mm,0,getdate()), 0)
--本月最后一天
SELECT DATEADD(ms,-3,DATEADD(mm, DATEDIFF(m,0,getdate())+1, 0))
--本周星期一
SELECT DATEADD(wk, DATEDIFF(wk,0,getdate()), 0)
--本周星期天
select DATEADD(wk,datediff(wk,0,getdate()),6)
--本年第一天 SELECT DATEADD(yy, DATEDIFF(yy,0,getdate()), 0)
--本年最后一天
SELECT DATEADD(ms,-3,DATEADD(yy, DATEDIFF(yy,0,getdate())+1, 0))
--本季度第一天
SELECT DATEADD(qq, DATEDIFF(qq,0,getdate()), 0)
--当天的半夜
SELECT DATEADD(DD,DATEDIFF(DD,0,GETDATE()),0)
--上个月的最后一天
SELECT DATEADD(MS,-3,DATEADD(MM,DATEDIFF(MM,0,GETDATE()),0))
--去年的最后一天
SELECT DATEADD(MS,-3,DATEADD(YY,DATEDIFF(YY,0,GETDATE()),0))
--本月的第一个星期一
SELECT DATEADD(wk,
DATEDIFF(wk,0,dateadd(dd,6-datepart(day,getdate()),getdate())), 0)
老实说上面的对于datediff的使用方式,跟之前定义的不太一样。。。。。暂时不明白为啥会得出正确的结果。。。