在平时的开发中,经常会用到基于某个时间段的查询,比如选择开始日期、结束日期.(yyyy-MM-dd格式),而在数据库表中该列存储的格式却是(yyyy-MM-dd HH:MM:SS)格式,这样就要用到如下转换
sbSql.Append(string.Format(" AND Convert(Char(10),RegisterDate,120)<='{0}'", endRegisterDate));
1
public DataTable GetOvertureDocInfoList(string boName, int isSend, string startRegisterDate, string endRegisterDate)
2
{
3
try
4
{
5
string strSql = "SELECT * FROM GovInfo_OvertureDocument WHERE '1'='1' ";
6
StringBuilder sbSql = new StringBuilder(strSql);
7
8
if (isSend > -1)
9
{
10
sbSql.Append(string.Format(" AND IsSend={0}",isSend));
11
}
12
13
if (startRegisterDate != "")
14
{
15
sbSql.Append(string.Format(" AND Convert(Char(10),RegisterDate,120)>='{0}'", startRegisterDate));
16
}
17
if (endRegisterDate != "")
18
{
19
sbSql.Append(string.Format(" AND Convert(Char(10),RegisterDate,120)<='{0}'", endRegisterDate));
20
}
21
22
//if (status != "")
23
//{
24
// sbSql.Append(string.Format("AND Status={0}", status));
25
//}
26
27
sbSql.Append(" ORDER BY registerdate DESC");
28
29
SqlDBAccess dbaccess = new SqlDBAccess();
30
DataSet ds = new DataSet();
31
ds = dbaccess.ExecuteDataset(boName, CommandType.Text, sbSql.ToString());
32
DataTable dt = ds.Tables[0];
33
34
return dt;
35
}
36
catch (Exception e)
37
{
38
LogUtil.Log.Error(this, e);
39
throw e;
40
}
41
}

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41
