你的位置: Kiyo'Space首页 数据库 阅读文章 欢迎留下您的足迹

SQL Server日期查询时的日期格式转换

[ 数据库 ] 分享

问题提出:
经常会有这样的查询要求,输入或者日历控件产生的日期格式为 yyyy-mm-dd,而数据库中的字段是datetime类型,即yyyy-mm-dd hh:mm:ss。如果简单的使用between语句或者=语句进行查询,查询结果可能是错误或者数据不全。
假设 数据库中有 2008-2-27 日的数据,但直接使用 fielddate='2008-2-27' 或者使用 between '2008-2-27' and '2008-2-27'将不能查到该天的数据。

问题解决:
使用SQL Server提供的Convert函数进行转换,由于2008-02-27最长是10位,所以使用如下语句 convert(varchar(10),fielddate,121))进行转换,其中fielddate为数据表的字段名。这样在查询时数据库得到参数后先自动将数据库内的信息转换为yyyy-mm-dd格式的10位字符,只要与参数相同即可返回查询结果.而convert中的121是指将datetime类型转换为char类型时获得包括世纪位数的4位年份。
Convert函数的一些说明,以下资料来源于网络

不带世纪数位 (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* 科威特 dd mon yyyy hh:mi:ss:mmmAM
- 131* 科威特 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。

使用 CONVERT:
CONVERT (data_type[(length)], expression [, style])

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

SQL语句的日期时间比较(Oracle,MS SQL,Access)

@@@@@Oracle:
在Oracle里,日期(时间)的对比主要是遵循先转换,再比较的原则,常用的函数是To_date和To_char,这两个函数分别将字符转类型为日期类型,和将日期类型转为日期类型参数如下:

举例:
select * from Table1 where Date_Type_Col=TO_DATE('1998-12-31 00:00:00','YYYY-MM-DD HH24:MI:SS')

select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;

@@@@@Access:
Access 数据库的Date格式字段值必须由一对 # 符号标识,即当Access与日期常量比较的时候,日期常量应由##包围。
举例:
Select * From Customer Where BirthDay
Between #2002-01-01# and #2002-01-31#
又如查找2001-01-01至2001-01-31(即该年一月份)生日的客户资料:
1、Select * FROM Customer Where Year(Birthday)=2001 and
month(Birthday)=1 AND
Day(Birthday)>=1 AND Day(Birthday)<=31
2、Select * FROM Customer Where FORMAT(Birthday,"yymmdd")
BETWEEN "20010101" AND "20010131"

没有评论

  • (Required)
  • (Required, will not be published)