July 14, 2003 at 10:26 am
what is the best way to search between tow
date fields ?????????
and help to check my code
----------------
WHERE (dbo.mainb.meosar = 1) AND (dbo.mainb.tarih >= CONVERT(datetime, CONVERT(varchar, DATEADD([day], 0, GETDATE()), 112) + ' 00:01:00')) AND
(dbo.mainb.tarih >= CONVERT(datetime, CONVERT(varchar, DATEADD([day], 0, GETDATE()), 112) + ' 00:01:00')) AND
(dbo.mainb.tarihb <= CONVERT(datetime, CONVERT(varchar, DATEADD([day], 30, GETDATE()), 112) + ' 23:59:00')) OR
(dbo.mainb.tarih <= CONVERT(datetime, CONVERT(varchar, DATEADD([day], 0, GETDATE()), 112) + ' 00:01:00')) AND
(dbo.mainb.tarihb >= CONVERT(datetime, CONVERT(varchar, DATEADD([day], 30, GETDATE()), 112) + ' 23:59:00')) OR
(dbo.mainb.tarih >= CONVERT(datetime, CONVERT(varchar, DATEADD([day], 0, GETDATE()), 112) + ' 00:01:00')) AND
(dbo.mainb.tarih <= CONVERT(datetime, CONVERT(varchar, DATEADD([day], 0, GETDATE()), 112) + ' 00:01:00'))
-----------------------------
thnks a lot
ilan
July 14, 2003 at 11:12 am
In English, not SQL, what are you trying to accomplish here? Have you taken a look at DATEADD(hour, DateField)? To strip the time portion of a datetime field, use DATEADD(day, 0, DATEDIFF(day, 0, DateField)) since it requires no lookup to syslanguages to find the localization string for CONVERT(varchar, DateField, 112). Also, use the BETWEEN clause. Demonstrated techniques below...:
WHERE
--...statements here...
AND
(
dbo.mainb.tarihb BETWEEN
DATEADD(hour, 1, DATEADD(day, 0, DATEDIFF(day, 0, GETDATE())))
AND
DATEADD(hour, -1, DATEADD(day, 31, DATEADD(day, 0, DATEDIFF(day, 0, GETDATE()))))
OR
--...convert the rest like above...
July 14, 2003 at 11:52 am
i need help
to search between tow date
start date = dbo.mainb.tarih
end date = dbo.mainb.tarih
----------
and the
start date must to be
GETDATE()+ ' 16:00:00'
and end date
DATEADD([day], 1, GETDATE() ' 16:00:00'
----------
thnks
ilan
July 14, 2003 at 11:55 am
Look at the code I posted; it explains how to add (or subtract) hours from GETDATE()...using DATEADD(hour, n, GETDATE())
July 14, 2003 at 12:09 pm
but like your explains i can not
search evry day
between Getdate() '16:00' and Getdate()+1 '16:00'
and in tow different field
thnks ilan
July 14, 2003 at 12:27 pm
your original WHERE clause doesn't make sense for dbo.mainb.tarih...you are saying give me all records that have tarih greater than or equal to today at 1am and tarih is less than or equal to today at 1am. Which basically boils down to: you are only going to retrieve records where tarih = today at 1am.
July 14, 2003 at 12:36 pm
and what not O.K like this ? after i fix it
i work
or i am mistake ?
-----------
WHERE (dbo.mainb.tarih >= CONVERT(varchar, DATEADD([day], 0, GETDATE()), 112) + ' 16:00:00') AND
(dbo.mainb.tarih >= CONVERT(varchar, DATEADD([day], 0, GETDATE()), 112) + ' 16:00:00') AND (dbo.mainb.tarihb <= CONVERT(varchar, DATEADD([day],
1, GETDATE()), 112) + ' 16:00:00') OR
(dbo.mainb.tarih <= CONVERT(varchar, DATEADD([day], 0, GETDATE()), 112) + ' 16:00:00') AND (dbo.mainb.tarihb >= CONVERT(varchar, DATEADD([day],
1, GETDATE()), 112) + ' 16:00:00') OR
(dbo.mainb.tarih >= CONVERT(varchar, DATEADD([day], 0, GETDATE()), 112) + ' 16:00:00') AND (dbo.mainb.tarih <= CONVERT(varchar, DATEADD([day],
0, GETDATE()), 112) + ' 16:00:00')
-------------------
ilan
July 14, 2003 at 1:01 pm
1) ok, this:
OR
(dbo.mainb.tarih >= CONVERT(varchar, DATEADD([day], 0, GETDATE()), 112) + ' 16:00:00') AND (dbo.mainb.tarih <= CONVERT(varchar, DATEADD([day], 0, GETDATE()), 112) + ' 16:00:00')
doesn't make sense. It can be shortened to:
OR dbo.mainb.tarih = DATEADD(hour, 16, DATEADD(day, 0, DATEDIFF(day, 0, GETDATE())))
since you are checking greater than or equal to and less than or equal to the exact same date.
To test whether a tarih is between 2 different dates:
-- Tests whether tarih between today @ 1am and tomorrow at 4pm
WHERE
tarih BETWEEN DATEADD(hour, 1, DATEDIFF(day, 0, GETDATE())) AND DATEADD(hour, 16, DATEDIFF(day, 0, GETDATE() + 1)))
July 14, 2003 at 1:23 pm
ok
i only wont to know
you think in the long way (my code) am i missed any record ??
OR you say thet correct way to do it
and in the end i get the same result ??
thnks
ilan
July 14, 2003 at 2:06 pm
no, your method of using CONVERT(VARCHAR...) is not incorrect, only inefficient. However your where condition:
WHERE (dbo.mainb.meosar = 1)
AND
(dbo.mainb.tarih >= CONVERT(datetime, CONVERT(varchar, DATEADD([day], 0, GETDATE()), 112) + ' 00:01:00'))
AND
(dbo.mainb.tarih >= CONVERT(datetime, CONVERT(varchar, DATEADD([day], 0, GETDATE()), 112) + ' 00:01:00'))
AND
(dbo.mainb.tarihb <= CONVERT(datetime, CONVERT(varchar, DATEADD([day], 30, GETDATE()), 112) + ' 23:59:00'))
OR
(dbo.mainb.tarih <= CONVERT(datetime, CONVERT(varchar, DATEADD([day], 0, GETDATE()), 112) + ' 00:01:00'))
AND
(dbo.mainb.tarihb >= CONVERT(datetime, CONVERT(varchar, DATEADD([day], 30, GETDATE()), 112) + ' 23:59:00'))
OR
(dbo.mainb.tarih >= CONVERT(datetime, CONVERT(varchar, DATEADD([day], 0, GETDATE()), 112) + ' 00:01:00'))
AND
(dbo.mainb.tarih <= CONVERT(datetime, CONVERT(varchar, DATEADD([day], 0, GETDATE()), 112) + ' 00:01:00'))
makes no sense in its current format. you either have to add some parentheses to separate out the OR and AND blocks so that you execute the WHERE conditions in the correct order. Like I said originally, if you tell me what you are trying to do, in English, I can help better. For instance, "I am trying to get all records that my date field, tarih, falls between today @ 1m and tomorrow @ 4pm, or any records where tarih is before this date and tarihb is after this date, and so on...
July 14, 2003 at 2:35 pm
i mean this
---------------
WHERE (dbo.mainb.tarih >= CONVERT(varchar, DATEADD([day], 0, GETDATE()), 112) + ' 16:00:00') AND
(dbo.mainb.tarih >= CONVERT(varchar, DATEADD([day], 0, GETDATE()), 112) + ' 16:00:00') AND (dbo.mainb.tarihb <= CONVERT(varchar, DATEADD([day],
1, GETDATE()), 112) + ' 16:00:00') OR
(dbo.mainb.tarih <= CONVERT(varchar, DATEADD([day], 0, GETDATE()), 112) + ' 16:00:00') AND (dbo.mainb.tarihb >= CONVERT(varchar, DATEADD([day],
1, GETDATE()), 112) + ' 16:00:00') OR
(dbo.mainb.tarih >= CONVERT(varchar, DATEADD([day], 0, GETDATE()), 112) + ' 16:00:00') AND (dbo.mainb.tarih <= CONVERT(varchar, DATEADD([day],
0, GETDATE()), 112) + ' 16:00:00')
----------------------
July 14, 2003 at 2:50 pm
Then you need more parentheses. I think this is what you are looking for:
WHERE
(
(dbo.mainb.tarih >= CONVERT(varchar, DATEADD([day], 0, GETDATE()), 112) + ' 16:00:00')
AND
(dbo.mainb.tarihb <= CONVERT(varchar, DATEADD([day], 1, GETDATE()), 112) + ' 16:00:00')
)
OR
(
(dbo.mainb.tarih <= CONVERT(varchar, DATEADD([day], 0, GETDATE()), 112) + ' 16:00:00')
AND
(dbo.mainb.tarihb >= CONVERT(varchar, DATEADD([day], 1, GETDATE()), 112) + ' 16:00:00')
)
OR
(
(dbo.mainb.tarih >= CONVERT(varchar, DATEADD([day], 0, GETDATE()), 112) + ' 16:00:00')
AND
(dbo.mainb.tarih <= CONVERT(varchar, DATEADD([day], 0, GETDATE()), 112) + ' 16:00:00')
)
although, to be honest, it makes no sense, since that statements all somewhat contradict each other and the last OR expression, like I stated above, simply equates to an equality expression, not a BETWEEN statement. I'm really confused what you're trying to accomplish. Sorry.
Edited by - jpipes on 07/14/2003 2:54:57 PM
July 14, 2003 at 3:58 pm
ok but how can i do this ??
---------
tarih BETWEEN DATEADD(hour, 1, DATEDIFF(day, 0, GETDATE()))
AND
tarihb DATEADD(hour, 16, DATEDIFF(day, 0, GETDATE() + 1)))
------------------------
ilan
July 15, 2003 at 8:47 am
tarih BETWEEN DATEADD(hour, 1, DATEDIFF(day, 0, GETDATE()))
AND
DATEADD(hour, 16, DATEDIFF(day, 0, tarihb)))
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply