December 1, 2009 at 4:32 am
HI
DECLARE @FromTime VARCHAR(15), @ToTime VARCHAR(15), @userid INT
DECLARE @FTime VARCHAR(15), @TTime VARCHAR(15)
DECLARE @TEMP TABLE (RID INT IDENTITY,FTIME VARCHAR(15),TTIME VARCHAR(15))
SET @FromTIME ='02:40 PM'
SET @ToTime = '02:59 PM'
INSERT INTO @TEMP
SELECT '10:00 AM','11:30 AM'
UNION ALL
SELECT '11:31 AM','01:00 PM'
UNION ALL
SELECT '02:00 PM','03:00 PM'
UNION ALL
SELECT '03:01 PM','04:00 PM'
Select * FROM @TEMP
Here My input time lies here and it should return false
SET @FromTIME ='05:00 PM'
SET @ToTime = '06:59 PM'
Here My input time does not lies here and it should return True
How to get True /False
Thanks
Thanks
Parthi
December 1, 2009 at 5:09 am
Not sure if you are understanding the gravity of the situation or I am not understanding your requirement.
If its time, you cant put it in varchar data type and compare easily and get the result (May be possible but I am not sure)
Anyway here is an attempt to answer your question. Thanks.
DECLARE @FromTime VARCHAR(20), @ToTime VARCHAR(20)
DECLARE @TEMP TABLE (RID INT IDENTITY,FTIME datetime,TTIME datetime)
SET @FromTIME ='2009-12-01 17:00:00'
SET @ToTime = '2009-12-01 18:59:00'
INSERT INTO @TEMP
SELECT '2009-12-01 10:00','2009-12-01 11:30'
UNION ALL
SELECT '2009-12-01 11:31','2009-12-01 13:00'
UNION ALL
SELECT '2009-12-01 14:00','2009-12-01 15:00'
UNION ALL
SELECT '2009-12-01 15:01','2009-12-01 16:00'
IF EXISTS( Select 1 FROM @TEMP
Where FTIME <= @FROMTIME
AND TTIME >= @TOTime) SELECT 'TRUE'
ELSE SELECT 'FALSE'
---------------------------------------------------------------------------------
December 1, 2009 at 5:29 am
Hi this is what the Input i too get from the table i my self convert it and try by using Varchar.But yours always shows FALSE .Not able to get the exact O/P which i needed .If we give from 12:00:00 then also False if we give 18:00:00 then also False but 18:00:00 is not in the table. it is true.
Thanks
Parthi
December 1, 2009 at 5:37 am
What exactly are you trying to do?
What are your expected results based on given parameter?
I tested Nabhas solution and it works just fine.
Is it possible that you still try to set the parameter to time values only?
Maybe you should give us an example based on Nabhas test scenario where you get a different result than expected.
December 1, 2009 at 9:56 pm
DECLARE @FromTime VARCHAR(20), @ToTime VARCHAR(20)
DECLARE @TEMP TABLE (RID INT IDENTITY,FTIME datetime,TTIME datetime)
INSERT INTO @TEMP
SELECT '2009-12-01 10:00','2009-12-01 11:30'
UNION ALL
SELECT '2009-12-01 11:31','2009-12-01 13:00'
UNION ALL
SELECT '2009-12-01 14:00','2009-12-01 15:00'
UNION ALL
SELECT '2009-12-01 15:01','2009-12-01 16:00'
SELECT * FROM @TEMP
SET @FromTIME ='2009-12-01 17:00:00'
SET @ToTime = '2009-12-01 18:59:00'
-- The Given Input does not lies in the table so that i can enter that record
SET @FromTIME ='2009-12-01 12:00:00'
SET @ToTime = '2009-12-01 14:59:00'
-- The Given Input lies in the table so that record should not be enter here
SET @FromTIME ='2009-12-01 16:00:00'
SET @ToTime = '2009-12-01 17:59:00'
-- The Given Input does not lies in the table so that i can enter that record
SET @FromTIME ='2009-12-01 13:00:00'
SET @ToTime = '2009-12-01 14:59:00'
-- The Given Input lies in the table so that record should not be enter here
Main thing is time for that paticular day
Thanks
Parthi
December 1, 2009 at 10:41 pm
Please include the code you are using to determine true/false. You're giving us the variables that you're passing in and the data you're checking it against, but you're not showing us what you're doing to check it, which is likely where the problem lies.
December 1, 2009 at 11:04 pm
parthi-1705 (12/1/2009)
DECLARE @FromTime VARCHAR(20), @ToTime VARCHAR(20)DECLARE @TEMP TABLE (RID INT IDENTITY,FTIME datetime,TTIME datetime)
INSERT INTO @TEMP
SELECT '2009-12-01 10:00','2009-12-01 11:30'
UNION ALL
SELECT '2009-12-01 11:31','2009-12-01 13:00'
UNION ALL
SELECT '2009-12-01 14:00','2009-12-01 15:00'
UNION ALL
SELECT '2009-12-01 15:01','2009-12-01 16:00'
SELECT * FROM @TEMP
SET @FromTIME ='2009-12-01 17:00:00'
SET @ToTime = '2009-12-01 18:59:00'
-- The Given Input does not lies in the table so that i can enter that record
SET @FromTIME ='2009-12-01 12:00:00'
SET @ToTime = '2009-12-01 14:59:00'
-- The Given Input lies in the table so that record should not be enter here
SET @FromTIME ='2009-12-01 16:00:00'
SET @ToTime = '2009-12-01 17:59:00'
-- The Given Input does not lies in the table so that i can enter that record
SET @FromTIME ='2009-12-01 13:00:00'
SET @ToTime = '2009-12-01 14:59:00'
-- The Given Input lies in the table so that record should not be enter here
Main thing is time for that paticular day
IF @FromTIME AND @ToTime Does not exit in the Temp table so that u can enter
BEGIN
SELECT 'CAN ENTER'
END
ELSE
BEGIN
SELECT 'ALREADY '
END
Thanks
Parthi
December 2, 2009 at 12:17 am
'Enter' where? you want to enter into the same temp table? This does not help you?
IF NOT EXISTS( Select 1 FROM @TEMP
Where FTIME <= @FROMTIME
AND TTIME >= @TOTime)
INSERT INTO @TEMP VALUES (@FromTime, @ToTime)
---------------------------------------------------------------------------------
December 2, 2009 at 2:08 am
Hi
I have Attached my Requirement
In Q1 (File Name) The O/P is True it is correct because 12:01:00 to 12:59:00 lies between the Temp table Range of 11:31:00 to 13:00:00
In Q2 (File Name) The O/P is False it is incorrect because 13:01:00 to 14:59:00 lies between the Temp table Range of 14:00:00 to 15:00:00
In Q3 (File Name) The O/P is False it is incorrect because 17:00:00 to 18:59:00 is not in the Table so it should return 'True' but in our case it is showing False
Thanks
Parthi
December 2, 2009 at 3:00 am
parthi-1705 (12/2/2009)
HiI have Attached my Requirement
In Q1 (File Name) The O/P is True it is correct because 12:01:00 to 12:59:00 lies between the Temp table Range of 11:31:00 to 13:00:00
In Q2 (File Name) The O/P is False it is incorrect because 13:01:00 to 14:59:00 lies between the Temp table Range of 14:00:00 to 15:00:00
In Q3 (File Name) The O/P is False it is incorrect because 17:00:00 to 18:59:00 is not in the Table so it should return 'True' but in our case it is showing False
Your Q1 sample is nowhere in your sample data. How could we guess?? Anyhow, using Nabhas query it will return TRUE.
Your Q2 sample is nowhere in your sample data either. But it will return FALSE.
Q3 returns false because the query is checking for existing ranges.
To bring it to a point where we can help you in an efficient way:
Please read and follow the first link in my signature and provide data as described.
You have been posting several requests by now so I can assume you know how to ask for help.
To me it looks like you should sit back for a moment and try to put together what aou're really looking for. I'll step back as well until I see a requirement supported by ready to use data including expected result in the way described.
December 2, 2009 at 3:12 am
parthi-1705 (12/2/2009)
In Q2 (File Name) The O/P is False it is incorrect because 13:01:00 to 14:59:00 lies between the Temp table Range of 14:00:00 to 15:00:00
If we take it as a 'range' the 'range' is not there in temp table but yes there is an 'overlap' but not complete 'range'. How can you say 13:01:00 falls in the range starting from 14:00:00 ?? 🙂
I am with Lutz, will step back for now and allow you to form your question properly and come back!
---------------------------------------------------------------------------------
December 2, 2009 at 3:57 am
Hello friends Thanks for your valuable suggestion i got my solutions
here it is You just copy this and Run you will find my Requirement :
DECLARE @FromTime VARCHAR(20), @ToTime VARCHAR(20)
DECLARE @TEMP1 TABLE (RID INT IDENTITY,FTIME VARCHAR(15),TTIME VARCHAR(15))
DECLARE @TEMP TABLE (RID INT IDENTITY,FTIME VARCHAR(20),TTIME VARCHAR(20))
DECLARE @Flg1 INT ,@Flg2 INT
SET @Flg1=0 SET @Flg2=0
INSERT INTO @TEMP
SELECT '2009-12-01 10:00:00','2009-12-01 11:30:00'
UNION ALL
SELECT '2009-12-01 11:31:00','2009-12-01 13:00:00'
UNION ALL
SELECT '2009-12-01 14:00:00','2009-12-01 15:00:00'
UNION ALL
SELECT '2009-12-01 15:01:00','2009-12-01 16:00:00'
SET @FromTIME ='2009-12-01 15:00:00'
SET @ToTime = '2009-12-01 18:59:00'
-- Select* FROM @TEMP
SET @FromTIME=RIGHT(REPLACE(@FromTIME,':',''),7)
SET @ToTime=RIGHT(REPLACE(@ToTime,':',''),7)
INSERT INTO @Temp1
Select RIGHT(REPLACE(FTIME,':',''),7),RIGHT(REPLACE(TTIME,':',''),7) FROM @TEMP
Select @Flg1=1 from @Temp1 WHERE @FromTIME BETWEEN FTIME AND TTIME
Select @Flg2=1 from @Temp1 WHERE @ToTime BETWEEN FTIME AND TTIME
IF @Flg1<>0 OR @Flg2<>0
BEGIN
SELECT 'ALREADY'
END
ELSE
BEGIN
SELECT 'CAN'
END
DELETE FROM @Temp1
SET @Flg1=0 SET @Flg2=0
SET @FromTIME ='2009-12-01 17:00:00'
SET @ToTime = '2009-12-01 18:59:00'
INSERT INTO @Temp1
Select RIGHT(REPLACE(FTIME,':',''),7),RIGHT(REPLACE(TTIME,':',''),7) FROM @TEMP
Select @Flg1=1 from @Temp1 WHERE @FromTIME BETWEEN FTIME AND TTIME
Select @Flg2=1 from @Temp1 WHERE @ToTime BETWEEN FTIME AND TTIME
IF @Flg1<>0 OR @Flg2<>0
BEGIN
SELECT 'ALREADY'
END
ELSE
BEGIN
SELECT 'CAN'
END
if it is possible for you peoples to minmize the above soltions it is accepted
thanks for your valuable time spending on this
Thanks
Parthi
December 2, 2009 at 5:22 am
You could declare two variables holding min and max time
declare @a datetime,
@b-2 datetime
select @a= min(FTIME),@b=max(TTIME) from @TEMP
select case
when cast(@FromTIME as datetime) BETWEEN @a AND @b-2
or cast(@ToTIME as datetime) BETWEEN @a AND @b-2
then 'already'
else 'can'
end
This should work as long as you keep @FromTIME AND @ToTIME as datetime values.
I'd consider the change of a values (relative) datatype as a bad habit...
In your example you're changing a datetime value to an integer value:
SET @FromTIME ='2009-12-01 15:00:00'
SET @ToTime = '2009-12-01 18:59:00'
SET @FromTIME=RIGHT(REPLACE(@FromTIME,':',''),7)
SET @ToTime=RIGHT(REPLACE(@ToTime,':',''),7)
If you need to do so, you should use separate variables.
December 2, 2009 at 5:45 am
lmu92 (12/2/2009)
You could declare two variables holding min and max time
declare @a datetime,@b-2 datetime
select @a= min(FTIME),@b=max(TTIME) from @TEMP
select case
when cast(@FromTIME as datetime) BETWEEN @a AND @b-2
or cast(@ToTIME as datetime) BETWEEN @a AND @b-2
then 'already'
else 'can'
end
This should work as long as you keep @FromTIME AND @ToTIME as datetime values.
I'd consider the change of a values (relative) datatype as a bad habit...
In your example you're changing a datetime value to an integer value:
SET @FromTIME ='2009-12-01 15:00:00'SET @ToTime = '2009-12-01 18:59:00'
SET @FromTIME=RIGHT(REPLACE(@FromTIME,':',''),7)
SET @ToTime=RIGHT(REPLACE(@ToTime,':',''),7)
If you need to do so, you should use separate variables.
-----------------------------------------------------------
Hi try this
I too accept change of datatype is not Good
DECLARE @FromTime VARCHAR(20), @ToTime VARCHAR(20)
DECLARE @TEMP1 TABLE (RID INT IDENTITY,FTIME VARCHAR(15),TTIME VARCHAR(15))
DECLARE @TEMP TABLE (RID INT IDENTITY,FTIME VARCHAR(20),TTIME VARCHAR(20))
DECLARE @Flg1 INT ,@Flg2 INT
SET @Flg1=0 SET @Flg2=0
INSERT INTO @TEMP
SELECT '2009-12-01 10:00:00','2009-12-01 11:30:00'
UNION ALL
SELECT '2009-12-01 11:31:00','2009-12-01 13:00:00'
UNION ALL
SELECT '2009-12-01 14:00:00','2009-12-01 15:00:00'
UNION ALL
SELECT '2009-12-01 15:01:00','2009-12-01 16:00:00'
SET @FromTIME ='2009-12-01 15:01:00'
SET @ToTime = '2009-12-01 15:59:00'
DECLARE @a datetime,@b datetime
select @a= min(FTIME),@b=max(TTIME) from @TEMP
select @a,@b,@FromTIME,@ToTime
select case
when cast(@a as datetime) BETWEEN @FromTIME AND @ToTIME
or cast(@b as datetime) BETWEEN @FromTIME AND @ToTIME
then 'already'
else 'can'
end
SET @FromTIME=RIGHT(REPLACE(@FromTIME,':',''),7)
SET @ToTime=RIGHT(REPLACE(@ToTime,':',''),7)
INSERT INTO @Temp1
Select RIGHT(REPLACE(FTIME,':',''),7),RIGHT(REPLACE(TTIME,':',''),7) FROM @TEMP
Select @Flg1=1 from @Temp1 WHERE @FromTIME BETWEEN FTIME AND TTIME
Select @Flg2=1 from @Temp1 WHERE @ToTime BETWEEN FTIME AND TTIME
IF @Flg1<>0 OR @Flg2<>0
BEGIN
SELECT 'ALREADY'
END
ELSE
BEGIN
SELECT 'CAN'
END
Your Answer is Showing CAN
see my answer it is showing ALREADY
actually i need this one only ALREADY, it is coming for me because [15:01:00-15:59:00]time lies in the table so i must not able to enter any data it should ALREADY
Thanks
Thanks
Parthi
December 2, 2009 at 6:09 am
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply