Convert、Cast、Case When Else End、DateAdd实际运用

最新推荐文章于 2021-07-14 14:33:33 发布

原创最新推荐文章于 2021-07-14 14:33:33 发布 · 712 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#sql server #microsoft #join #date #存储 #float

SQL Server、SQL 语句专栏收录该内容

6 篇文章

订阅专栏

本文介绍了一种在SQLServer中根据当前时间查询康休中心班次的方法，通过使用复杂的SQL语句结合CAST、CONVERT及DATEADD函数实现跨天班次的准确匹配。

这个项目是一个康休中心项目的模块里的某个功能：

涉及的表：tb班次表

包括的字段：

1 fstrID nvarchar 50 0
0 fstr班次ID nvarchar 50 1
0 fstrStoreID nvarchar 10 1
0 fstr班次开始类型 tinyint 1 1
0 fstr班次开始时间 nvarchar 50 1
0 fstr班次开始小时 tinyint 1 1
0 fstr班次开始分钟 tinyint 1 1
0 fstr班次结束类型 tinyint 1 1
0 fstr班次结束时间 nvarchar 50 1
0 fstr班次结束小时 tinyint 1 1
0 fstr班次结束分钟 tinyint 1 1

数据如下：

tb班次表数据

fstrID fstr班次ID fstrStoreID fstr班次开始类型 fstr班次开始时间 fstr班次开始小时 fstr班次开始分钟 fstr班次结束类型 fstr班次结束时间 fstr班次结束小时 fstr班次结束分钟
1 0 1 0 11:00 44 58 1 10:59 43 117
2 1 1 0 11:00 44 58 0 23:00 56 58
3 2 1 0 14:00 47 58 1 1:00 34 58
4 3 1 0 16:00 49 58 1 6:00 39 58

还有一个代码表：CodeCellection，存储日期等的代码，小时跟分钟都存储在代码表里，

包括字段：

1 CodeID int 4 0
0 CodeType nvarchar 100 1
0 CodeName nvarchar 200 1
0 CodeParentID int 4 1
0 CodeDescription nvarchar 500 1
0 CodeOrder tinyint 1 1

tb班次表涉及到小时、分的都只存储的是CodeCollection的CodeID字段值

语句功能描述：

康休中心包括4班，这里根据当前时间选择哪一班，这个是康休中心的人搞的需求，所以设计的不健全，
所以我们要判断它班次结束类型的1代表的是当天还是0代表的是当天，就产生了2条语句，最终是为了
取到符合的班次，这里逻辑不再做过多的介绍，主要看几个SQL Server函数的用法，我这里写的只是我
用到的，不是所有的都介绍：
Convert、Cast转换类型函数：
将某种数据类型的表达式显式转换为另一种数据类型。CAST 和 CONVERT 提供相似的功能。

语法

使用 CAST：

CAST ( expression AS data_type )

使用 CONVERT：

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

参数

expression

是任何有效的 Microsoft® SQL Server™ 表达式。有关更多信息，请参见表达式。

data_type

目标系统所提供的数据类型，包括 bigint 和 sql_variant。不能使用用户定义的数据类型。有关可用的数据类型的更多信息，请参见数据类型。

length

nchar、nvarchar、char、varchar、binary 或 varbinary 数据类型的可选参数。

style

日期格式样式，借以将 datetime 或 smalldatetime 数据转换为字符数据（nchar、nvarchar、char、varchar、nchar 或 nvarchar 数据类型）；或者字符串格式样式，借以将 float、real、money 或 smallmoney 数据转换为字符数据（nchar、nvarchar、char、varchar、nchar 或 nvarchar 数据类型）。

SQL Server 支持使用科威特算法的阿拉伯样式中的数据格式。

在表中，左侧的两列表示将 datetime 或 smalldatetime 转换为字符数据的 style 值。给 style 值加 100，可获得包括世纪数位的四位年份 (yyyy)。

DATEADD

在向指定日期加上一段时间的基础上，返回新的 datetime 值。

语法

DATEADD ( datepart , number, date )

参数

datepart

是规定应向日期的哪一部分返回新值的参数。下表列出了 Microsoft® SQL Server™ 识别的日期部分和缩写。

日期部分缩写
Year yy, yyyy
quarter qq, q
Month mm, m
dayofyear dy, y
Day dd, d
Week wk, ww
Hour hh
minute mi, n
second ss, s
millisecond ms

number

是用来增加 datepart 的值。如果指定一个不是整数的值，则将废弃此值的小数部分。例如，如果为 datepart 指定 day，为 number 指定 1.75，则 date 将增加 1。

date

是返回 datetime 或 smalldatetime 值或日期格式字符串的表达式。有关指定日期的更多信息，请参见 datetime 和 smalldatetime。

如果您只指定年份的最后两位数字，则小于或等于"两位数年份截止期"配置选项的值的最后两位数字的数字所在世纪与截止年所在世纪相同。大于该选项的值的最后两位数字的数字所在世纪为截止年所在世纪的前一个世纪。例如，如果 two digit year cutoff 为 2049（默认），则 49 被解释为 2049，2050 被解释为 1950。为避免模糊，请使用四位数的年份。

返回类型

返回 datetime，但如果 date 参数是 smalldatetime，返回 smalldatetime。

Case When else end 不再做过多解释，一看语句就会明白的

涉及到2个表

我当时根据查询结果判断，如果查询的recordCount=0 则就用另外一条语句，也就是说将1代表当天
换成0代表当天，或者将0代表当天换成1代表当天

tb班次表数据
fstrID	fstr班次ID	fstrStoreID	fstr班次开始类型	fstr班次开始时间	fstr班次开始小时	fstr班次开始分钟	fstr班次结束类型	fstr班次结束时间	fstr班次结束小时	fstr班次结束分钟
1	0	1	0	11:00	44	58	1	10:59	43	117
2	1	1	0	11:00	44	58	0	23:00	56	58
3	2	1	0	14:00	47	58	1	1:00	34	58
4	3	1	0	16:00	49	58	1	6:00	39	58

日期部分	缩写
Year	yy, yyyy
quarter	qq, q
Month	mm, m
dayofyear	dy, y
Day	dd, d
Week	wk, ww
Hour	hh
minute	mi, n
second	ss, s
millisecond	ms

--1代表当天
select tb班次.*,a.codename as fstr开始分钟,b.codename as fstr开始小时,c.codename as fstr结束分钟,d.codename as fstr结束小时 from tb班次 join codecollection a on (tb班次.fstr班次开始分钟 = a.codeid) join codecollection b on (tb班次.fstr班次开始小时 = b.codeid) join codecollection c on (tb班次.fstr班次结束分钟 = c.codeid) join codecollection d on (tb班次.fstr班次结束小时 = d.codeid)
where getdate() between
((case when fstr班次结束类型=1 then
   (dateadd(mi,cast(a.codename as tinyint),dateadd(hh,cast(b.codename as tinyint),cast(convert(varchar(10),dateadd(dd,-1,getdate()),101) as datetime))))
      else
(dateadd(mi,cast(a.codename as tinyint),dateadd(hh,cast(b.codename as tinyint),cast(convert(varchar(10),getdate(),101) as datetime))))
end
) )
  and
((dateadd(mi,cast(c.codename as tinyint),dateadd(hh,cast(d.codename as tinyint),cast(convert(varchar(10),getdate(),101) as datetime)))))

--0代表当天
select tb班次.*,a.codename as fstr开始分钟,b.codename as fstr开始小时,c.codename as fstr结束分钟,d.codename as fstr结束小时 from tb班次 join codecollection a on (tb班次.fstr班次开始分钟 = a.codeid) join codecollection b on (tb班次.fstr班次开始小时 = b.codeid) join codecollection c on (tb班次.fstr班次结束分钟 = c.codeid) join codecollection d on (tb班次.fstr班次结束小时 = d.codeid)
where getdate() between
(dateadd(mi,cast(a.codename as tinyint),dateadd(hh,cast(b.codename as tinyint),cast(convert(varchar(10),getdate(),101) as datetime))))
and
((case when fstr班次结束类型=1 then
   (dateadd(mi,cast(c.codename as tinyint),dateadd(hh,cast(d.codename as tinyint),cast(convert(varchar(10),dateadd(dd,1,getdate()),101) as datetime))))
      else
(dateadd(mi,cast(c.codename as tinyint),dateadd(hh,cast(d.codename as tinyint),cast(convert(varchar(10),dateadd(dd,1,getdate()),101) as datetime))))
end
) )