May 10, 2006 at 4:39 pm
i have a big problem on insert date
and after Search
I have Table of workers that i insert the employe evry day
like this
Table=sn
id | fld1(start Date) | fld4 (end_date) | mhlka |
| 111 | 10/05/2006 07:00 | 10/05/2006 09:00 | 1 |
| 111 | 10/05/2006 09:00 | 10/05/2006 12:00 | 1 |
| 111 | 10/05/2006 12:00 | 10/05/2006 14:30 | 1 |
| 222 | 10/05/2006 07:00 | 10/05/2006 14:00 | 2 |
| 222 | 10/05/2006 14:00 | 10/05/2006 14:30 | 2 |
| 333 | 10/05/2006 08:00 | 10/05/2006 15:00 | 3 |
| 333 | 10/05/2006 15:00 | 10/05/2006 16:00 | 3 |
and before i wont to insert the employe(from table employe)
to the next shift i search
and if the employe existing in the insert table (SN)
i dont sohw him in the List box (in my application)
like this
----------------------------------------------------
SELECT * FROM SilokE WHERE ((SilokE.mhlka) Like ('3'))
AND (((SilokE.id) Not In
(select id FROM sn WHERE sn.fld2 in (1) and
((sn.fld1 >= '10/05/2006 09:30 ' and sn.fld4 <= '01/01/2009 11:30 ')
or (sn.fld1 <= '10/05/2006 09:30 ' and sn.fld4 >= '10/05/2006 09:30 ')
or (sn.fld1 >= '10/05/2006 09:30 ' and sn.fld1 <= '01/01/2009 11:30 ' )
or (sn.fld1 >= '10/05/2006 09:30 ' and sn.fld1 <='10/05/2006 09:30 '))))
AND ((SilokE.mhlka) Like ('3'))) ORDER BY fname
------------------------------------------------------------
and the problem is if i select one employe (the same employe ID)
to few shift on one Day like this
from (10/05/2006 07:00 ) TO (10/05/2006 08:00)
and after from (10/05/2006 08:00) to (10/05/2006 12:00)
i cnot find the employe because i must to search from
from (10/05/2006 08:01 ) TO (10/05/2006 12:01)
i need to insert this employe to the next shift
so how to search BETWEEN Tow Date Fields
and to show in my list box of the employes (table employes)
this employes that available to the next Shift
but not to add one minute
THX
May 11, 2006 at 12:30 pm
This is very similar to the classic problem of only searching by the day, and losing the last day from the results.
I'm willing to bet that these datetime values are being inserted into your table from some kind of GETDATE function. Which is including the seconds and milliseconds. So, you have to take that into account. E.g., 09:30:30.693 (hypothetically, the time entered in the table) is greater than 09:30:00.000 (the time you are searching by).
You can solve this in a couple ways. Change any "<= X" comparison to "= '10/05/2006 09:30 ' and sn.fld4 = '10/05/2006 09:30:00.000' and sn.fld4 <= '01/01/2009 11:30:59.997')
May 11, 2006 at 12:54 pm
THX
do you think thet like this it work ????????????
---------------------------------------
SELECT * FROM SilokE WHERE ((SilokE.mhlka) Like ('3'))
AND (((SilokE.id) Not In
(select id FROM sn WHERE sn.fld2 in (1) and
((sn.fld1 >= '10/05/2006 09:30:00.000' and sn.fld4 <= '01/01/2009 11:30:59.997' ')
or (sn.fld1 <= '10/05/2006 09:30:00.000' and sn.fld4 >= '10/05/2006 09:30 ')
or (sn.fld1 >= '10/05/2006 09:30:00.000' and sn.fld1 <= '01/01/2009 11:30:59.997' )
or (sn.fld1 >= '10/05/2006 09:30:00.000' and sn.fld1 <='10/05/2006 09:30:00.000'))))
AND ((SilokE.mhlka) Like ('3'))) ORDER BY fname
_______________________________________________
OR i must do do this ONLY this !!!!!!!!!!!!!!
----------------
SELECT * FROM SilokE WHERE ((SilokE.mhlka) Like ('3'))
AND (((SilokE.id) Not In
(select id FROM sn WHERE sn.fld2 in (1) and
(sn.fld1 >= '10/05/2006 09:30:00.000' and sn.fld4 <= '01/01/2009 11:30:59.997')
AND ((SilokE.mhlka) Like ('3'))) ORDER BY fname
-----------------------------------------------
May 11, 2006 at 12:57 pm
The first is mostly correct, but you still have a couple less than comparisons with zeroes in there.
SELECT * FROM SilokE WHERE ((SilokE.mhlka) Like ('3'))
AND (((SilokE.id) Not In
(select id FROM sn WHERE sn.fld2 in (1) and
((sn.fld1 >= '10/05/2006 09:30:00.000' and sn.fld4 <= '01/01/2009 11:30:59.997' ')
or (sn.fld1 = '10/05/2006 09:30:00.000')
or (sn.fld1 >= '10/05/2006 09:30:00.000' and sn.fld1 = '10/05/2006 09:30:00.000' and sn.fld1 <='10/05/2006 09:30:59.997'))))
AND ((SilokE.mhlka) Like ('3'))) ORDER BY fname
Run that against some sample data, and see if it gives you the results you are looking for.
May 11, 2006 at 1:35 pm
THX
but the main problem is how to insert properly ????
i work i web page (vbscript) the form of the insert is like this
and i dont add the second + millisecond
-----------------
Date start | |
07:00 07:30 08:00 | hour start |
Date end | |
08:00 09:00 09:30 10:00 | Hour end |
employe-id |
-------------------------------------------------
and on the page (the insert page )
i do this
fld1 (date_start)='date start' & 'hour start'
fld4 (date_end)='date end' & 'date end'
so whan to add the second + millisecond
can i add it on the list box of the 'hour start' +' Hour end'
THX
May 12, 2006 at 5:43 am
That would depend, in some part, in how exactly you are passing those values into your query. Are you building the query on the fly in ASP? Is the query a stored procedure, that you're passing these values to as variables?
If you're building it on the fly, then just add it in as you build the values. So, instead of:
fld4 (date_end)='date end' & 'date end'
you'd have (since you only need to modify the ending time):
fld4 (date_end)='date end' & 'date end' & ':59.997'
If you're passing the values as variables to an SP, I recommend you actually go with doing the DATEADD to the variable value.
May 12, 2006 at 6:29 am
hi but in the first you say to do this
-----------------------
SELECT * FROM SilokE WHERE ((SilokE.mhlka) Like ('3'))
AND (((SilokE.id) Not In
(select id FROM sn WHERE sn.fld2 in (1) and
((sn.fld1 >= '10/05/2006 09:30:00.000' and sn.fld4 <= '01/01/2009 11:30:59.997' ')
or (sn.fld1 <= '10/05/2006 09:30:59.997' and sn.fld4 >= '10/05/2006 09:30:00.000')
or (sn.fld1 >= '10/05/2006 09:30:00.000' and sn.fld1 <= '01/01/2009 11:30:59.997' )
or (sn.fld1 >= '10/05/2006 09:30:00.000' and sn.fld1 <='10/05/2006 09:30:59.997'))))
AND ((SilokE.mhlka) Like ('3'))) ORDER BY fname
----------------------------------------
look at day start (fld1) thre is tow kind hours
1) 09:30:00.000
2) 09:30:59.997
i can use only one !!!!
--------------
i weel make a list box thet the user select the hour
but i can use only one Value
like this
list boox start hour
hour start | |
| label | value |
| 7:00 | 07:00:59.997 |
| 7:00 | 07:30:59.997 |
| 8:00 | 08:00:59.997 |
| 8:30 | 08:30:59.997 |
| 9:00 | 09:00:59.997 |
| 9:30 | 09:30:59.997 |
| ... | ... |
list boox End hour
hour end | |
| label | value |
| 7:00 | 07:00:00.000 |
| 7:00 | 07:30:00.000 |
| 8:00 | 08:00:00.000 |
| 8:30 | 08:30:00.000 |
| 9:00 | 09:00:00.000 |
| 9:30 | 09:30:00.000 |
| ... | ... |
is ok or not
---------------------------------------
OR i insert the time value with end "00.000"
and only search like this
-------------------------
fld4 (date_end)='date end' & 'date end' & ':59.997'
------------------------------------------
and what to to with FLD1 (start time) ????
this ??
fld1 (start_end)='start end' & 'start end' & ':59.997'
TNX
May 12, 2006 at 7:47 am
Even if they are fixed values, do not use literal dates in your WHERE clause - performance will suffer badly. Declare DateTime variables and use those, eg :
DECLARE @Date1 DateTime
SET @Date1 = CONVERT(DateTime, , 102)
SELECT * FROM dbo.MyTable WHERE MyDate = @Date1
This also makes it a lot easier to see what's going on and to play about with the values in development.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply