–以2013-12-10 12:56:55为例
–print convert(nvarchar(10),getdate(),120) => 2013-12-10
–print convert(nvarchar(20),getdate(),23) => 2013-12-10
–print convert(nvarchar(20),getdate(),20) => 2018-07-18 09:09:09
–print convert(varchar(100), getdate(), 112) => 20200809
–print convert(nvarchar(16),getdate(),120) => 2019-04-26 11:56
打印当前日期的年月
print DateName(year,GetDate())
print DateName(month,GetDate())
print DateName(month,DATEADD(month,1,GetDate()))
print DateName(month,DATEADD(month,-1,GetDate()))
print DATEPART(month,GetDate())
print DATEPART(year,GetDate())*
--2020-09-29 08:58:18
select convert(varchar(100), getdate(), 20)
--20200929085818
select replace(replace(replace(convert(varchar(100), getdate(), 20), '-', ''), ' ', ''), ':', '')
--202009290858
select left(replace(replace(replace(convert(varchar(100), getdate(), 20), '-', ''), ' ', ''), ':', ''), 12)
--2020-09-29 09:06:25.557
select convert(varchar(100), getdate(), 121)
--20200929090625557
select replace(replace(replace(replace(convert(varchar(100), getdate(), 121), '-', ''), ' ', ''), ':', ''),'.','')
----------------------------------
--09:12:55
select convert(varchar(100), getdate(), 8)
--09-29-20
select convert(varchar(100), getdate(), 10)
--20/09/29
select convert(varchar(100), getdate(), 11)
--200929
select convert(varchar(100), getdate(), 12)
--09:13:13
select convert(varchar(100), getdate(), 24)
--09:13:13:093
select convert(varchar(100), getdate(), 114)
--2020-09-29 09:13:13.093
select convert(varchar(100), getdate(), 121)
–当前日期+3天
PRINT CONVERT(VARCHAR(10),DATEADD(day,3,GETDATE()),120)
–还可以这样
–年
select datepart(YEAR,’2013-06-08′)
select datepart(yyyy,’2013-06-08′)
select datepart(yy,’2013-06-08′)
–月
select datepart(MONTH,’2013-06-08′)
select datepart(mm,’2013-06-08′)
select datepart(m,’2013-06-08′)
–日
select datepart(dd,’2013-06-08′)
–1年中的第多少天
select datepart(dy,’2013-06-08′)
–季度
select datepart(qq,’2013-06-08′)
–1年中的第多少周
select datepart(wk,’2013-06-08′)
–星期
select datepart(dw,’2013-06-08′) 会输出 7
select datepart(weekday, ‘2013-06-08’) 会输出 7
select datename(weekday, ‘2013-06-08’) 会输出 星期六
时间差
print datediff(year, '2020-05-01', getdate())
print datediff(month, '2020-05-01', getdate())
print datediff(day, '2020-05-01', getdate())
print datediff(hour, '2022-03-31', getdate())
print datediff(minute, convert(datetime,'2020-05-01 10:00:00'), getdate())
print datediff(second, convert(datetime,'2020-05-01 10:00:00'), getdate())
本周星期一 至 星期日
SELECT DATEADD(wk, DATEDIFF(wk,0,getdate()), 0) --本周星期一
SELECT DATEADD(wk, DATEDIFF(wk,0,getdate()), 1) --本周星期二
SELECT DATEADD(wk, DATEDIFF(wk,0,getdate()), 2) --本周星期三
SELECT DATEADD(wk, DATEDIFF(wk,0,getdate()), 3) --本周星期四
SELECT DATEADD(wk, DATEDIFF(wk,0,getdate()), 4) --本周星期五
SELECT DATEADD(wk, DATEDIFF(wk,0,getdate()), 5) --本周星期六
SELECT DATEADD(wk, DATEDIFF(wk,0,getdate()), 6) --本周星期日
上n周的星期一 至 星期日
--上1周星期一【1*7-1=6】 上1周星期日【1*7-7=0】
select convert(char(10),dateadd(dd, - DATEPART (weekday , getdate()+@@DATEFIRST -1)-6,getdate()),120)
select convert(char(10),dateadd(dd, - DATEPART (weekday , getdate()+@@DATEFIRST -1)-0,getdate()),120)
--上2周星期一【2*7-1=13】 上2周星期日【2*7-7=7】
select convert(char(10),dateadd(dd, - DATEPART (weekday , getdate()+@@DATEFIRST -1)-13,getdate()),120)
select convert(char(10),dateadd(dd, - DATEPART (weekday , getdate()+@@DATEFIRST -1)-7,getdate()),120)
--上3周星期一【3*7-1=20】 上3周星期日【3*7-7=14】
select convert(char(10),dateadd(dd, - DATEPART (weekday , getdate()+@@DATEFIRST -1)-20,getdate()),120)
select convert(char(10),dateadd(dd, - DATEPART (weekday , getdate()+@@DATEFIRST -1)-14,getdate()),120)
下n周星期一 至 周星期日
--下1周星期一【1*7+2=9】 下1周星期日【2*7+1=15】
SELECT DATEADD(D,(select 9-(select DATEPART(weekday,getdate()))), GETDATE());
SELECT DATEADD(D,(select 15-(select DATEPART(weekday,getdate()))), GETDATE());
--下2周星期一【2*7+2=16】 下2周星期日【3*7+1=22】
SELECT DATEADD(D,(select 16-(select DATEPART(weekday,getdate()))), GETDATE());
SELECT DATEADD(D,(select 22-(select DATEPART(weekday,getdate()))), GETDATE());
--下3周星期一【3*7+2=23】 下3周星期日【4*7+1=29】
SELECT DATEADD(D,(select 23-(select DATEPART(weekday,getdate()))), GETDATE());
SELECT DATEADD(D,(select 29-(select DATEPART(weekday,getdate()))), GETDATE());
--下4周星期一【4*7+2=30】 下4周星期日【5*7+1=36】
SELECT DATEADD(D,(select 30-(select DATEPART(weekday,getdate()))), GETDATE());
SELECT DATEADD(D,(select 36-(select DATEPART(weekday,getdate()))), GETDATE());
*
获取当前月第一天
--获取当前月第一天
select convert(nvarchar(10),dateadd(dd,-datepart(day,getdate())+1,getdate()),23)
select dateadd(mm, datediff(mm,0,getdate()), 0)
select dateadd(day, 1 - datepart(day, getdate()), getdate())
select convert(char(10),dateadd(dd,-day(getdate())+1,getdate()),111)
*
获取某个时间段内，所有“月份”
declare @begin datetime,@end datetime
set @begin='2015-2-6'
set @end='2015-12-2'
declare @months int
set @months=DATEDIFF(month,@begin,@end)
select convert(varchar(7),DATEADD(month,number,@begin) ,120) AS 月份
from master.dbo.spt_values
where type='p' AND number<=@months
获取某个时间段内，所有“日期”
SQL Pivot 行转列
DECLARE @days INT,
@date_start DATETIME = '2017-03-15',
@date_end DATETIME = '2017-04-13'
SET @days = DATEDIFF(DAY, @DATE_START, @DATE_END);
SELECT convert(char(10), DATEADD(dd, number, @DATE_START),112) AS yyyymmdd
FROM master.dbo.spt_values as spt
WHERE type = 'p'
AND number <= @days
--直接查询
SELECT convert(varchar(10), DATEADD(dd, number, '2017-03-15'),112) AS duty_date
FROM master.dbo.spt_values as spt
WHERE type = 'p' AND number <= 29直接输出日期，效果图如下
DECLARE @sql2 NVARCHAR(4000)
DECLARE @days INT,
@date_start DATETIME = '2020-02-01',
@date_end DATETIME = '2020-02-10'
SET @days = DATEDIFF(DAY, @DATE_START, @DATE_END);
SELECT @sql2 = ISNULL(@sql2 + ',', '') + '['+convert(varchar(100), duty_date, 112)+']' FROM (
SELECT convert(varchar(10), DATEADD(dd, number, @DATE_START),112) AS duty_date
FROM master.dbo.spt_values as spt
WHERE type = 'p' AND number <= @days
) tt
GROUP BY duty_date
PRINT @sql2
go
查询小时数据，按小时分组
-- 按小时产生连续的
SELECT
substring(convert(char(32),DATEADD(HH,number,CONCAT('2019-01-18',' ', '00:00')),120),1,16) AS GroupDay,type
FROM
master..spt_values
WHERE type = 'p' AND DATEDIFF(HH,DATEADD(HH,number,CONCAT('2019-01-18',' ', '00:00')),CONCAT('2019-01-18',' ', '23:00'))>=0
-- 按小时统计交易笔数
declare @paySdate nvarchar(10) = '2021-05-22'
declare @payEdate nvarchar(10) = '2021-05-23'
declare @paySTime nvarchar(10) = '00:00'
declare @payETime nvarchar(10) = '23:59'
select a.GroupDay, ISNULL(b.e,0) 'feeCount' from (
SELECT
substring(convert(char(32),DATEADD(HH,number,CONCAT(@paySdate,' ', @paySTime)),120),1,16) AS GroupDay,type
FROM
master..spt_values
WHERE
type = 'p' AND DATEDIFF(HH,DATEADD(HH,number,CONCAT(@paySdate,' ', @paySTime)),CONCAT(@payEdate,' ', @payETime))>=0
) a
left join (
select
convert(char(32),created_time,23) as d, datepart(hh,created_time) as h,
substring(convert(char(32),DATEADD(HH,datepart(hh,created_time),convert(char(32),created_time,23)),120),1,16) as st,
count(*) as e
from
trans_order
where 1=1
and created_time >= @paySdate and created_time<=@payEdate
and convert(char(8),created_time,108)>=@paySTime and convert(char(8),created_time,108)<=@payETime
group by convert(char(32),created_time,23),datepart(hh,created_time)) b
on b.st=a.GroupDay order by GroupDay
查询某个时间段内，工作日
declare @date_start datetime = '2023-03-01'
declare @date_end datetime = '2023-03-31'
declare @counter date = @date_start
create table temp (
calendar_date date,
is_workday int
)
while @counter <= @date_end
begin
--插入数据
insert into temp select @counter,case when datepart(weekday,@counter) = 1 or datepart(weekday,@counter) = 7 then 0 else 1 end
set @counter = dateadd(day,1,@counter)
end
--统计工作日天数
select sum(is_workday) as 工作日天数 from temp
--查询工作日日期
select calendar_date
,is_workday
,[week] =
case datepart(weekday,calendar_date)
when 1 then '星期日'
when 2 then '星期一'
when 3 then '星期二'
when 4 then '星期三'
when 5 then '星期四'
when 6 then '星期五'
when 7 then '星期六'
end
from temp
where is_workday = 1
--删除临时表
drop table temp
效果图
分钟数，转 x 小时 x 分钟【总时长转小时转分钟，总时间转换小时转换分钟】
例如：66分钟 = 1 小时 6 分钟
66分钟 = 01:06
66分钟 = 01:06:00
--66是总分钟数
--01:01
select CONVERT(varchar(5),DATEADD(MINUTE,66,0),108)
--66是总分钟数
--1小时，使用 SUBSTRING 字符串截取出【小时】数
select CONVERT(int,SUBSTRING(CONVERT(varchar(5),DATEADD(MINUTE,66,0),108),1,2)) as hour_time
--66是总分钟数
--6分钟，使用 SUBSTRING 字符串截取出【分钟】数
select CONVERT(int,SUBSTRING(CONVERT(varchar(5),DATEADD(MINUTE,66,0),108),4,5)) as minute_time
--90是总分钟数
--01:30:00
select CONVERT(varchar(12),DATEADD(MINUTE,90,0),108)
--90是总分钟数
--01:30
select SUBSTRING(CONVERT(varchar(12),DATEADD(MINUTE,90,0),108),1,5)
查询小时数据，按小时分组
select isnull(convert(varchar(2), sv.number) + '-' + convert(varchar(2), sv.number + 1), '合计') AS [时间段]
--,sum(xiaoshoushu) AS [销量]
--,sum(shixiao) AS [实销]
--,sum(chengben) as [成本]
--,(sum(shoujia)-sum(chengben)) as [毛利]
--,(SELECT sum(shixiao) FROM xiaoshoux c WHERE DATEPART(hh, c.xiaoshoutime) <= ISNULL(sv.number, 23) ) AS [累计实销]
from master..spt_values sv
--join xiaoshoux x on (sv.number = DATEPART(hh, x.xiaoshoutime))
where sv.type='p' and sv.number >= 0 and sv.number < 24
--and x.xiaoshoutime between '2013-07-01' and '2013-07-16'
group by sv.number
with rollup
查询当天的数据
-- 今天的所有数据
select * from trans_queue where DateDiff(dd,registration_time,getdate())=0
-- 昨天的所有数据
select * from trans_queue where DateDiff(dd,registration_time,getdate())=1
-- 7天内的所有数据
select * from trans_queue where DateDiff(dd,registration_time,getdate())<=7
-- 30天内的所有数据
select * from trans_queue where DateDiff(dd,registration_time,getdate())<=30
-- 本月的所有数据
select * from trans_queue where DateDiff(mm,registration_time,getdate())=0
-- 本年的所有数据
select * from trans_queue where DateDiff(yy,registration_time,getdate())=0
-- 今天的所有数据
select * from trans_queue where 1=1
and registration_time > convert(varchar(10),dateadd(day,-1,getdate()),23) + ' 23:59:59'
and registration_time < convert(varchar(10),dateadd(day,1,getdate()),23)
查询当天的数据（EF Core）
var list = dbcontext.users.Where(x => ((DbFunctions)null).DateDiffDay(x.created_time, DateTime.Now) == 0).ToList();
数据脱敏
--concat()、left()和right()字符串函数组合使用
--concat(str1,str2,…)：返回结果为连接参数产生的字符串
--left(str,len)：返回从字符串str 开始的len 最左字符
--right(str,len)：从字符串str 开始，返回最右len 字符
--电话号码脱敏
select phone as 脱敏前电话号码,concat(left(phone,3), '********') as 脱敏后电话号码 from sys_user
select phone as 脱敏前电话号码,concat(left(phone,3), '****', right(phone,4)) as 脱敏后电话号码 from sys_user
--身份证号码脱敏
select id_card as 未脱敏身份证, concat(left(id_card,6),'********',right(id_card,4)) as 脱敏后身份证号 from sys_user
--电话号码、身份证脱敏
select left(real_name,1) + replicate('*',len(real_name)-1) as 脱敏后姓名
,left(id_card,4) + '****' + substring(id_card,9,1) + '*' + right(id_card,1) as 脱敏后身份证号
from sys_user
/*
* 设置语言为英语
*/
set language N’English’ –设置语言为英语
select datename(weekday, ‘2013-06-08’) 会输出 Saturday
/*
* 设置语言为简体中文
*/
set language N’Simplified Chinese’ –设置语言为简体中文
select datename(weekday, ‘2013-06-08’) 会输出 星期六
/*
* 设置一周的第一天为星期一
*/
set datefirst 1 –设置一周的第一天为星期一(美国一周的第一天习惯为星期天)
select datepart(weekday, ‘2013-06-08’) 会输出 6
/*
* 设置一周的第一天为星期日
*/
set datefirst 7 –设置一周的第一天为星期日(美国一周的第一天习惯为星期天)
select datepart(weekday, ‘2013-06-08’) 会输出 7
/*
* 查询当前会话的 datefirst 设置值
*/
select @@datefirst
SELECT CONVERT(VARCHAR(10),GETDATE(),120) –2015-07-13
SELECT CONVERT(VARCHAR(10),GETDATE(),101) –07/13/2015
–按日分组：2013-01-01
select convert(nvarchar(10),CreateDate,120) as Times,ISNULL(sum(Unit),0.0) as Drinking from pdt_Out
group by convert(nvarchar(10),CreateDate,120)
go
–按月分组：2012-01
select DATEPART(month,CreateDate) as Times,sum(Unit) as Totals from pdt_Out
group by DATEPART(month,CreateDate)
go
–按年分组：2013
select DATEPART(year,CreateDate) as Times,sum(Unit) as Totals from pdt_Out
group by DATEPART(year,CreateDate)
go
–当前日期减一
SELECT DATEADD(dd,-1,GETDATE())
–当前日期减一（格式为：2018-01-01）
SELECT CONVERT(NVARCHAR(10),DATEADD(dd,-1,GETDATE()),120)