search between tow dates Problem
-
SQLBill,
Yes, I see that later on in the posts
. I wasn't sure what Ilan wanted, because the problem changed from the original problem:how to use always the the Date Today like this =(Getdate() +'20:00:00')
SELECT ... FROM ... WHERE startDate >= (Getdate() +'16:00:00') AND endDate <=(Getdate() +'20:00:00')
to (later on, in response to you):
DECLARE @StartTime datetime, @EndTime datetime ,@Today smalldatetime,@Today2 smalldatetime
SET @Today = GETDATE()-2
SET @Today2 = GETDATE()-1
set @StartTime = @Today + ' ' + '16:00'
set @EndTime = @Today2 + ' ' + ' 20:00'
SELECT *
FROM dbo.MAINB
WHERE tarih >= @StartTime AND tarihB >= @EndTime
AND (meosar LIKE '1')
-
i am confused !!!
i dont Know what to do i tray all advise from all the wonderful group of people here but i am steel have the same problem !!
this is my data in my table
----------------------------------
-id--- ------tarih---------- ---tarihb------------- ---eisor---
4760 | 01/11/2005 16:00:00 | 02/11/2005 18:00:00 | 1
4759 | 01/11/2005 16:00:00 | 02/11/2005 20:00:00 | 1
----------------------------------------------------------
and i dont Catch the dates between tow dates search
BETWEEN the hour 16:00 and 20:00
like this
--------------------
DECLARE @StartTime datetime, @EndTime datetime ,@Today smalldatetime,@Today2 smalldatetime
SET @Today = GETDATE()-3
SET @Today2 = GETDATE()-4
set @StartTime = @Today + ' ' + '16:00'
set @EndTime = @Today2 + ' ' + ' 20:00'
SELECT *
FROM dbo.MAINB
WHERE tarih >= @StartTime AND tarihB >= @EndTime
AND (meosar LIKE '1')
----------------------------------------------------
thanks
ilan
-
Ilan -
I see what's happening, unexpected but it makes sense once you see it. When you are setting @StartTime to @Today (which is the date - 3 days) + '16:00' the very unexpected result is @Today + 16hrs! to see it in action try this:
DECLARE @StartTime datetime, @EndTime datetime
DECLARE @Today datetime
DECLARE @Today2 datetime
SET @Today = GETDATE()-3
SET @Today2 = GETDATE()-4
print 'Today: ' + cast(@today as char(20))
print 'Today2: ' + cast(@Today2 as char(20))
set @StartTime = @Today + ' ' + '16:00'
set @EndTime = @Today2 + ' ' + ' 20:00'
print ''
print 'Starttime: ' + cast(@starttime as char(20))
print 'EndTime: ' + cast(@endtime as char(20))
No wonder it doesn't work in the query! Instead try this:
DECLARE @StartTime datetime, @EndTime datetime
SET @StartTime = convert(char(10),getdate()-3,112) + ' 16:00'
SET @EndTime = convert(char(10),getdate()-4,112) + '20:00'
print 'StartTime: ' + cast(@StartTime as char(20))
print 'EndTime: ' + cast(@EndTime as char(20))
SELECT *
FROM dbo.MAINB
WHERE tarih >= @StartTime AND tarihB >= @EndTime
AND (meosar LIKE '1%')
You'll note I also modified your like statement - your sample table doesn't show meosar so I'm just assuming that it's a char/varchar columns from which you want to select any record where meosar starts with '1'.
Joe
-
midan1, your problem you are playing with varchars where it must be datetime.
If period from 16:00 to 20:00 is something to be hardcoded?
I don't think so. So, store it in some table as datetime and use those values in your view.
Or, if you gonna harcode it, convert those times to datetime datatype immediately and than use it.
Something like this:
@StartTime = @Today + convert( datetime, '1900-01-01 16:00')
_____________
Code for TallyGenerator -
ilan,
Try this:
Script:
DECLARE @StartTime datetime, @EndTime datetime ,@Today smalldatetime,@Today2 smalldatetime
SET @Today = GETDATE()-4
SET @Today2 = GETDATE()-3
set @StartTime = @Today + ' ' + '16:00'
set @EndTime = @Today2 + ' ' + ' 20:00'
SELECT *
FROM dbo.MAINB
WHERE (tarih >= @StartTime OR tarihB >= @EndTime)
AND (meosar = '1')
Also, you have "meosar LIKE '1'", but your example the column is eisor. Which is correct? (The LIKE should be = ).
-SQLBill
Viewing 5 posts - 31 through 34 (of 34 total)
You must be logged in to reply to this topic. Login to reply