SQL Query从特定时间范围中选择数据 [英] SQL Query to pick data from a certain time range

问题描述

我的数据库中有两个字段。

1）Invoice_Date和Invoice_Time



他们从我的发票中记录日期和时间。



我想在两个查询下选择数据：

1）使用From_Time DateTimePicker和To_Time DateTimePicker（它们显示为19:20:18 PM）

这里我想要在13:10:12 PM到18:10:20 PM之间选择数据，无论它是什么日期。



2）其次我想要数据对于特定的数据范围（从datatimepickers）到特定的时间范围（时间datatimepickers）



请告知如何操作。



我试过：

 @ Time1之间的InvTime 和 @ Time2 

但它没有生成任何数据，而数据在给定的时间范围内。



请帮忙。

谢谢

解决方案

你的第一个要求是只选择基于时间没有r到了约会。以下示例将每个DateTime数据类型转换为Time数据类型，以便仅比较时间值。解决方案假定DateTimePicker返回格式为hh：mm：ss tt或HH：mm：ss的时间。

 Dim strSQL As String =选择col1，col2，col3，来自table1的InvTime，其中CAST（@ Time1 AS Time）和CAST（@ Time2 AS Time）之间的CAST（InvTime AS Time）; 
 Dim obCommand As SqlCommand = New SqlCommand（strSQL，cn）
 Dim obParm As New SqlParameter 
 obParm.SqlDbType = SqlDbType.DateTime 
 obParm.Direction = ParameterDirection.Input 
 obParm.ParameterName =@ Time1
 obParm.Value = CDate（From_Time_DateTimePicker.Value）
 obCommand.Parameters.Add（obParm）
 obParm = New SqlParameter 
 obParm.SqlDbType = SqlDbType.DateTime 
 obParm.Direction = ParameterDirection.Input 
 obParm.ParameterName =@ Time2
 obParm.Value = CDate（To_Time_DateTimePicker.Value）
 obCommand.Parameters.Add （obParm）
 obParm = Nothing 
尝试
 rs = obCommand.ExecuteReader 
 Catch myException As SqlException 
调用ShowSQLException（myException）
结束尝试
 

您的第二个要求是使用Date DateTimePicker中的日期和TimeTimeTimePicker中的时间。解决方案假定Date DateTimePicker返回MM / dd / yyyy格式，Time DateTimePicker返回格式为hh：mm：ss tt或HH：mm：ss的时间。

 Dim strSQL As String =从table1中选择col1，col2，col3，InvTime，其中@ DateTime1和@ DateTime2之间的InvTime; 
 Dim obCommand As SqlCommand = New SqlCommand（strSQL，cn）
 Dim obParm As New SqlParameter 
 obParm.SqlDbType = SqlDbType.DateTime 
 obParm.Direction = ParameterDirection.Input 
 obParm.ParameterName =@ DateTime1
 obParm.Value = CDate（From_Date_DateTimePicker.Value&& From_Time_DateTimePicker.Value）
 obCommand.Parameters.Add（obParm）
 obParm =新的SqlParameter 
 obParm.SqlDbType = SqlDbType.DateTime 
 obParm.Direction = ParameterDirection.Input 
 obParm.ParameterName =@ DateTime2
 obParm.Value = CDate（To_Date_DateTimePicker.Value& amp; ;& To_Time_DateTimePicker.Value）
 obCommand.Parameters.Add（obParm）
 obParm = Nothing 
尝试
 rs = obCommand.ExecuteReader 
 Catch myException As SqlException 
调用ShowSQLException（myException）
结束尝试
 

访问这里...





http://social.msdn.microsoft.com/Forums/sqlserver/en-US/ea13429e-8044-492c-b0b3-1d72abfe6e00/sql -date-range-select [ ^ ]





或



http://www.sqlservercentral.com/Forums /Topic1251207-391-1.aspx [ ^ ]

Hi,
I have two fields in my DB.
1) Invoice_Date and Invoice_Time

They recored date and time from my invoices.

I want to pick data under two queries:
1)Using From_Time DateTimePicker and To_Time DateTimePicker (they display as 19:20:18 PM)
Here I want to pick data for say between 13:10:12 PM to 18:10:20 PM, no matter what date it is.

2) Secondly I want data for a specific data range (from datatimepickers) to specific time range (time datatimepickers)

Please advise how to do it.

I tried:

Where InvTime between @Time1 and @Time2

But it did not generate any data, while data is there for the given time range.

Please help.
Thanks

解决方案

Your first requirement was to select only based on time without regard to the date. The following example casts each DateTime data type as a Time data type so that only the time value is compared. Solution assumes that DateTimePicker returns a time of the format hh:mm:ss tt or HH:mm:ss.

Dim strSQL As String = "Select col1,col2,col3,InvTime from table1 where CAST(InvTime AS Time) between CAST(@Time1 AS Time) AND CAST(@Time2 AS Time);"
Dim obCommand As SqlCommand = New SqlCommand(strSQL, cn)
Dim obParm As New SqlParameter
obParm.SqlDbType = SqlDbType.DateTime
obParm.Direction = ParameterDirection.Input
obParm.ParameterName = "@Time1"
obParm.Value = CDate(From_Time_DateTimePicker.Value)
obCommand.Parameters.Add(obParm)
obParm = New SqlParameter
obParm.SqlDbType = SqlDbType.DateTime
obParm.Direction = ParameterDirection.Input
obParm.ParameterName = "@Time2"
obParm.Value = CDate(To_Time_DateTimePicker.Value)
obCommand.Parameters.Add(obParm)
obParm = Nothing
Try
    rs = obCommand.ExecuteReader
Catch myException As SqlException
    Call ShowSQLException(myException)
End Try

Your second requirement was to use dates from a Date DateTimePicker and times from a Time DateTimePicker. Solution assumes that Date DateTimePicker returns MM/dd/yyyy format and Time DateTimePicker returns a time of the format hh:mm:ss tt or HH:mm:ss.

Dim strSQL As String = "Select col1,col2,col3,InvTime from table1 where InvTime between @DateTime1 AND @DateTime2;"
Dim obCommand As SqlCommand = New SqlCommand(strSQL, cn)
Dim obParm As New SqlParameter
obParm.SqlDbType = SqlDbType.DateTime
obParm.Direction = ParameterDirection.Input
obParm.ParameterName = "@DateTime1"
obParm.Value = CDate(From_Date_DateTimePicker.Value & " " & From_Time_DateTimePicker.Value)
obCommand.Parameters.Add(obParm)
obParm = New SqlParameter
obParm.SqlDbType = SqlDbType.DateTime
obParm.Direction = ParameterDirection.Input
obParm.ParameterName = "@DateTime2"
obParm.Value = CDate(To_Date_DateTimePicker.Value & " " & To_Time_DateTimePicker.Value)
obCommand.Parameters.Add(obParm)
obParm = Nothing
Try
    rs = obCommand.ExecuteReader
Catch myException As SqlException
    Call ShowSQLException(myException)
End Try

visit here...


http://social.msdn.microsoft.com/Forums/sqlserver/en-US/ea13429e-8044-492c-b0b3-1d72abfe6e00/sql-date-range-select[^]


or

http://www.sqlservercentral.com/Forums/Topic1251207-391-1.aspx[^]

这篇关于SQL Query从特定时间范围中选择数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！