使用Visual Basic查询sql数据库的时间

I want to display max time and min time for a day in grid control using visual basic from sql database. table column are:-

我想使用sql数据库中的visual basic显示网格控制中一天的最大时间和最短时间。表栏是: -

UserID,UserName,Date,Time
1 Shanks 30/1/2009 10:11:22
1 Shanks 30/1/2009 10:15:22
1 Shanks 30/1/2009 12:15:22
1 Shanks 30/1/2009 13:15:22

output must be in grid

输出必须在网格中

1 Shanks 30/1/2009 10:11:22 13:15:22

1 个解决方案

#1

I'm going to assume your table structure is something like this

我假设你的表结构是这样的

CREATE TABLE mytable (UserID integer, UserName varchar(20), [Date] datetime, [Time] varchar(8))

your time is stored as a varchar field because there is no time type in sql2005. This will display the min and max time per each user and date. There are two options, the first if your times are HH:MM:SS then you can just use the convert function. The second shows you an example of parsing it and building the date yourself.

您的时间存储为varchar字段,因为sql2005中没有时间类型。这将显示每个用户和日期的最小和最大时间。有两个选项,第一个是你的时间是HH:MM:SS然后你可以使用转换功能。第二个显示了一个解析它并自己构建日期的示例。

SELECT
   UserID,
   UserName,
   [Date],
   CONVERT(varchar, MIN(CONVERT(datetime, [Time], 108)), 108),
   CONVERT(varchar, MAX(CONVERT(datetime, [Time], 108)), 108)
FROM mytable
GROUP BY UserID, UserName, [Date]
ORDER BY UserID, [Date]


SELECT
   UserID,
   UserName,
   [Date],
   CONVERT(varchar, MIN(DATEADD(second, CAST(SUBSTRING(Time, 7, 2) AS integer), DATEADD(minute, CAST(SUBSTRING(Time, 4, 2) AS integer), DATEADD(hour, CAST(SUBSTRING(Time, 1, 2) AS integer), 0)))), 108),
   CONVERT(varchar, MAX(DATEADD(second, CAST(SUBSTRING(Time, 7, 2) AS integer), DATEADD(minute, CAST(SUBSTRING(Time, 4, 2) AS integer), DATEADD(hour, CAST(SUBSTRING(Time, 1, 2) AS integer), 0)))), 108)
FROM mytable
GROUP BY UserID, UserName, [Date]
ORDER BY UserID, [Date]