首页 新闻 搜索 专区 学院

C# + Sql 关于时间问题,求解,坐等。。。

0
悬赏园豆:20 [已关闭问题] 关闭于 2012-07-18 16:40

这是 数据库中的 数据 , 选中的 是查询过滤的后想要获取的数据  

下面是 C#代码 :

fromDate = new DateTime(2011, 9, 22, 9, 38, 09,000);
toDate = new DateTime(2011, 9, 22, 9, 38, 58,000);

int pageIndex = 1;
int pageSize = int.MaxValue;

int totalRecords = 0;

// 这里的dataList  当时间是以 分钟,小时 ,天数间距获取数据的时候 可以获取数据               -----------------------但是 以秒为间距却获取不到数据(就像上面的代码)
List<STEPDATAVIEW> dataList = Data.BLL.STEPDATAVIEW.GetModelListOrderByCollectDate(
hospitalId, userId, Guid.Empty,
loginName, userName, phone, fromDate, toDate, pageIndex, pageSize, out totalRecords);

下面是存储过程

  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        int = 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) * @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           (u.LOGINNAME = @loginName OR @loginName = '') AND
 88           (u.USERNAME LIKE '%' + @userName + '%' OR @userName = '') AND
 89           ((u.MOBILE LIKE '%' + @phone + '%' OR @phone = '') OR
 90           (u.PHONE LIKE '%' + @phone + '%' OR @phone = ''))
 91 )
 92 SELECT *
 93 FROM PageIndex
 94 WHERE PageIndex.Indexid > @lowerBound AND PageIndex.IndexId < @upperRound;
 95 
 96 DECLARE    @totalRecords    int
 97 
 98 SELECT @totalRecords = COUNT(*)
 99 FROM dbo.T_STEPDATA d WITH (NOLOCK) INNER JOIN
100       dbo.T_USER u WITH (NOLOCK) ON d.USERID = u.USERID
101 WHERE (ISNULL(d.ISDELETED, 0) = 0) AND 
102       (ISNULL(u.ISDELETED, 0) = 0) AND
103       (u.HOSPITALID = @hospId OR @hospId IS NULL) AND
104       (u.USERID = @userId OR @userId IS NULL) AND
105       (d.DATAID = @dataId OR @dataId IS NULL) AND
106       (d.COLLECTDATE BETWEEN @fromDate AND @toDate) AND    
107       (u.LOGINNAME = @loginName OR @loginName = '') AND
108       (u.USERNAME LIKE '%' + @userName + '%' OR @userName = '') AND
109       ((u.MOBILE LIKE '%' + @phone + '%' OR @phone = '') OR
110       (u.PHONE LIKE '%' + @phone + '%' OR @phone = ''));
111 
112 SET NOCOUNT OFF;
113 
114 RETURN @totalRecords;
曹wl的主页 曹wl | 初学一级 | 园豆:169
提问于:2012-07-18 15:58
< >
分享
所有回答(1)
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')

a85295853 | 园豆:2 (初学一级) | 2012-07-18 16:40
清除回答草稿
   您需要登录以后才能回答,未注册用户请先注册