Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Convert Char field to time for BETWEEN search?

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 later

if 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 MVP
http://visakhm.blogspot.com/
Go to Top of Page

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
Go to Top of Page

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 MVP
http://visakhm.blogspot.com/

Go to Top of Page

CLEE25
Starting Member

14 Posts

Posted - 2012-02-24 : 08:30:10
They exist as CHAR fields.
Go to Top of Page

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"
Go to Top of Page

CLEE25
Starting Member

14 Posts

Posted - 2012-02-24 : 08:56:44
09:00
16:00
12:00
14:30

etc, etc.
Go to Top of Page

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 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION
Go to Top of Page

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 MVP
http://visakhm.blogspot.com/

Go to Top of Page

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.
Go to Top of Page

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 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.
Go to Top of Page

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"
Go to Top of Page
   

- Advertisement -