sql server语句中日期时间格式化查询
今天在做会员管理系统搜索时,我发现以前的搜索时间方式不太科学,效率也不是太高.由其是在查询指定的时间相等的时候,我在数据库中都存这样的时间格式"2007-5-22 14:32:12"当我们在查询2007-5-22是否与它相等,结果是不相等的.
所我们都喜欢找其它途径来解决这个问题。但是我发现我们的方法都不是太好，因为我们有的时候对sql sever
的语句特性还了解的不够彻底。所以我查询了查sql server有帮助,终于发现个好东西,可以解决这个问题.
CONVERT 函数,可以解决这个问题,能将2007-5-22 14:32:12这样的时间在查询或where 子句中格式化为:2007-5-22;
如下:
我将查询用户的注册时间 UserRegisterTime
- select * from [user] where UserActive=0 and replace(CONVERT(char(10),UserRegisterTime,111),'/','-')='2007-05-22' order by UserRegisterTime desc
在这其中关键的一句
- replace(CONVERT(char(10),UserRegisterTime,111),'/','-');
为什么我要replace将convert中的"/"符,换成"-"呢.是因为convert在这里格式化出来的时间是:2007/05/22
还要特别说明一点:convert格式化出为的时间都是带有前有0的格式,比如2007-1-1 12:12:31结果为:
2007-01-01,所以你在查询前务必要将你的日期中的月和日中小于10（不包括10）的数字加上前导0,
如：1就应该为01
此涵数的具体用法如下:
转换函数
当 Microsoft® SQL Server? 2000 不自动执行不同数据类型的表达式转换时，可使用转换函数 CAST 和 CONVERT 进行转换。这些转换函数还可用来获得各种特殊的数据格式。这两个转换函数都可用于选择列表、Where 子句和允许使用表达式的任何地方。
如果希望 Transact-SQL 程序代码遵从 SQL-92 标准，请使用 CAST 而不要使用 CONVERT。如果要利用 CONVERT 中的样式功能，请使用 CONVERT 而不要使用 CAST。
使用 CAST 或 CONVERT 时，需要两条信息：
要转换的表达式（例如，销售报告要求销售数据从货币型数据转换为字符型数据）。
要将所给表达式转换到的数据类型，例如，varchar 或 SQL Server 提供的任何其它数据类型。
除非将被转换的值存储起来，否则转换仅在 CAST 或 CONVERT 函数的作用期内有效。
在下面的示例中，第一个 Select 语句中使用 CAST，第二个 Select 语句中使用 CONVERT，将 title 列转换为 char(50) 列，以使结果更可读：
- USE pubs
- Select CAST(title AS char(50)), ytd_sales
- FROM titles
- Where type = 'trad_cook'
或
- USE pubs
- Select CONVERT(char(50), title), ytd_sales
- FROM titles
- Where type = 'trad_cook'
下面是结果集：（对任何一个查询）
- ytd_sales
- ----------------------------------------- -----------
- Onions, Leeks, and Garlic: Cooking Secrets of the 375
- Fifty Years in Buckingham Palace Kitchens 15096
- Sushi, Anyone? 4095
- (3 row(s) affected)
在下面的示例中，int 类型的ytd_sales 列转换为 char(20) 列，从而可以对该列使用 LIKE 谓词：
- USE pubs
- Select title, ytd_sales
- FROM titles
- Where CAST(ytd_sales AS char(20)) LIKE '15%'
- AND type = 'trad_cook'
下面是结果集：
- Title ytd_sales
- ----------------------------------------- ---------
- Fifty Years in Buckingham Palace Kitchens 15096
- (1 row(s) affected)
SQL Server 自动处理某些数据类型的转换。例如，如果比较 char 和 datetime 表达式、smallint 和 int 表达式、或不同长度的 char 表达式，SQL Server 可将它们自动转换。这种转换称为隐性转换。对这些转换不必使用 CAST 函数。但是，在下列情况下使用 CAST 都是可以接受的：
两个表达式的数据类型完全相同。
两个表达式可隐性转换。
必须显式转换数据类型。
如果试图进行不可能的转换（例如，将含有字母的 char 表达式转换为 int 类型），SQL Server 将显示一条错误信息。
如果转换时没有指定数据类型的长度，则 SQL Server 自动提供长度为 30。
转换为 datetime 或 smalldatetime 时，SQL Server 将拒绝所有无法识别为日期的值（包括 1753 年 1 月 1 日以前的日期）。当日期处于适当的范围内（1900 年 1 月 1 日到 2079 年 6 月 6 日）时，可将 datetime 值转换为 smalldatetime。时间值被四舍五入为最接近的分钟数。
转换为 bit 将把任何非零值都更改为 1。
转换为 money 或 smallmoney 时，整数后将加上货币单位。例如，整数值 4 被转换为货币时相当于 4 美元（对于默认语言 us_english）。浮点值的小数部分将四舍五入为四位小数以用于 money 值。将要转换为整数数据类型的 char 或 varchar 数据类型的表达式中，只能包含数字和可选的加号或减号（+ 或 -）。将忽略前导空格。要转换为 money 的 char 或 varchar 数据类型的表达式，还可包含可选的小数点和前导美元符号 ($)。
要转换为 float 或 real 数据类型的 char 或 varchar 类型表达式还可选择性地包含指数符号（e 或 E，后面有可选的 + 或 – 符号，再后面是数字）。
将字符表达式转换为其它大小的数据类型时，对于新的数据类型过长的值将被截断，SQL Server 将在 osql 实用工具和 SQL 查询分析器中显示星号 (*)。若数字表达式对于新的数据类型太长而无法显示，值将截断。下面是字符截断的示例：
- USE pubs
- Select SUBSTRING(title, 1, 25) AS Title, CONVERT(char(2), ytd_sales)
- FROM titles
- Where type = 'trad_cook'
下面是结果集：
- Title
- ------------------------- --
- Onions, Leeks, and Garlic *
- Fifty Years in Buckingham *
- Sushi, Anyone? *
- (3 row(s) affected)
在进行数据类型转换时，若目标数据类型的小数位数小于源数据类型的小数位数，则该值将被截断。例如，CAST(10.3496 AS money) 的结果是 $10.35。
可将 text 数据显式转换为 char 或 varchar，将 image 显式转换为 binary 或 varbinary。由于这些数据类型被限制为 8,000 个字符，所以转换也限制为 character 和 binary 数据类型的最大长度，即 8,000 个字符。可将 ntext 数据显式转换为 nchar 或 nvarchar，但最大长度为 4,000 个字符。如果没有指定长度，被转换的值的默认长度为 30 个字符。不支持隐式转换。
style 参数
将 datetime 数据转换为 char 或 varchar 数据时，CONVERT 的 style 参数提供了各种日期显示格式。为 style 参数提供的数值确定了 datetime 数据的显示方式。年份可以显示为两位或四位数。默认情况下，SQL Server 将年份显示为两位数。若要显示包括世纪的四位数年份 (yyyy)（即使年份数据是使用两位数的年份格式存储的），请给 style 值加 100 以获得四位数年份。
Convert的使用方法：
格式：
- Convert(data_type,expression[,style])
说明：此样式一般在时间类型(datetime,smalldatetime)与字符串类型(nchar,nvarchar,char,varchar)相互转换的时候才用到.
例子：
- Select Convert(varchar(30),getdate(),101) now
结果为
- now
- 09/15/2001
style数字在转换时间时的含义如下： Style(2位表示年份) Style(4位表示年份) 输入输出格式
0 100 mon dd yyyy hh：miAM(或PM)
1 101 mm/dd/yy
2 102 yymmdd
3 103 dd/mm/yy
4 104 ddmmyy
5 105 ddmmyy
6 106 dd mon yy
7 107 mon dd,yy
8 108 hh：mm：ss
9 109 mon dd yyyy hh：mi：ss：mmmmAM(或PM)
10 110 mmddyy
11 111 yy/mm/dd
12 112 yymmdd
13 113 dd mon yyyy hh：mi：ss：mmm(24小时制)
14 114 hh：mi：ss：mmm(24小时制)
20 120 yyyymmdd hh：mi：ss(24小时制)
21 121 yyyymmdd hh：mi：ss：mmm(24小时制)
以下示例显示用 style 参数进行转换：
- Select CONVERT(char(12), GETDATE(), 3)
此语句把当前日期转换为样式 3，dd/mm/yy。
在这里我附上一些实例：
- select CONVERT(varchar, getdate(), 120 )
- 2004-09-12 11:06:08
- select replace(replace(replace(CONVERT(varchar, getdate(), 120 ),'-',''),' ',''),':','')
- 20040912110608
- select CONVERT(varchar(12) , getdate(), 111 )
- 2004/09/12
- select CONVERT(varchar(12) , getdate(), 112 )
- 20040912
- select CONVERT(varchar(12) , getdate(), 102 )
- 2004.09.12
- select CONVERT(varchar(12) , getdate(), 101 )
- 09/12/2004
- select CONVERT(varchar(12) , getdate(), 103 )
- 12/09/2004
- select CONVERT(varchar(12) , getdate(), 104 )
- 12.09.2004
- select CONVERT(varchar(12) , getdate(), 105 )
- 12-09-2004
- select CONVERT(varchar(12) , getdate(), 106 )
- 12 09 2004
- select CONVERT(varchar(12) , getdate(), 107 )
- 09 12, 2004
- select CONVERT(varchar(12) , getdate(), 108 )
- 11:06:08
- select CONVERT(varchar(12) , getdate(), 109 )
- 09 12 2004 1
- select CONVERT(varchar(12) , getdate(), 110 )
- 09-12-2004
- select CONVERT(varchar(12) , getdate(), 113 )
- 12 09 2004 1
- select CONVERT(varchar(12) , getdate(), 114 )
- 11:06:08.177
