关注我
在.net的C#开发中，对日期类型DateTime来进行模糊查询数据库的思路分析。
一、 使用like进行模糊查询，需要将日期使用Convert转化为字符串类型。
在数据表中有一字段类型为datetime时，输入如下的sql语句查询无效，会返回0个记录：
select count(*) as num from ziyuaninfo where addtime like '2011%'
在数据表中有一字段类型为datetime时，输入如下的sql语句查询无效，会返回0个记录：
select count(*) as num from ziyuaninfo where addtime like '2009%'
刚开始不解，好像以前是用这条语句查询，一直是正确的？现在怎么就……。
稍一冷静蓦然想起过去常将存放日期数据的字段类型设为varchar(50)，因而查询无误，时间一常便形成一种误解，以为datetime类型可以用上面的语句查询。在对日期类型数据查询时，必须要使用convert转换，正确的语句如下：
select count(*) as num from ziyuaninfo where convert(varchar(50),addtime,120) like '2009%'
即可查到所需结果。在convert函数中，第一个参数“varchar(50”是转换类型的结果，第二个参数“addtime”是要转换的字段，第三个是将日期数据转换的格式。格式含义如下：
- 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 数据类型长度来截断不需要的日期部分。
注意：使用like这种方式的缺点是转化为字符串后必须按照字符串的格式进行模糊查询，不够灵活，效率差。
二、 采用between...and来进行查询
这种查询方式必须指定日期的范围，前小后大，比如：查询2012-11-22，则用：日期字段 between '2012-11-22' and '2012-11-23'，因为日期类型在数据库中默认带时分秒毫秒。
下面以C#为例介绍一下按日期时间类型模糊查询数据库的实现思路：
<asp:TextBox ID="txtSearchContent" runat="server" Text="" Width="240px" ></asp:TextBox> <asp:RadioButtonList ID="rdoBtnSearchTimeRange" runat="server" RepeatDirection="Horizontal"> <asp:ListItem Selected="True" Value="0">默认</asp:ListItem> <asp:ListItem Value="1">3天</asp:ListItem> <asp:ListItem Value="2">周</asp:ListItem> <asp:ListItem Value="3">3个月</asp:ListItem> <asp:ListItem Value="4">以前</asp:ListItem> <asp:ListItem Value="5">以后</asp:ListItem> </asp:RadioButtonList>
if (InputCheckTool.Check(txtSearchContent.Text.Trim(), InputCheckTool.Type.日期))
{
string searchTime = txtSearchContent.Text.Trim().Replace('/', '-').Replace('_', '-').Replace('.', '-');
string searchTimeRange = string.Empty;
if (searchTime.Length == 4)
{
searchTime += "-1-1";
searchTimeRange = "yy";
}
else if (searchTime.Length < 8)
{
searchTime += "-1";
searchTimeRange = "mm";
}
else
{
searchTimeRange = "dy";
}
switch (rdoBtnSearchTimeRange.SelectedValue)
{
case "1":
searchTimeRange = "3day";
break;
case "2":
searchTimeRange = "wk";
break;
case "3":
searchTimeRange = "3mm";
break;
case "4":
searchTimeRange = "before";
break;
case "5":
searchTimeRange = "after";
break;
default:
break;
}
return ArticleManager.GetHotArticleByCategoryIDAndCreateTime(Convert.ToInt32(ddlArticleType.SelectedValue), searchTime, searchTimeRange, Convert.ToInt32(txtNumber.Text.Trim()));
}
else
{
ScriptManager.RegisterClientScriptBlock(UpdatePanel1, this.GetType(), "click", "alert('只能进行模糊查询\\r\\n日期的格式必须为下面的一种：\\r\\nYYYY\\r\\nYYYY-MM\\r\\nYYYY-MM-DD\\r\\n年月日的分隔符只能为下面中的一种:\\r\\n横杠（-)或者斜杠(/)或者下划线(_)或者小数点(.)');focusAndSelectSearchContent();", true);
return null;
}使用下面的自定义的正则表达式类进行判断时间日期输入的合法性：
public class InputCheckTool
{
public enum Type
{
整数 = 0,
正整数,
负整数,
数字,
电话,
正数,
负数,
浮点数,
正浮点数,
负浮点数,
浮点数2,
非负浮点数,
非正浮点数,
邮件,
颜色,
url,
中文,
ACSII字符,
邮编,
手机,
IP地址,
非空,
图片,
压缩文件,
日期,
QQ号码,
用户名 = 27,
字母,
大写字母,
小写字母,
身份证
}
static string pattern;
public static bool Check(string str, Type t)
{
switch (Convert.ToInt32(t))
{
case 0://整数
pattern = @"^-?[1-9]\d*$";
break;
case 1://正整数
pattern = "^[0-9]*[1-9][0-9]*$";
break;
case 2://负整数
pattern = "^-[0-9]*[1-9][0-9]*$";
break;
case 3://数字
pattern = "^([+-]?)\\d*\\.?\\d+$";
break;
case 4://电话
//在做项目时常常用到判断电话号码的正则表达式，写了一个，可验证如下27种格式：
//110
//8888888
//88888888
//8888888-123
//88888888-23435
//0871-8888888-123
//023-88888888-23435
//86-0871-8888888-123
//8888888_123
//88888888_23435
//0871_8888888_123
//023_88888888_23435
//86_0871_8888888_123
//8888888－123
//88888888－23435
//0871－8888888－123
//023－88888888－23435
//86－0871－8888888－123
//8888888—123
//88888888—23435
//0871—8888888—123
//023—88888888—23435
//86—0871—8888888—123
//13588888888
//15988888888
//013588888888
//015988888888
//(0315)7663551
pattern = @"((^(\d{2,4}[-_－—]?)?\d{3,8}([-_－—]?\d{3,8})?([-_－—]?\d{1,7})?$)|(^0?1[35]\d{9}$)z)|(^(\([0-9]+\))?[0-9]{7,8}$)";
break;
case 5://正数（正整数+ 0）
pattern = @"^\d+$";
break;
case 6://负数（负整数+ 0）
pattern = @"^((-\d+)|(0+))$";
break;
case 7://浮点数
pattern = @"^(-?\d+)(\.\d+)?$";
break;
case 8://正浮点数
pattern = @"^(([0-9]+\.[0-9]*[1-9][0-9]*)|([0-9]*[1-9][0-9]*\.[0-9]+)|([0-9]*[1-9][0-9]*))$";
break;
case 9://负浮点数
pattern = @"^(-(([0-9]+\.[0-9]*[1-9][0-9]*)|([0-9]*[1-9][0-9]*\.[0-9]+)|([0-9]*[1-9][0-9]*)))$";
break;
case 10://浮点数
pattern = "^-?([1-9]\\d*.\\d*|0.\\d*[1-9]\\d*|0?.0+|0)$";
break;
case 11://非负浮点数（正浮点数+ 0）
pattern = @"^\d+(\.\d+)?$";
break;
case 12://非正浮点数（负浮点数+ 0）
pattern = @"^((-\d+(\.\d+)?)|(0+(\.0+)?))$";
break;
case 13://邮件 //正确
pattern = @"^[\w-]+(\.[\w-]+)*@[\w-]+(\.[\w-]+)+$";
break;
case 14://颜色
pattern = "^[a-fA-F0-9]{6}$";
break;
case 15://url(http格式的)
pattern = @"http://([\w-]+\.)+[\w-]+(/[\w- ./?%&=]*)?";
break;
case 16://仅中文
pattern = @"[\u4e00-\u9fa5]";
break;
case 17://仅ACSII字符
pattern = "^[\\x00-\\xFF]+$";
break;
case 18://邮编
pattern = "^\\d{6}$";
break;
case 19://手机(13号段和号段)
pattern = "^13[0-9]{9}$";
break;
case 20://ip地址
pattern = @"^(25[0-5]|2[0-4][0-9]|[0-1]{1}[0-9]{2}|[1-9]{1}[0-9]{1}|[1-9])\.(25[0-5]|2[0-4][0-9]|[0-1]{1}[0-9]{2}|[1-9]{1}[0-9]{1}|[1-9]|0)\.(25[0-5]|2[0-4][0-9]|[0-1]{1}[0-9]{2}|[1-9]{1}[0-9]{1}|[1-9]|0)\.(25[0-5]|2[0-4][0-9]|[0-1]{1}[0-9]{2}|[1-9]{1}[0-9]{1}|[0-9])$";
break;
case 21://非空
pattern = "^\\S+$";
break;
case 22://图片
pattern = @"(.*)\.(jpg|gif|png|bmp)$";
break;
case 23://压缩文件
pattern = "(.*)\\.(rar|zip|7zip|tgz)$";
break;
case 24://日期
//这个日期正则表达式支持如下格式，其中的"年/月/日"的分隔符可以为"-"或者"/"或者"_"或者"."的四种形式之一
//YYYY
//YYYY-MM
//YYYY-MM-DD 的形式
pattern = @"((^((1[8-9]\d{2})|([2-9]\d{3}))$)|(^((1[8-9]\d{2})|([2-9]\d{3}))([-\/\._])(0?[1-9]|10|11|12)$)|(^((1[8-9]\d{2})|([2-9]\d{3}))([-\/\._])(10|12|0?[13578])([-\/\._])(3[01]|[12][0-9]|0?[1-9])$)|(^((1[8-9]\d{2})|([2-9]\d{3}))([-\/\._])(11|0?[469])([-\/\._])(30|[12][0-9]|0?[1-9])$)|(^((1[8-9]\d{2})|([2-9]\d{3}))([-\/\._])(0?2)([-\/\._])(2[0-8]|1[0-9]|0?[1-9])$)|(^([2468][048]00)([-\/\._])(0?2)([-\/\._])(29)$)|(^([3579][26]00)([-\/\._])(0?2)([-\/\._])(29)$)|(^([1][89][0][48])([-\/\._])(0?2)([-\/\._])(29)$)|(^([2-9][0-9][0][48])([-\/\._])(0?2)([-\/\._])(29)$)|(^([1][89][2468][048])([-\/\._])(0?2)([-\/\._])(29)$)|(^([2-9][0-9][2468][048])([-\/\._])(0?2)([-\/\._])(29)$)|(^([1][89][13579][26])([-\/\._])(0?2)([-\/\._])(29)$)|(^([2-9][0-9][13579][26])([-\/\._])(0?2)([-\/\._])(29)$))";
break;
case 25://QQ号码
pattern = "[1-9][0-9]{4,}";
break;
case 27://用来用户注册。匹配由数字、个英文字母或者下划线组成的字符串
pattern = "^(?!\\d)[a-zA-Z0-9_\\u4e00-\\u9fa5]+$";
break;
case 28://字母
pattern = "^[A-Za-z]+$";
break;
case 29://大写字母
pattern = "^[A-Z]+$";
break;
case 30://小写字母
pattern = "^[a-z]+$";
break;
case 31://身份证
pattern = @"^[1-9]([0-9]{16}|[0-9]{13})[xX0-9]$";
break;
default:
pattern = string.Empty;
break;
}
return Regex.IsMatch(str, pattern);
}
}访问数据库的C#代码如下：
/// <summary>
/// 根据文章类型和文章创建时间得到查询的热门文章信息
/// </summary>
/// <param name="categoryID">文章目录ID</param>
/// <param name="createTime">搜索的发布文章的开始时间</param>
/// <param name="searchTimeRange">搜索的时间范围</param>
/// <param name="hotArticleNumber">热文搜索的数量</param>
/// <returns></returns>
public static IList<Article> GetHotArticleByCategoryIDAndCreateTime(int categoryID, string searchCreateTime, string searchTimeRange, int hotArticleNumber)
{
IList<Article> articles = new List<Article>();
Article article = null;
SqlParameter[] para = new SqlParameter[]{
new SqlParameter("@categoryID", categoryID),
new SqlParameter("@searchCreateTime", searchCreateTime),
new SqlParameter("@searchTimeRange", searchTimeRange),
new SqlParameter("@hotArticleNumber", hotArticleNumber)
};
using (SqlDataReader reader = SqlHelper.ExecuteReader(SqlHelper.connectionString, "dbo.usp_GetHotArticleByCategoryIDAndCreateTime", para))
{
while (reader.Read())
{
article = new Article();
article.ArticleID = Convert.ToInt64(reader["ArticleID"]);
article.Title = reader["Title"].ToString();
article.ArticleLabel = reader["ArticleLabel"].ToString();
article.CreatedTime = Convert.ToDateTime(reader["CreatedTime"]);
article.Clicks = Convert.ToInt32(reader["Clicks"]);
articles.Add(article);
}
}
return articles;
}调用数据库的存储过程的脚本如下：
/****** Object: StoredProcedure [dbo].[usp_GetHotArticleByCategoryIDAndCreateTime] Script Date: 11/02/2012 16:12:49 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: <Author,sms,Name> -- Create date: <Create Date,,> -- Description: <Description,,> -- ============================================= CREATE PROCEDURE [dbo].[usp_GetHotArticleByCategoryIDAndCreateTime] -- Add the parameters for the stored procedure here @categoryID int=-1, @searchCreateTime nvarchar(50), @searchTimeRange nvarchar(20), @hotArticleNumber int=100 AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; -- Insert statements for procedure here if (@categoryID = -1) begin if (@searchTimeRange='dy') SELECT top (@hotArticleNumber) ArticleID,Title,ArticleLabel,CreatedTime,Clicks from Article where CreatedTime between @searchCreateTime and DATEADD(DAY,1,@searchCreateTime) order by Clicks desc else if (@searchTimeRange='mm') SELECT top (@hotArticleNumber) ArticleID,Title,ArticleLabel,CreatedTime,Clicks from Article where CreatedTime between @searchCreateTime and DATEADD(MM,1,@searchCreateTime) order by Clicks desc else if (@searchTimeRange='yy') SELECT top (@hotArticleNumber) ArticleID,Title,ArticleLabel,CreatedTime,Clicks from Article where CreatedTime between @searchCreateTime and DATEADD(YY,1,@searchCreateTime) order by Clicks desc else if (@searchTimeRange='3day') SELECT top (@hotArticleNumber) ArticleID,Title,ArticleLabel,CreatedTime,Clicks from Article where CreatedTime between @searchCreateTime and DATEADD(DAY,3,@searchCreateTime) order by Clicks desc else if (@searchTimeRange='wk') SELECT top (@hotArticleNumber) ArticleID,Title,ArticleLabel,CreatedTime,Clicks from Article where CreatedTime between @searchCreateTime and DATEADD(WK,1,@searchCreateTime) order by Clicks desc else if (@searchTimeRange='3mm') SELECT top (@hotArticleNumber) ArticleID,Title,ArticleLabel,CreatedTime,Clicks from Article where CreatedTime between @searchCreateTime and DATEADD(MM,3,@searchCreateTime) order by Clicks desc else if (@searchTimeRange='before') SELECT top (@hotArticleNumber) ArticleID,Title,ArticleLabel,CreatedTime,Clicks from Article where CreatedTime < @searchCreateTime order by Clicks desc else SELECT top (@hotArticleNumber) ArticleID,Title,ArticleLabel,CreatedTime,Clicks from Article where CreatedTime > @searchCreateTime order by Clicks desc end else begin if (@searchTimeRange='dy') SELECT top (@hotArticleNumber) ArticleID,Title,ArticleLabel,CreatedTime,Clicks from Article where CategoryID=@categoryID and CreatedTime between @searchCreateTime and DATEADD(DAY,1,@searchCreateTime) order by Clicks desc else if (@searchTimeRange='mm') SELECT top (@hotArticleNumber) ArticleID,Title,ArticleLabel,CreatedTime,Clicks from Article where CategoryID=@categoryID and CreatedTime between @searchCreateTime and DATEADD(MM,1,@searchCreateTime) order by Clicks desc else if (@searchTimeRange='yy') SELECT top (@hotArticleNumber) ArticleID,Title,ArticleLabel,CreatedTime,Clicks from Article where CategoryID=@categoryID and CreatedTime between @searchCreateTime and DATEADD(YY,1,@searchCreateTime) order by Clicks desc else if (@searchTimeRange='3day') SELECT top (@hotArticleNumber) ArticleID,Title,ArticleLabel,CreatedTime,Clicks from Article where CategoryID=@categoryID and CreatedTime between @searchCreateTime and DATEADD(DAY,3,@searchCreateTime) order by Clicks desc else if (@searchTimeRange='wk') SELECT top (@hotArticleNumber) ArticleID,Title,ArticleLabel,CreatedTime,Clicks from Article where CategoryID=@categoryID and CreatedTime between @searchCreateTime and DATEADD(WK,1,@searchCreateTime) order by Clicks desc else if (@searchTimeRange='3mm') SELECT top (@hotArticleNumber) ArticleID,Title,ArticleLabel,CreatedTime,Clicks from Article where CategoryID=@categoryID and CreatedTime between @searchCreateTime and DATEADD(MM,3,@searchCreateTime) order by Clicks desc else if (@searchTimeRange='before') SELECT top (@hotArticleNumber) ArticleID,Title,ArticleLabel,CreatedTime,Clicks from Article where CategoryID=@categoryID and CreatedTime < @searchCreateTime order by Clicks desc else SELECT top (@hotArticleNumber) ArticleID,Title,ArticleLabel,CreatedTime,Clicks from Article where CategoryID=@categoryID and CreatedTime > @searchCreateTime order by Clicks desc end END GO
好了，就介绍到这里吧！