C# + Sql 时间 秒数被忽略了 坐等求解，秒回。。。
0
悬赏园豆：20
[已解决问题]
解决于 2012-07-19 10:51
1 CREATE PROCEDURE [dbo].[GetStepDataListByCollectDate] 2 @hospId uniqueidentifier = null, 3 @userId uniqueidentifier = null, 4 @dataId uniqueidentifier = null, 5 @loginName nvarchar(50) = null, 6 @userName nvarchar(50) = null, 7 @phone varchar(20) = null, 8 @fromDate varchar(19) = null, 9 @toDate varchar(19) = null, 10 @pageIndex bigint = 1, 11 @pageSize bigint = 100 12 AS 13 SET NOCOUNT ON; 14 15 IF ISDATE(@fromDate) = 0 SET @fromDate = '1753-01-01 00:00:00'; 16 IF ISDATE(@toDate) = 0 SET @toDate = '9999-12-31 23:59:59'; 17 IF @pageIndex < 1 SET @pageIndex = 1 18 IF @pageSize < 1 SET @pageSize = 10 19 20 DECLARE @lowerBound bigint 21 DECLARE @upperRound bigint 22 DECLARE @top bigint 23 24 SET @lowerBound = convert(numeric(20,0),@pageSize) * convert(numeric(20,0),@pageIndex) - convert(numeric(20,0),@pageSize); 25 SET @upperRound = @lowerBound + convert(numeric(20,0),@pageSize) + 1; 26 SET @top = @lowerBound +convert(numeric(20,0),@pageSize); 27 28 SET @loginName = ISNULL(@loginName, ''); 29 SET @userName = ISNULL(@userName, ''); 30 SET @phone = ISNULL(@phone, ''); 31 32 WITH PageIndex AS 33 ( 34 SELECT TOP (@top) ROW_NUMBER() OVER (ORDER BY COLLECTDATE DESC) IndexId, 35 u.HOSPITALID, u.USERID, d.DATAID, d.ADDDATE, d.COLLECTDATE, d.GATEWAYCODE, d.SENSORCODE, 36 u.LOGINNAME, u.USERNAME, g.GENDERNAME, ISNULL(u.AGE, 0) AS AGE, 37 u.ROLEID, u.USERCODE, u.MOBILE, u.PHONE, u.EMAIL, 38 ISNULL(d.IMPORTANT, 0) AS IMPORTANT, d.ISREMARK, d.DIAGNOSIS, 39 ISNULL(SMS.SMSCOUNT, 0) AS SMSCOUNT, 40 d.DATATYPE, d.STRIDE, d.CALORIE, 41 d.STEPS, d.FASTSTEPS, 42 d.SLOWSTEPS, d.MINUTES, 43 d.EXERCISE, d.BEGINTIME, 44 d.ENDTIME, d.FAST00TO01, 45 d.SLOW00TO01, d.FAST01TO02, 46 d.SLOW01TO02, d.FAST02TO03, 47 d.SLOW02TO03, d.FAST03TO04, 48 d.SLOW03TO04, d.FAST04TO05, 49 d.SLOW23TO00, d.FAST23TO00, 50 d.SLOW22TO23, d.FAST22TO23, 51 d.SLOW21TO22, d.FAST21TO22, 52 d.SLOW20TO21, d.FAST20TO21, 53 d.SLOW19TO20, d.FAST19TO20, 54 d.SLOW18TO19, d.FAST18TO19, 55 d.SLOW17TO18, d.FAST17TO18, 56 d.SLOW16TO17, d.FAST16TO17, 57 d.SLOW15TO16, d.FAST15TO16, 58 d.SLOW14TO15, d.FAST14TO15, 59 d.SLOW13TO14, d.FAST13TO14, 60 d.SLOW12TO13, d.FAST12TO13, 61 d.SLOW11TO12, d.FAST11TO12, 62 d.SLOW10TO11, d.FAST10TO11, 63 d.SLOW09TO10, d.FAST09TO10, 64 d.SLOW08TO09, d.FAST08TO09, 65 d.SLOW07TO08, d.FAST07TO08, 66 d.SLOW06TO07, d.FAST06TO07, 67 d.SLOW05TO06, d.FAST05TO06, 68 d.SLOW04TO05, ISNULL(d.DESTSTEPS, 0) 69 AS DESTSTEPS, ISNULL(d.DESTDISTANCE, 0) AS DESTDISTANCE, 70 ISNULL(d.DESTEXERCISE, 0) AS DESTEXERCISE, 71 ISNULL(d.DESTCALORIE, 0) AS DESTCALORIE, 72 ISNULL(d.ISSMSNOTIFY, 0) AS ISSMSNOTIFY 73 FROM dbo.T_STEPDATA d WITH (NOLOCK) INNER JOIN 74 dbo.T_USER u WITH (NOLOCK) ON d.USERID = u.USERID LEFT OUTER JOIN 75 dbo.T_GENDER g WITH (NOLOCK) ON 76 u.GENDERID = g.GENDERID LEFT OUTER JOIN 77 (SELECT DATAID, COUNT(*) AS SMSCOUNT 78 FROM dbo.T_SMS s WITH (NOLOCK) 79 WHERE ISNULL(ISDELETED, 0) = 0 80 GROUP BY DATAID) SMS ON SMS.DATAID = d.DATAID 81 WHERE (ISNULL(d.ISDELETED, 0) = 0) AND 82 (ISNULL(u.ISDELETED, 0) = 0) AND 83 (u.HOSPITALID = @hospId OR @hospId IS NULL) AND 84 (u.USERID = @userId OR @userId IS NULL) AND 85 (d.DATAID = @dataId OR @dataId IS NULL) AND 86 --(d.COLLECTDATE BETWEEN @fromDate AND @toDate) AND 87 (d.COLLECTDATE BETWEEN CONVERT(varchar(19), @fromDate, 25) AND CONVERT(varchar(19), @toDate, 25)) AND 88 (u.LOGINNAME = @loginName OR @loginName = '') AND 89 (u.USERNAME LIKE '%' + @userName + '%' OR @userName = '') AND 90 ((u.MOBILE LIKE '%' + @phone + '%' OR @phone = '') OR 91 (u.PHONE LIKE '%' + @phone + '%' OR @phone = '')) 92 ) 93 SELECT * 94 FROM PageIndex 95 WHERE PageIndex.Indexid > @lowerBound AND PageIndex.IndexId < @upperRound; 96 97 DECLARE @totalRecords int 98 99 SELECT @totalRecords = COUNT(*) 100 FROM dbo.T_STEPDATA d WITH (NOLOCK) INNER JOIN 101 dbo.T_USER u WITH (NOLOCK) ON d.USERID = u.USERID 102 WHERE (ISNULL(d.ISDELETED, 0) = 0) AND 103 (ISNULL(u.ISDELETED, 0) = 0) AND 104 (u.HOSPITALID = @hospId OR @hospId IS NULL) AND 105 (u.USERID = @userId OR @userId IS NULL) AND 106 (d.DATAID = @dataId OR @dataId IS NULL) AND 107 (d.COLLECTDATE BETWEEN CONVERT(varchar(19), @fromDate, 25) AND CONVERT(varchar(19), @toDate, 25)) AND 108 --(d.COLLECTDATE BETWEEN @fromDate AND @toDate) AND 109 (u.LOGINNAME = @loginName OR @loginName = '') AND 110 (u.USERNAME LIKE '%' + @userName + '%' OR @userName = '') AND 111 ((u.MOBILE LIKE '%' + @phone + '%' OR @phone = '') OR 112 (u.PHONE LIKE '%' + @phone + '%' OR @phone = '')); 113 114 SET NOCOUNT OFF; 115 116 RETURN @totalRecords;
在C#下，
这里选中的数据 当 以 2011-09-22 09:38:09 --- 2011-09-22 09:38:59 的时候取不出数据存储过程返回的条数位零，
但是 当 以 2011-09-22 09:38:09 --- 2011-09-22 09:39:00 的时候 ， 就可以取出数据 存储过程返回条数为六，
存储过程在数据库中执行 可以取到数据 ，
我又看了一下 不能在一分钟内取数据，也就是说分钟不能相同，这是为什么呢，我看了一天了 也看不出哪里错了。
这是C# 代码
DAL
public List<Model.STEPDATAVIEW> GetModelListOrderByCollectDate(Guid hospId, Guid userId, Guid dataId, string loginName, string userName, string phone, DateTime fromDate, DateTime toDate, int pageIndex, int pageSize, out int records) { SqlParameter[] parameters = { new SqlParameter("@hospId", SqlDbType.UniqueIdentifier), new SqlParameter("@userId", SqlDbType.UniqueIdentifier), new SqlParameter("@dataId", SqlDbType.UniqueIdentifier), new SqlParameter("@loginName", SqlDbType.NVarChar, 50), new SqlParameter("@userName", SqlDbType.NVarChar, 50), new SqlParameter("@phone", SqlDbType.VarChar, 20), new SqlParameter("@fromDate", SqlDbType.DateTime), new SqlParameter("@toDate", SqlDbType.DateTime), new SqlParameter("@pageIndex", SqlDbType.Int), new SqlParameter("@pageSize", SqlDbType.Int) }; if (!hospId.Equals(Guid.Empty)) parameters[0].Value = hospId; if (!userId.Equals(Guid.Empty)) parameters[1].Value = userId; if (!dataId.Equals(Guid.Empty)) parameters[2].Value = dataId; if (!string.IsNullOrEmpty(loginName)) parameters[3].Value = loginName; if (!string.IsNullOrEmpty(userName)) parameters[4].Value = userName; if (!string.IsNullOrEmpty(phone)) parameters[5].Value = phone; if (fromDate > DateTime.Parse("1753-01-01")) parameters[6].Value = fromDate; if (toDate > DateTime.Parse("1753-01-01")) parameters[7].Value = toDate; if (pageIndex > 0) parameters[8].Value = pageIndex; if (pageSize > 0) parameters[9].Value = pageSize; List<Model.STEPDATAVIEW> models = new List<Model.STEPDATAVIEW>(); using (DataSet ds = DbHelperSQL.RunProcedure("GetStepDataListByCollectDate", parameters, out records)) { DataRowCollection rows = ds.Tables[0].Rows; List<DataRow> lists = new List<DataRow>(); foreach (DataRow dataRow in rows) lists.Add(dataRow); if (records <= 0 || ds.Tables.Count <= 0 || ds.Tables[0].Rows.Count <= 0) return models; foreach (DataRow dr in ds.Tables[0].Rows) { Model.STEPDATAVIEW model = new Model.STEPDATAVIEW { HOSPITALID = new Guid(dr["HOSPITALID"].ToString()), USERID = new Guid(dr["USERID"].ToString()), DATAID = new Guid(dr["DATAID"].ToString()), GATEWAYCODE = dr["GATEWAYCODE"].ToString(), SENSORCODE = dr["SENSORCODE"].ToString(), LOGINNAME = dr["LOGINNAME"].ToString(), USERNAME = dr["USERNAME"].ToString(), GENDERNAME = dr["GENDERNAME"].ToString(), AGE = int.Parse(dr["AGE"].ToString()), ROLEID = int.Parse(dr["ROLEID"].ToString()), USERCODE = dr["USERCODE"].ToString(), MOBILE = dr["MOBILE"].ToString(), PHONE = dr["PHONE"].ToString(), EMAIL = dr["EMAIL"].ToString(), IMPORTANT = int.Parse(dr["IMPORTANT"].ToString()), DIAGNOSIS = dr["DIAGNOSIS"].ToString(), SMSCOUNT = int.Parse(dr["SMSCOUNT"].ToString()) }; if (dr["ISREMARK"].ToString() != "") model.ISREMARK = dr["ISREMARK"].ToString() == "1" || dr["ISREMARK"].ToString().ToLower() == "true"; if (dr["ADDDATE"].ToString() != "") model.ADDDATE = DateTime.Parse(dr["ADDDATE"].ToString()); if (dr["COLLECTDATE"].ToString() != "") model.COLLECTDATE = DateTime.Parse(dr["COLLECTDATE"].ToString()); model.BEGINTIME = dr["BEGINTIME"].ToString(); model.ENDTIME = dr["ENDTIME"].ToString(); if (dr["DATATYPE"].ToString() != "") model.DATATYPE = int.Parse(dr["DATATYPE"].ToString()); if (dr["STRIDE"].ToString() != "") model.STRIDE = int.Parse(dr["STRIDE"].ToString()); if (dr["CALORIE"].ToString() != "") model.CALORIE = int.Parse(dr["CALORIE"].ToString()); if (dr["STEPS"].ToString() != "") model.STEPS = int.Parse(dr["STEPS"].ToString()); if (dr["FASTSTEPS"].ToString() != "") model.FASTSTEPS = int.Parse(dr["FASTSTEPS"].ToString()); if (dr["SLOWSTEPS"].ToString() != "") model.SLOWSTEPS = int.Parse(dr["SLOWSTEPS"].ToString()); if (dr["MINUTES"].ToString() != "") model.MINUTES = int.Parse(dr["MINUTES"].ToString()); if (dr["EXERCISE"].ToString() != "") model.EXERCISE = int.Parse(dr["EXERCISE"].ToString()); if (dr["FAST00TO01"].ToString() != "") model.FAST00TO01 = int.Parse(dr["FAST00TO01"].ToString()); if (dr["SLOW00TO01"].ToString() != "") model.SLOW00TO01 = int.Parse(dr["SLOW00TO01"].ToString()); if (dr["FAST01TO02"].ToString() != "") model.FAST01TO02 = int.Parse(dr["FAST01TO02"].ToString()); if (dr["SLOW01TO02"].ToString() != "") model.SLOW01TO02 = int.Parse(dr["SLOW01TO02"].ToString()); if (dr["FAST02TO03"].ToString() != "") model.FAST02TO03 = int.Parse(dr["FAST02TO03"].ToString()); if (dr["SLOW02TO03"].ToString() != "") model.SLOW02TO03 = int.Parse(dr["SLOW02TO03"].ToString()); if (dr["FAST03TO04"].ToString() != "") model.FAST03TO04 = int.Parse(dr["FAST03TO04"].ToString()); if (dr["SLOW03TO04"].ToString() != "") model.SLOW03TO04 = int.Parse(dr["SLOW03TO04"].ToString()); if (dr["FAST04TO05"].ToString() != "") model.FAST04TO05 = int.Parse(dr["FAST04TO05"].ToString()); if (dr["SLOW23TO00"].ToString() != "") model.SLOW23TO00 = int.Parse(dr["SLOW23TO00"].ToString()); if (dr["FAST23TO00"].ToString() != "") model.FAST23TO00 = int.Parse(dr["FAST23TO00"].ToString()); if (dr["SLOW22TO23"].ToString() != "") model.SLOW22TO23 = int.Parse(dr["SLOW22TO23"].ToString()); if (dr["FAST22TO23"].ToString() != "") model.FAST22TO23 = int.Parse(dr["FAST22TO23"].ToString()); if (dr["SLOW21TO22"].ToString() != "") model.SLOW21TO22 = int.Parse(dr["SLOW21TO22"].ToString()); if (dr["FAST21TO22"].ToString() != "") model.FAST21TO22 = int.Parse(dr["FAST21TO22"].ToString()); if (dr["SLOW20TO21"].ToString() != "") model.SLOW20TO21 = int.Parse(dr["SLOW20TO21"].ToString()); if (dr["FAST20TO21"].ToString() != "") model.FAST20TO21 = int.Parse(dr["FAST20TO21"].ToString()); if (dr["SLOW19TO20"].ToString() != "") model.SLOW19TO20 = int.Parse(dr["SLOW19TO20"].ToString()); if (dr["FAST19TO20"].ToString() != "") model.FAST19TO20 = int.Parse(dr["FAST19TO20"].ToString()); if (dr["SLOW18TO19"].ToString() != "") model.SLOW18TO19 = int.Parse(dr["SLOW18TO19"].ToString()); if (dr["FAST18TO19"].ToString() != "") model.FAST18TO19 = int.Parse(dr["FAST18TO19"].ToString()); if (dr["SLOW17TO18"].ToString() != "") model.SLOW17TO18 = int.Parse(dr["SLOW17TO18"].ToString()); if (dr["FAST17TO18"].ToString() != "") model.FAST17TO18 = int.Parse(dr["FAST17TO18"].ToString()); if (dr["SLOW16TO17"].ToString() != "") model.SLOW16TO17 = int.Parse(dr["SLOW16TO17"].ToString()); if (dr["FAST16TO17"].ToString() != "") model.FAST16TO17 = int.Parse(dr["FAST16TO17"].ToString()); if (dr["SLOW15TO16"].ToString() != "") model.SLOW15TO16 = int.Parse(dr["SLOW15TO16"].ToString()); if (dr["FAST15TO16"].ToString() != "") model.FAST15TO16 = int.Parse(dr["FAST15TO16"].ToString()); if (dr["SLOW14TO15"].ToString() != "") model.SLOW14TO15 = int.Parse(dr["SLOW14TO15"].ToString()); if (dr["FAST14TO15"].ToString() != "") model.FAST14TO15 = int.Parse(dr["FAST14TO15"].ToString()); if (dr["SLOW13TO14"].ToString() != "") model.SLOW13TO14 = int.Parse(dr["SLOW13TO14"].ToString()); if (dr["FAST13TO14"].ToString() != "") model.FAST13TO14 = int.Parse(dr["FAST13TO14"].ToString()); if (dr["SLOW12TO13"].ToString() != "") model.SLOW12TO13 = int.Parse(dr["SLOW12TO13"].ToString()); if (dr["FAST12TO13"].ToString() != "") model.FAST12TO13 = int.Parse(dr["FAST12TO13"].ToString()); if (dr["SLOW11TO12"].ToString() != "") model.SLOW11TO12 = int.Parse(dr["SLOW11TO12"].ToString()); if (dr["FAST11TO12"].ToString() != "") model.FAST11TO12 = int.Parse(dr["FAST11TO12"].ToString()); if (dr["SLOW10TO11"].ToString() != "") model.SLOW10TO11 = int.Parse(dr["SLOW10TO11"].ToString()); if (dr["FAST10TO11"].ToString() != "") model.FAST10TO11 = int.Parse(dr["FAST10TO11"].ToString()); if (dr["SLOW09TO10"].ToString() != "") model.SLOW09TO10 = int.Parse(dr["SLOW09TO10"].ToString()); if (dr["FAST09TO10"].ToString() != "") model.FAST09TO10 = int.Parse(dr["FAST09TO10"].ToString()); if (dr["SLOW08TO09"].ToString() != "") model.SLOW08TO09 = int.Parse(dr["SLOW08TO09"].ToString()); if (dr["FAST08TO09"].ToString() != "") model.FAST08TO09 = int.Parse(dr["FAST08TO09"].ToString()); if (dr["SLOW07TO08"].ToString() != "") model.SLOW07TO08 = int.Parse(dr["SLOW07TO08"].ToString()); if (dr["FAST07TO08"].ToString() != "") model.FAST07TO08 = int.Parse(dr["FAST07TO08"].ToString()); if (dr["SLOW06TO07"].ToString() != "") model.SLOW06TO07 = int.Parse(dr["SLOW06TO07"].ToString()); if (dr["FAST06TO07"].ToString() != "") model.FAST06TO07 = int.Parse(dr["FAST06TO07"].ToString()); if (dr["SLOW05TO06"].ToString() != "") model.SLOW05TO06 = int.Parse(dr["SLOW05TO06"].ToString()); if (dr["FAST05TO06"].ToString() != "") model.FAST05TO06 = int.Parse(dr["FAST05TO06"].ToString()); if (dr["SLOW04TO05"].ToString() != "") model.SLOW04TO05 = int.Parse(dr["SLOW04TO05"].ToString()); if (dr["DESTSTEPS"].ToString() != "") model.DESTSTEPS = int.Parse(dr["DESTSTEPS"].ToString()); if (dr["DESTDISTANCE"].ToString() != "") model.DESTDISTANCE = int.Parse(dr["DESTDISTANCE"].ToString()); if (dr["DESTEXERCISE"].ToString() != "") model.DESTEXERCISE = int.Parse(dr["DESTEXERCISE"].ToString()); if (dr["DESTCALORIE"].ToString() != "") model.DESTCALORIE = int.Parse(dr["DESTCALORIE"].ToString()); if (dr["ISSMSNOTIFY"].ToString() != "") model.ISSMSNOTIFY = dr["ISSMSNOTIFY"].ToString() == "1" || dr["ISSMSNOTIFY"].ToString().ToLower() == "true"; models.Add(model); } } return models; }
BLL 就是简单的调用
界面调试调用
1 // 益体康演示 2 Guid hospitalId = new Guid("13A05A7B-5669-4E60-8CA7-21F1A0FB6B0A"); 3 // wcc 4 Guid userId = new Guid("D8398DEA-E396-4F9C-903C-F80EAAE216C0"); 5 6 string loginName = string.Empty; 7 string userName = string.Empty; 8 string phone = string.Empty; 9 10 DateTime fromDate = DateTime.MinValue; 11 DateTime toDate = DateTime.MaxValue; 12 13 fromDate = new DateTime(2011, 9, 22, 9, 38, 09,000); 14 toDate = new DateTime(2011, 9, 22, 9, 38, 58,000); 15 int pageIndex = 1; 16 int pageSize = int.MaxValue; 17 18 int totalRecords = 0; 19 List<STEPDATAVIEW> dataList = 20 Data.BLL.STEPDATAVIEW.GetModelListOrderByCollectDate( 21 hospitalId, userId, Guid.Empty, 22 loginName, userName, phone, fromDate, toDate, pageIndex, pageSize, out totalRecords); 23 24 var s = 0;
最佳答案
0
在查询分析器里执行下：
select * from tab1 where collectdate > '2011-09-22 09:38:09' and collectdate < '2011-09-22 09:38:59'
收获园豆：20
数据库中 可以取到数据 是C#中问题
@Strong C菜: 如果你是用C#调用存储过程，并且你的存储过程在查询分析器里执行正确，那么请问，你为什么不贴C#代码，却要贴存储过程？你确定传入的fromDate,toDate的值正确吗？
@程序猿.码农: 第一次求助，刚上班才半月的菜鸟， 等下我把代码拷上。
@Strong C菜: 鉴于你存储过程申明的是 varchar 类型，请在C#中把fromDate转换为字符串，如下：
Data.BLL.STEPDATAVIEW.GetModelListOrderByCollectDate(hospitalId, userId, Guid.Empty,loginName, userName, phone, fromDate.ToString("yyyy-MM-dd HH:mm:ss"), toDate.ToString("yyyy-MM-dd HH:mm:ss"), pageIndex, pageSize, out totalRecords);
@程序猿.码农: 但是 声明的时候类型都是DateTime 类型的 转成字符串的话报错的， 关键是只有分钟相同就不行，好像是秒数传进去后就被忽略了。 分钟不同的话就可以，但是他也只是取出两分钟之间的，比如（'2011-09-22 09:38:09' 于'2011-09-22 09:39:59' 取出的是09:38 到09:39 之间的数据）
@Strong C菜: @fromDate varchar(19) = null,
9 @toDate varchar(19) = null,
这是你的存储过程申明的数据类型，如何看出是DateTime类型？
你 new SqlParameter 时，应该使用 varchar 类型。
@程序猿.码农: 大神，大恩 ，谢谢侬了 。 解决了 ···
其他回答(4)
1
2011-09-22 09:38:59.997
请在最后回上.997
并不是秒数没有，而是毫秒数没有，楼主描述让人误解。
SQL Server 的毫秒数递增是3，即09:38:59 相当于 09:38:59.000，依次大概如下：
09:38:59.000
09:38:59.003
09:38:59.006
09:38:59.009
09:38:59.012
09:38:59.015
09:38:59.018
09:38:59.021
09:38:59.024
09:38:59.027
09:38:59.030
...
09:38:59.997 (这是最大的毫秒数)
09:38:59.998/999,就向秒位进一了
楼上正解
请再看看我的补充，刚有点急 ，打出的字没头绪，现在好了
0
可能COLLECTDATE字段使用了smalldatetime类型，如果是这样，改为datetime类型。
我看了 是DateTime 类型的
0
用性能跟踪器跟踪一下，具体传进来的值是多少，是不是跟你想象中的一致.
出现这样的问题，用性能跟踪最好了
0
你页面上直接传个字符串，数据库里转成时间就好了
select * from employee where start_date between convert(datetime,'2010-11-10 08:53:21') and convert(datetime,'2010-11-10 08:53:22')
