Author |
Topic |
CLEE25
Starting Member
14 Posts |
Posted - 2012-02-23 : 17:27:47
|
So I inherited a database that has a table called positions with a Start Time and an End Time.Unfortunately, these "times" are stored in a CHAR field, not a time/date field. I need to run a query that looks for a user entered time BETWEEN the start and end times and pull the records. Something like:Select * from positions where <usertime> BETWEEN CONVERT(datetime,StartTime) AND CONVERT(datetime,EndTime)However, I don't want 'dates' just the time part. I have tried Datepart as well, but just can't seem to make it work. Any ideas appreciated. |
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-02-23 : 18:01:16
|
[code]Select * from positions where <usertime> BETWEEN CONVERT(time,StartTime) AND CONVERT(time,EndTime)[/code]if you're using sql 2008 or laterif not use like [code]Select * from positions where <usertime> >= DATEADD(dd,-DATEDIFF(dd,0,StartTime),StartTime) AND <usertime> < =DATEADD(dd,-DATEDIFF(dd,0,EndTime),EndTime)[/code]------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
CLEE25
Starting Member
14 Posts |
Posted - 2012-02-23 : 19:15:37
|
Thanks for the reply, but I am getting the following error:Conversion failed when converting datetime from character string.And using SQL 2005 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-02-23 : 20:59:38
|
how are values existing in StartTime and EndTime fields?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
CLEE25
Starting Member
14 Posts |
Posted - 2012-02-24 : 08:30:10
|
They exist as CHAR fields. |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2012-02-24 : 08:32:29
|
Can you post some examples from your table? N 56°04'39.26"E 12°55'05.63" |
|
|
CLEE25
Starting Member
14 Posts |
Posted - 2012-02-24 : 08:56:44
|
09:0016:0012:0014:30etc, etc. |
|
|
Transact Charlie
Master Smack Fu Yak Hacker
3451 Posts |
Posted - 2012-02-24 : 09:14:06
|
[code]DECLARE @sample TABLE ([timeChar] CHAR(5))INSERT @sample VALUES ('09:00'), ('16:00'), ('12:00'), ('14:30')SELECT [timeChar] , 60 * CAST(SUBSTRING([timeChar], 1, 2) AS INT) + CAST(SUBSTRING([timeChar], 4,2) AS INT) AS [Minutes]FROM @sample[/code]Charlie===============================================================Msg 3903, Level 16, State 1, Line 1736The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-02-24 : 10:02:42
|
quote: Originally posted by CLEE25 They exist as CHAR fields.
what about uservalue?how are you passing it?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
CLEE25
Starting Member
14 Posts |
Posted - 2012-02-24 : 17:50:26
|
[quote]Originally posted by Transact Charlie
DECLARE @sample TABLE ([timeChar] CHAR(5))INSERT @sample VALUES ('09:00'), ('16:00'), ('12:00'), ('14:30')SELECT [timeChar] , 60 * CAST(SUBSTRING([timeChar], 1, 2) AS INT) + CAST(SUBSTRING([timeChar], 4,2) AS INT) AS [Minutes]FROM @sample Thanks Charlie, but I believe this function would only give me the minutes between the two times. I am looking to see if a Time Field is between two times. |
|
|
CLEE25
Starting Member
14 Posts |
Posted - 2012-02-24 : 18:13:07
|
quote: Originally posted by visakh16 [quote]Originally posted by CLEE25 They exist as CHAR fields.
what about uservalue?how are you passing it?Hey V, Haven't even gotten that far. Just trying to pull the data out with conversion. Tried:SELECT Cast('1/1/2000 '+cStartTime as Datetime) as thetimefrom dbo.PositionsSELECT Convert(datetime, '1/1/2000 '+cStartTime) as thetimefrom dbo.PositionsSELECT CONVERT(DATETIME,cStartTime,108) as thetimefrom dbo.PositionsSELECT convert(datetime,cast(cStartTime as DATETIME),108) as thetimefrom dbo.PositionsCan't seem any of them to take. |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2012-02-24 : 19:54:20
|
You already have the times in ISO format, so you bother converting?DECLARE @UserTime VARCHAR(5) = '11:00'SELECT * FROM dbo.Table1 WHERE @UserTime BETWEEN StartTime AND EndTime N 56°04'39.26"E 12°55'05.63" |
|
|
|