time format - SQL Server Forums

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

SQL Server 2000 Forums

SQL Server Development (2000)

time format

Author

Topic

cool.mugil
Starting Member

32 Posts

Posted - 2008-04-09 : 03:01:17

hai,
my table format is as follows
S.No Intime OutTime
1 22:00 03:00
2 03:05 06:00
i want to select the record whose time is between 00:10.
please help me.
thanks in advance.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-09 : 03:03:56

BETWEEN what?

E 12°55'05.25"
N 56°04'39.16"

mailbalajijagan@gmail.com
Starting Member

2 Posts

Posted - 2008-04-09 : 03:30:28

Hi you can try this.

Select SNo from <TblName> where datediff(mi,outtime,Intime)<=10

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2008-04-09 : 03:47:40

i was assuming OP wanted to know where 00:10 was between intime and outtime, rather than the difference between the 2 being less than 10 minutes.

but what about where it goes over midnight? and what datatypes? need more info first i think

Em

cool.mugil
Starting Member

32 Posts

Posted - 2008-04-09 : 04:03:18

hai,
my table format is as follows
S.No Intime OutTime
1 22:00 03:00
2 03:05 06:00
i want to select the record whose 00:10 was between intime and outtime.intime and outtime datatypes where char.

please help me.
thanks in advance.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-09 : 04:28:44

You mean which records are at most 10 minutes apart regarding Intime and Outtime?

DECLARE	@Sample TABLE (No TINYINT, InTime CHAR(5), OutTime CHAR(5))

INSERT	@Sample
SELECT	1, '22:00', '03:00' UNION ALL
SELECT	2, '03:05', '06:00' UNION ALL
SELECT	3, '23:57', '00:04'

SELECT	*
FROM	(
		SELECT	CAST(InTime AS DATETIME) AS InTime,
			CASE
				WHEN OutTime < InTime THEN DATEADD(DAY, 1, CAST(OutTime AS DATETIME))
				ELSE CAST(OutTime AS DATETIME)
			END AS OutTime 
		FROM	@Sample
	) AS d
WHERE	DATEDIFF(MINUTE, InTime, OutTime) BETWEEN 0 AND 10

E 12°55'05.25"
N 56°04'39.16"

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2008-04-09 : 05:03:12

i interpreted the question differently. i though OP meant more like...

taking Peso's query...
WHERE dateadd(day,1,cast('00:00' as datetime)) between intime and outtime

i.e. 00:10 meaning 10 past midnight, rather than 10 minutes?
Em

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2008-04-09 : 05:12:41

actually if that is the case you'd have to handle the midnight cross over for the that too i guess...


DECLARE	@Sample TABLE (No TINYINT, InTime CHAR(5), OutTime CHAR(5))


INSERT	@Sample
SELECT	1, '22:00', '03:00' UNION ALL
SELECT	2, '03:05', '06:00' UNION ALL
SELECT	3, '23:57', '00:04'

SELECT	*
FROM	(
		SELECT	CAST(InTime AS DATETIME) AS InTime,
			CASE
				WHEN OutTime < InTime THEN DATEADD(DAY, 1, CAST(OutTime AS DATETIME))
				ELSE CAST(OutTime AS DATETIME)
			END AS OutTime, 
			CASE
				WHEN OutTime < InTime THEN DATEADD(DAY, 1, cast('00:10' as datetime))
				ELSE cast('00:10' as datetime)
			END AS [time] 
			
		FROM	@Sample
	) AS d
WHERE	[time] between intime and outtime

cool.mugil
Starting Member

32 Posts

Posted - 2008-04-09 : 05:23:44

great,this works for me.
thanks

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2008-04-09 : 05:46:58

What about say '00:09' - '00:01'?

Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2008-04-09 : 05:52:41

DOH! ...my brain's melting now. i need more coffee. of course we still don't actually know what the OP wanted?

Em

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2008-04-09 : 05:52:43

Maybe this...

select * from @Sample where '00:10' between InTime and OutTime or (InTime > OutTime and (InTime < '00:10' or OutTime > '00:10'))

Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2008-04-09 : 05:53:50

quote:
Originally posted by elancaster

DOH! ...my brain's melting now. i need more coffee. of course we still don't actually know what the OP wanted?

Em

Very true...

Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources