search between tow dates Problem
-
need help
i dont Catch the dates between tow dates search !!!
this is my SQL QUERY !!!
-------------------------------
SELECT TOP 100 PERCENT *
FROM dbo.notmdb_tb
WHERE (tarih >= CONVERT(varchar, DATEADD([day], - 1, GETDATE()), 112) + ' 16:00:00') AND (tarihb <= CONVERT(varchar, DATEADD([day], 0, GETDATE()), 112)
+ ' 16:00:00') OR
(tarih <= CONVERT(varchar, DATEADD([day], - 1, GETDATE()), 112) + ' 16:00:00') AND (tarihb >= CONVERT(varchar, DATEADD([day], 0, GETDATE()), 112)
+ ' 16:00:00') OR
(tarih <= CONVERT(varchar, DATEADD([day], 0, GETDATE()), 112) + ' 16:00:00') AND (tarihb >= CONVERT(varchar, DATEADD([day], 0, GETDATE()), 112)
+ ' 16:00:00') OR
(tarih >= CONVERT(varchar, DATEADD([day], - 1, GETDATE()), 112) + ' 16:00:00') AND (tarih <= CONVERT(varchar, DATEADD([day], - 1, GETDATE()), 112)
+ ' 16:00:00')
ORDER BY unit
--------------------------------------------------
i can not Catch the dates
tarihb=01/11/2005 06:00:00
tarih=01/11/2005 20:00:00
What is the problem
thnks
ilan
-
What data type is the column tarihb? I would also use (tarih >= Convert(DateTime, CONVERT(varchar, DATEADD([day], - 1, GETDATE()), 112) + ' 16:00:00')) AND (tarihb <= Convert(DateTime, CONVERT(varchar, DATEADD([day], 0, GETDATE()), 112)) to insure a datetime value is being used for comparisons!
-
tarih = data Type datetime
length = 8
tarihb = data Type datetime
length = 8
thnks
-
So Have you given it a shot with the implicit conversion of the varchar back to a datetime?
-
Can you provide a few sample rows of data with dates that you won't find with your query, but you expect the query to find them?
Could you also elaborate on the intentions of your query?
Lastly, could you please state which of the columns is intended to be startdate and enddate? It may be a possibility that they have been reversed, and thus you don't get the result you expect..?
/Kenneth
-
can you help me i dont understand what to do ??
and do you have idea how to improve it
because i have problem with it !!
thnks
-
i wont to search between tow dates Fields !!
the date is
Getdate()-1 16:00 and Getdate() 16:00
but between tow date fields
start date =datea
end date =dateb
1 datea = Getdate()-1 16:00
2 dateb = Getdate() 16:00
thnks
-
You don't have any 'datea' and 'dateb' - you have tarih and tarib
Which of them is start and end?
In your query below, explain in simple words your intentions with the WHERE clause - that is in what situations should tarih and tarib dates evaluate to something that you want to return?
SELECT TOP 100 PERCENT *
FROM dbo.notmdb_tb
WHERE (tarih >= CONVERT(varchar, DATEADD([day], - 1, GETDATE()), 112) + ' 16:00:00') AND (tarihb <= CONVERT(varchar, DATEADD([day], 0, GETDATE()), 112)
+ ' 16:00:00') OR
(tarih <= CONVERT(varchar, DATEADD([day], - 1, GETDATE()), 112) + ' 16:00:00') AND (tarihb >= CONVERT(varchar, DATEADD([day], 0, GETDATE()), 112)
+ ' 16:00:00') OR
(tarih <= CONVERT(varchar, DATEADD([day], 0, GETDATE()), 112) + ' 16:00:00') AND (tarihb >= CONVERT(varchar, DATEADD([day], 0, GETDATE()), 112)
+ ' 16:00:00') OR
(tarih >= CONVERT(varchar, DATEADD([day], - 1, GETDATE()), 112) + ' 16:00:00') AND (tarih <= CONVERT(varchar, DATEADD([day], - 1, GETDATE()), 112)
+ ' 16:00:00')
ORDER BY unit
/Kenneth
-
ok i wont to search like this
SELECT TOP 100 PERCENT *
FROM dbo.MAINB
WHERE (tarih '01/11/2005 16:00:00') BETWEEN (tarihB '01/11/2005 20:00:00')
AND (meosar LIKE '1')
the start day is =tarih
the end date is = tarihB
thank you
-
Try this:
SELECT TOP 100 PERCENT *
FROM dbo.MAINB
WHERE DateDiff(ss, tarih, '01/11/2005 16:00:00') <=0 And DateDiff(ss, tarihB '01/11/2005 20:00:00') >= 0
AND (meosar LIKE '1')
This will give you records that have 'tarih' a start date greater than or eqaul to '01/11/2005 16:00:00' and a end date 'tarihB' less than or equal to '01/11/2005 20:00:00'.
Test (The second paramter simulates tarih and tarib):
select DateDiff(ss, '01/11/2005 16:00:00', '01/11/2005 16:00:00') returns 0
select DateDiff(ss, '01/11/2005 16:00:01', '01/11/2005 16:00:00') returns -1
-
I don't understand your problem. You say you can't 'CATCH' these dates:
tarihb=01/11/2005 06:00:00
tarih=01/11/2005 20:00:00
But you say your script should catch date/times between:
(tarih '01/11/2005 16:00:00') BETWEEN (tarihB '01/11/2005 20:00:00')
01/11/2005 06:00:00 and 20:00:00 are NOT BETWEEN those two values.
-SQLBill
-
Thanks
But i have problem
thet i must use the date of TODAY ONLY !
LIKE THIS
tarih = [ GETDATE() + ' 16:00:00' ]
and
tarihB =[ GETDATE()-1 + ' 20:00:00' ]
-
Thanks
But i have problem
thet i must use the date of TODAY ONLY !
LIKE THIS
tarih = [ GETDATE() + ' 16:00:00' ]
and
tarihB =[ GETDATE()-1 + ' 20:00:00' ]
-
1st.
DO NOT CONVERT DATETIME TO VARCHAR!
2nd. If after serious consideration you'll decide you neet to convert to varchar,
DO NOT CONVERT DATETIME TO VARCHAR!
3rd. If anyway you must to convert it to varchar,
DO NOT CONVERT DATETIME TO VARCHAR!
DO NOT CONVERT DATETIME TO VARCHAR!
DO NOT CONVERT DATETIME TO VARCHAR!
DECLARE @StartTime datetime, @EndTime datetime, @Today datetime
SELECT @StartTime = '1900-01-01 16:00:00', @EndTime = '1900-01-01 20:00:00'
SELECT @Today = GETDATE()
SELECT @Today = dbo.DateOnly(@Today)
SELECT @StartTime = @Today + @StartTime , @EndTime = @Today + @EndTime
SELECT ...
FROM TABLE
WHERE DateTimeColumn between @StartDate and @EndDate
----------
CREATE FUNCTION dbo.DateOnly
(@DT datetime)
RETURNS datetime
AS
BEGIN
RETURN convert(datetime, convert(int, @dt - 0.5))
END
GO
_____________
Code for TallyGenerator -
OK what you say is that i cannot use VIEW
only a PROCEDURE ????
Viewing 15 posts - 1 through 15 (of 34 total)
You must be logged in to reply to this topic. Login to reply