SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Convert Char field to time for BETWEEN search?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

CLEE25
Starting Member

14 Posts

Posted - 02/23/2012 :  17:27:47  Show Profile  Reply with Quote
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
52317 Posts

Posted - 02/23/2012 :  18:01:16  Show Profile  Reply with Quote
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
Go to Top of Page

CLEE25
Starting Member

14 Posts

Posted - 02/23/2012 :  19:15:37  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 02/23/2012 :  20:59:38  Show Profile  Reply with Quote
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 - 02/24/2012 :  08:30:10  Show Profile  Reply with Quote
They exist as CHAR fields.
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30213 Posts

Posted - 02/24/2012 :  08:32:29  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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 - 02/24/2012 :  08:56:44  Show Profile  Reply with Quote
09:00
16:00
12:00
14:30

etc, etc.
Go to Top of Page

Transact Charlie
Flowing Fount of Yak Knowledge

United Kingdom
3451 Posts

Posted - 02/24/2012 :  09:14:06  Show Profile  Visit Transact Charlie's Homepage  Reply with Quote

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

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 02/24/2012 :  10:02:42  Show Profile  Reply with Quote
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 - 02/24/2012 :  17:50:26  Show Profile  Reply with Quote
[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 - 02/24/2012 :  18:13:07  Show Profile  Reply with Quote
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

Sweden
30213 Posts

Posted - 02/24/2012 :  19:54:20  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000