MSSQL Tips

随意记录的一些代码片段,方便自己存取

SQL Server 日期时间格式转换

开发软件的人经常也要接触到数据库,无论是做视图,还是做存储过程,都会接触到Select日期。

有时候根据不同的项目需求,选择出的日期有时候会有不同的格式要求,比如只取日期不取时间,比如计算日期间隔等。

我每次遇到这个问题,都要重新打开浏览器,重新搜索以便复制黏贴。

所以这次还是整理了下,这样不用每次都要重新去搜索了。

SQL Server2000中使用convert来取得datetime数据类型样式(全)

日期数据格式的处理,两个示例

CONVERT(varchar(16), 时间一, 20) 结果:2007-02-01 08:02/*时间一般为getdate()函数或数据表里的字段*/

CONVERT(varchar(10), 时间一, 23) 结果:2007-02-01 /*varchar(10)表示日期输出的格式,如果不够长会发生截取*/
语句及查询结果:

Select CONVERT(varchar(100), GETDATE(), 0): 05 16 2006 10:57AM
Select CONVERT(varchar(100), GETDATE(), 1): 05/16/06
Select CONVERT(varchar(100), GETDATE(), 2): 06.05.16
Select CONVERT(varchar(100), GETDATE(), 3): 16/05/06
Select CONVERT(varchar(100), GETDATE(), 4): 16.05.06
Select CONVERT(varchar(100), GETDATE(), 5): 16-05-06
Select CONVERT(varchar(100), GETDATE(), 6): 16 05 06
Select CONVERT(varchar(100), GETDATE(), 7): 05 16, 06
Select CONVERT(varchar(100), GETDATE(), 8): 10:57:46
Select CONVERT(varchar(100), GETDATE(), 9): 05 16 2006 10:57:46:827AM
Select CONVERT(varchar(100), GETDATE(), 10): 05-16-06
Select CONVERT(varchar(100), GETDATE(), 11): 06/05/16
Select CONVERT(varchar(100), GETDATE(), 12): 060516
Select CONVERT(varchar(100), GETDATE(), 13): 16 05 2006 10:57:46:937
Select CONVERT(varchar(100), GETDATE(), 14): 10:57:46:967
Select CONVERT(varchar(100), GETDATE(), 20): 2006-05-16 10:57:47
Select CONVERT(varchar(100), GETDATE(), 21): 2006-05-16 10:57:47.157
Select CONVERT(varchar(100), GETDATE(), 22): 05/16/06 10:57:47 AM
Select CONVERT(varchar(100), GETDATE(), 23): 2006-05-16
Select CONVERT(varchar(100), GETDATE(), 24): 10:57:47
Select CONVERT(varchar(100), GETDATE(), 25): 2006-05-16 10:57:47.250
Select CONVERT(varchar(100), GETDATE(), 100): 05 16 2006 10:57AM
Select CONVERT(varchar(100), GETDATE(), 101): 05/16/2006
Select CONVERT(varchar(100), GETDATE(), 102): 2006.05.16
Select CONVERT(varchar(100), GETDATE(), 103): 16/05/2006
Select CONVERT(varchar(100), GETDATE(), 104): 16.05.2006
Select CONVERT(varchar(100), GETDATE(), 105): 16-05-2006
Select CONVERT(varchar(100), GETDATE(), 106): 16 05 2006
Select CONVERT(varchar(100), GETDATE(), 107): 05 16, 2006
Select CONVERT(varchar(100), GETDATE(), 108): 10:57:49
Select CONVERT(varchar(100), GETDATE(), 109): 05 16 2006 10:57:49:437AM
Select CONVERT(varchar(100), GETDATE(), 110): 05-16-2006
Select CONVERT(varchar(100), GETDATE(), 111): 2006/05/16
Select CONVERT(varchar(100), GETDATE(), 112): 20060516
Select CONVERT(varchar(100), GETDATE(), 113): 16 05 2006 10:57:49:513
Select CONVERT(varchar(100), GETDATE(), 114): 10:57:49:547
Select CONVERT(varchar(100), GETDATE(), 120): 2006-05-16 10:57:49
Select CONVERT(varchar(100), GETDATE(), 121): 2006-05-16 10:57:49.700
Select CONVERT(varchar(100), GETDATE(), 126): 2006-05-16T10:57:49.827
Select CONVERT(varchar(100), GETDATE(), 130): 18 1427 10:57:49:907AM
Select CONVERT(varchar(100), GETDATE(), 131): 18/04/1427 10:57:49:920AM

说明:
使用 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)。

不带世纪数位 (yy) 带世纪数位 (yyyy) 
标准 
输入/输出** 
– 0 或 100 (*) 默认值 mon dd yyyy hh:miAM(或 PM) 
1 101 美国 mm/dd/yyyy 
2 102 ANSI yy.mm.dd 
3 103 英国/法国 dd/mm/yy 
4 104 德国 dd.mm.yy 
5 105 意大利 dd-mm-yy 
6 106 – dd mon yy 
7 107 – mon dd, yy 
8 108 – hh:mm:ss 
– 9 或 109 (*) 默认值 + 毫秒 mon dd yyyy hh:mi:ss:mmmAM(或 PM) 
10 110 美国 mm-dd-yy 
11 111 日本 yy/mm/dd 
12 112 ISO yymmdd 
– 13 或 113 (*) 欧洲默认值 + 毫秒 dd mon yyyy hh:mm:ss:mmm(24h) 
14 114 – hh:mi:ss:mmm(24h) 
– 20 或 120 (*) ODBC 规范 yyyy-mm-dd hh:mm:ss[.fff] 
– 21 或 121 (*) ODBC 规范(带毫秒) yyyy-mm-dd hh:mm:ss[.fff] 
– 126(***) ISO8601 yyyy-mm-dd Thh:mm:ss.mmm(不含空格) 
– 130* Hijri**** dd mon yyyy hh:mi:ss:mmmAM 
– 131* Hijri**** dd/mm/yy hh:mi:ss:mmmAM 


* 默认值(style 0 或 100、9 或 109、13 或 113、20 或 120、21 或 121)始终返回世纪数位 (yyyy)。
** 当转换为 datetime时输入;当转换为字符数据时输出。
*** 专门用于 XML。对于从 datetime或 smalldatetime 到 character 数据的转换,输出格式如表中所示。对于从 float、money 或 smallmoney 到 character 数据的转换,输出等同于 style 2。对于从 real 到 character 数据的转换,输出等同于 style 1。
****Hijri 是具有几种变化形式的日历系统,Microsoft® SQL Server™ 2000 使用其中的科威特算法。

 

重要 默认情况下,SQL Server 根据截止年份 2049 解释两位数字的年份。即,两位数字的年份 49 被解释为 2049,而两位数字的年份 50 被解释为 1950。许多客户端应用程序(例如那些基于 OLE 自动化对象的客户端应用程序)都使用 2030 作为截止年份。SQL Server 提供一个配置选项("两位数字的截止年份"),借以更改 SQL Server 所使用的截止年份并对日期进行一致性处理。然而最安全的办法是指定四位数字年份。


当从 smalldatetime 转换为字符数据时,包含秒或毫秒的样式将在这些位置上显示零。当从 datetime 或 smalldatetime 值进行转换时,可以通过使用适当的 char 或 varchar 数据类型长度来截断不需要的日期部分。

SQL Server Rand函数 (随机数)

随机数: RAND

返回从 0 到 1 之间的随机 float 值。

语法: RAND ( [ seed ] )
seed
提供种子值的整数表达式(tinyint、smallint 或 int)。如果未指定 seed,则 Microsoft SQL Server 数据库引擎 随机分配种子值。对于指定的种子值,返回的结果始终相同。

随机数是我们经常会用到的,几乎所有的语言都支持产生随机数。而且函数都差不多。例如在VBA和VB中也是用RAND,在C#中用RANDOM等等

--由于没有设置seed值,所以每次Rand()出的值都是不同的随机Float
select RAND()
select RAND()
select RAND()

--指定seed数值后,产生的数值固定
select RAND(123)
select RAND(123)

mssql_rand

mssql_checksum

SQL Server CheckSum函数 (校验和值)

指定对表的所有列进行计算。如果有任一列是非可比数据类型,则 CHECKSUM 返回错误。
非可比数据类型有 text、ntext、image、XML 和 cursor,还包括以上述任一类型作为基类型的 sql_variant。

expression 
除非可比数据类型之外的任何类型的表达式。
返回值是int

CHECKSUM 对其参数列表计算一个称为校验和的哈希值。此哈希值用于生成哈希索引。如果 CHECKSUM 的参数为列,并且对计算的 CHECKSUM 值生成索引,则结果是一个哈希索引。它可用于对列进行等价搜索。

CHECKSUM 满足哈希函数的下列属性:在使用等于 (=) 运算符比较时,如果两个列表的相应元素具有相同类型且相等,则在任何两个表达式列表上应用的 CHECKSUM 将返回同一值。对于该定义,指定类型的 Null 值被作为相等进行比较。如果表达式列表中的某个值发生更改,则列表的校验和通常也会更改。但只在极少数情况下,校验和会保持不变。因此,我们不推荐使用 CHECKSUM 来检测值是否更改,除非应用程序可以容忍偶尔丢失更改。请考虑改用 HashBytes。指定 MD5 哈希算法时,HashBytes 为两个不同输入返回相同结果的可能性比 CHECKSUM 小得多。

表达式的顺序影响 CHECKSUM 的结果值。用于 CHECKSUM(*) 的列顺序是表或视图定义中指定的列顺序。其中包括计算列。

--校验固定字符和的哈希值
select CHECKSUM('abcdef')

--校验GUID和的哈希值
select CHECKSUM(NEWID())

mssql_checksum

mssql_newid

SQL Server NewID函数 (生成GUID)

SQL Server的主键需要唯一性,以便数据的检索。

一般使用36位的GUID或者自增长的Integer类型数字。

GUID是一个32位数字字符+4个'-'符号的,总共36位字符串,而且它不会重复。

从存储的空间节约来看,数字型占用更少的位数,而GUID占用的更多。

但是从移植性和全局唯一性来看,我更倾向于GUID(uniqueidentifier 类型),因为它不会重复。

在SQL SERVER中,使用内置函数NewID()来生成GUID字符串,如下:

select newid()

mssql_newid如图,生成了3个SQL SERVER GUID

同步MSSQL数据

特定条件下,同步两个SQL Server数据库数据

在同时使用多个SQL SERVER数据库的时候,经常需要两个数据库之间进行同步工作。

同步可以由SQL SERVER通过作业来自动完成,如下逻辑代码供参考:

--	INSERT INTO [172.29.11.212].FlexSFS.dbo.tbWorkOrder 
--	select * from tbWorkOrder where DateCreated>@THEDATE AND CodeWorkorder NOT IN 
--	(SELECT CodeWorkorder FROM [172.29.11.212].FlexSFS.dbo.tbWorkOrder WHERE DateCreated>@THEDATE)	
	
	--INSERT INTO tbPackage 
	--select * from [172.29.11.212].FlexSFS.dbo.tbPackage where DateCreated>@THEDATE AND CodePackage NOT IN 
	--(SELECT CodePackage FROM tbPackage WHERE DateCreated>@THEDATE)	
	
--	INSERT INTO [172.29.11.212].FlexSFS.dbo.tbPackage 
--	select * from tbPackage where DateCreated>@THEDATE AND CodePackage NOT IN 
--	(SELECT CodePackage FROM [172.29.11.212].FlexSFS.dbo.tbPackage WHERE DateCreated>@THEDATE)		
	
	--INSERT INTO tbHandOver 
	--select * from [172.29.11.212].FlexSFS.dbo.tbHandOver where DateCreated>@THEDATE AND CodeHandOver NOT IN 
	--(SELECT CodeHandOver FROM tbHandOver WHERE DateCreated>@THEDATE)	

--	INSERT INTO [172.29.11.212].FlexSFS.dbo.tbHandOver 
--	select * from tbHandOver where DateCreated>@THEDATE AND CodeHandOver NOT IN 
--	(SELECT CodeHandOver FROM [172.29.11.212].FlexSFS.dbo.tbHandOver WHERE DateCreated>@THEDATE)
	
	--INSERT INTO tbProduct 
	--select * from [172.29.11.212].FlexSFS.dbo.tbProduct where TimeCreated>@THEDATE AND CodeProduct NOT IN 
	--(SELECT CodeProduct FROM tbProduct WHERE TimeCreated>@THEDATE)	

--	INSERT INTO [172.29.11.212].FlexSFS.dbo.tbProduct 
--	select * from tbProduct where TimeCreated>@THEDATE AND CodeProduct NOT IN 
--	(SELECT CodeProduct FROM [172.29.11.212].FlexSFS.dbo.tbProduct WHERE TimeCreated>@THEDATE)	

 

同步MSSQL数据

MSSQL IsNull

SQL Server IsNull 去除NULL函数

在使用MS SQL Server查询字段值的时候,经常会遇到空值字段或者无值字段。

即 字段内容 = '' 或者字段内容 = NULL

A = NULL
B = ''

但是,它们的含义是不一样的,在查询的时候一定要注意。

好在MSSQL提供了一个内置函数ISNULL,利用它可以筛选出NULL值字段

-- 删除所有为空值('')或者为NULL的数据

delete from [TableXXX]
where IsNull(Value,'') = ''

recommand

//字段Line中有空值,有NULL值,还有其他有效值,如何排除?

mssql_isnull_01

//通过ISNULL函数,凡是NULL值则判定为'', 然后排除<>'' 即可mssql_isnull_02