| Author |
 Topic  |
|
|
CLEE25
Starting Member
13 Posts |
 Posted - 02/23/2012 : 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
India
47094 Posts |
Posted - 02/23/2012 : 18:01:16
|
Select * from positions where <usertime> BETWEEN CONVERT(time,StartTime) AND CONVERT(time,EndTime)
if you're using sql 2008 or later
if not use like
Select * from positions where <usertime> >= DATEADD(dd,-DATEDIFF(dd,0,StartTime),StartTime) AND <usertime> < =DATEADD(dd,-DATEDIFF(dd,0,EndTime),EndTime)
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
Edited by - visakh16 on 02/23/2012 18:01:43 |
 |
|
|
CLEE25
Starting Member
13 Posts |
Posted - 02/23/2012 : 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 |
Edited by - CLEE25 on 02/23/2012 19:15:58 |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47094 Posts |
Posted - 02/23/2012 : 20:59:38
|
how are values existing in StartTime and EndTime fields?
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
CLEE25
Starting Member
13 Posts |
Posted - 02/24/2012 : 08:30:10
|
| They exist as CHAR fields. |
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29138 Posts |
Posted - 02/24/2012 : 08:32:29
|
Can you post some examples from your table?
N 56°04'39.26"
E 12°55'05.63" |
|
|
|
CLEE25
Starting Member
13 Posts |
Posted - 02/24/2012 : 08:56:44
|
09:00
16:00
12:00
14:30
etc, etc. |
|
|
|
Transact Charlie
Flowing Fount of Yak Knowledge
United Kingdom
3437 Posts |
Posted - 02/24/2012 : 09:14:06
|
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
Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47094 Posts |
Posted - 02/24/2012 : 10:02:42
|
quote:
Originally posted by CLEE25
They exist as CHAR fields.
what about uservalue?
how are you passing it?
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
CLEE25
Starting Member
13 Posts |
Posted - 02/24/2012 : 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
13 Posts |
Posted - 02/24/2012 : 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 thetime
from dbo.Positions
SELECT Convert(datetime, '1/1/2000 '+cStartTime) as thetime
from dbo.Positions
SELECT CONVERT(DATETIME,cStartTime,108) as thetime
from dbo.Positions
SELECT convert(datetime,cast(cStartTime as DATETIME),108) as thetime
from dbo.Positions
Can't seem any of them to take.
|
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29138 Posts |
Posted - 02/24/2012 : 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" |
|
|
| |
Topic |
|
Convert Char field to time for BETWEEN search?
