Use Time-Only in query (with parameters)
-
Does anyone know how to use just the time portion of a DateTime object when calling an SQL Query (using SQL Server). I have a field in the database that stores only the Time, and want to do a select based on this field, but it forces me to match the Date portion of the field or it will not work. So, as simple example, if wanted to get all records with time of 9:30am, would use a Command something like:
DateTime timeToGet = new DateTime(1900,1,1,9,30,0); SqlCommand command = new SqlCommand(); command.CommandText = "Select * from MyTable where MyTime = @TimeParam"; SqlParameter parameter = new SqlParameter(); parameter.ParameterName = "TimeParam"; parameter.SqlDbType = SqlDbType.DateTime; parameter.Value = timeToGet; command.Parameters.Add(parameter);This will work fine as long as Date portion of field in database = 1/1/1900. But, if date portion is something else then will not find record. Maybe it would work if could pass it a Time-Only object (instead of DateTime object) but not sure if object like that exists, and if did, not sure parameter object could handle it. If anyone has any ideas, please let me know. Thanks, BK -
Does anyone know how to use just the time portion of a DateTime object when calling an SQL Query (using SQL Server). I have a field in the database that stores only the Time, and want to do a select based on this field, but it forces me to match the Date portion of the field or it will not work. So, as simple example, if wanted to get all records with time of 9:30am, would use a Command something like:
DateTime timeToGet = new DateTime(1900,1,1,9,30,0); SqlCommand command = new SqlCommand(); command.CommandText = "Select * from MyTable where MyTime = @TimeParam"; SqlParameter parameter = new SqlParameter(); parameter.ParameterName = "TimeParam"; parameter.SqlDbType = SqlDbType.DateTime; parameter.Value = timeToGet; command.Parameters.Add(parameter);This will work fine as long as Date portion of field in database = 1/1/1900. But, if date portion is something else then will not find record. Maybe it would work if could pass it a Time-Only object (instead of DateTime object) but not sure if object like that exists, and if did, not sure parameter object could handle it. If anyone has any ideas, please let me know. Thanks, BK