Avatar of Gareth_Pointon
Gareth_Pointon
 asked on

SQL View - Pass over variables

Hi,

I have built a complex SQL view that checks for users that are not in certain tables on certain dates.

This is working OK when I manually add the date rage "From" and "To" in to the SQL View....
I'm Using a between function for two date like this:

 (start BETWEEN '2013-01-01' AND '2013-20-04') AND (stop BETWEEN '2013-01-01' AND '2013-20-04')

What I would like to do now is to Call this View from my ASP page and use two variables for the from and to dates.

Can this be done ?

Thanks
ASPMicrosoft SQL Server 2005SQL

Avatar of undefined
Last Comment
Gareth_Pointon

8/22/2022 - Mon
plusone3055

yes it can be done
just create extra text boxes on that form
and tie those variables into your View as the dates

OR
create a button to click
when that button is clicked
have a new popup form appear to exnter the dates in textboxes
Ioannis Paraskevopoulos

Consider your view as a table where you can SELECT FROM and have a WHERE clause.

So you could have:

SELECT * FROM YourView WHERE start >= '2013-01-01' AND start <=  '2013-20-04' AND stop >= '2013-01-01' AND stop <='2013-20-04'

Open in new window


Giannis
Big Monty

you cannot pass parameters to a view, you would have to change it to either a stored procedure or a function, which should be relatively easy to you, despite any complexities of your view. you could do something like the following:

create procedure nameOfProc
     @startDate datetime,
     @endDate datetime
as
begin
       -- your view code
       AND (start BETWEEN @startDate AND @endDate) AND (stop BETWEEN @startDate AND @endDate)

end
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
PortletPaul

a view is a stored query, these do not accept parameters

you are free to simply add a where condition e.g.

select
*
from that_view
where (start BETWEEN '2013-01-01' AND '2013-20-04') AND (stop BETWEEN '2013-01-01' AND '2013-20-04')

You could use a "table function" instead, these DO accept parameters

before I leave with that thought however please "Beware of Between"

your selection criteria probably should look more like this:

select
*
from that_view
where (start >= '2013-01-01' AND start < '2013-20-05')  -- nb < the next day
AND (stop >= '2013-01-01' AND stop < '2013-20-05') -- nb < the next day
Scott Fell

Use the query you created in sql manager studio and place it in your asp page.

startDate=request.form("StartDate")
endDate=request.form("EndDate")
' only good dates
if not isdate(startDate) then
   startDate=dateadd("m",-1,date)
end if
if not isdate(endDate) then
   endDAte=date
end if

sql="Select * from MyTable WHERE  (start BETWEEN '"&startDate&"' AND '"&endDate&"' ) AND (stop BETWEEN '"&startDate&"'  AND '"&endDate&"' )"

' add sql to your recordset call

Open in new window

Scott Fell

I concur with @PortletPaul to not use between and use greater then/less then instead.  Every time I run into that in ready, the warning is not using between.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
PortletPaul

seems I'm much to slow - sorry for the repetition, but please do be careful with using "between ... and" with datetime information. It can have unexpected problems.

= '2013-20-04' means equal to '2013-20-04 00:00:00:0000'

(so anything during the 24 hours up to 2013-20-05 are potentially "missed")
Ioannis Paraskevopoulos

LOL it is why i didn't just copied and paste the between statements and changed them to >= and <=.

He is right though...

Giannis
Gareth_Pointon

ASKER
Hi Folks.

Yes I have read there may be issues with between but didn't think it to be 100%.

However I want to try and get away from using the SQP with in the ASP page a the data is not filtered before it gets to the page and this can slow things down, where as the view has already filtered the SQL data on the SQL server and saved bandwidth....

I thought I'd Past my code here so you all get a true image of the issue:
SELECT     ID, title, firstname, surname, Department, Position, suspend
FROM         dbo.tblUsers
WHERE     (Department = N'Site Engineer') AND (suspend = 0) AND (NOT EXISTS
                          (SELECT     Eng_ID, start, stop
                            FROM          dbo.qryEng_Booked_Check
                            WHERE      (Eng_ID = dbo.tblUsers.ID) AND (start BETWEEN '2013-01-01' AND '2013-20-04') AND (stop BETWEEN '2013-01-01' AND '2013-20-04')))

Open in new window

Sorry I was going to do this earlier but didn't expect such a big response.

Whats your thoughts now seeing the code...
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
Big Monty

turn it into a stored procedure
Gareth_Pointon

ASKER
What do you mean?
Big Monty

see my original post on turning the view into a stored procedure
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Scott Fell

>"However I want to try and get away from using the SQP with in the ASP page a the data is not filtered before it gets to the page"

I would want to accept data to the page, then filter using vb code (not with js) before submitting.  As far as performance, I don't know you will see much difference running the sql on your asp page vs on the server.  Maybe if you have hundreds of users per minute?  

For performance, you will get much more out of using getrows() then looping through the recordset.  

For stored procedures, I will create code to access the SP as an include file and turn it into a function.  Then call the function on the page as needed.
PortletPaul

>>.read there may be issues with between but didn't think it to be 100%.
there are issues with between and datetime ranges, and it is 100% verifiable...

the safest way to ensure you get all you want - but only what you want is to:

where ( [datetime] >= [dt-variable-low] and  [datetime] < [dt-variable-high+1] )

sorry, I'll convert you yet :)

just one observation, that NOT EXISTS subquery only needs one field, not 3 lines 14/15 below are redundant
SELECT ID
	, title
	, firstname
	, surname
	, Department
	, Position
	, suspend
FROM dbo.tblUsers
WHERE (Department = N'Site Engineer')
	AND (suspend = 0)
	AND (
		NOT EXISTS (
			SELECT Eng_ID
				, start
				, stop
			FROM dbo.qryEng_Booked_Check
			WHERE (Eng_ID = dbo.tblUsers.ID)
				AND (
					start BETWEEN '2013-01-01'
						AND '2013-20-04'
					)
				AND (
					stop BETWEEN '2013-01-01'
						AND '2013-20-04'
					)
			)
		)

Open in new window

Gareth_Pointon

ASKER
So what your saying is the date filter needs to be like this:
SELECT ID
	, title
	, firstname
	, surname
	, Department
	, Position
	, suspend
FROM dbo.tblUsers
WHERE (Department = N'Site Engineer')
	AND (suspend = 0)
	AND (
		NOT EXISTS (
			SELECT Eng_ID
				, start
				, stop
			FROM dbo.qryEng_Booked_Check
			WHERE (Eng_ID = dbo.tblUsers.ID)
				AND (
					start >= '2013-01-01'
						AND <= '2013-20-04'
					)
				AND (
					stop >= '2013-01-01'
						AND <= '2013-20-04'
					)
			)
		)

Open in new window

   
And that the variables are:
     @startDate datetime,
     @endDate datetime

Is fo how do you pass this over from the SQL page. Is it like calling a new RS or totally different.

Sorry this is a new way forme and once I got it its in there :-)
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
PortletPaul

no, you have the equivalent of between, look carefully I have removed 2 by = and moved 04 to 05
					start >= '2013-01-01'
						AND < '2013-20-05'
					)
				AND (
					stop >= '2013-01-01'
						AND < '2013-20-05'

Open in new window

Someone else may address the question of how to pass the parameters: you as using .asp ?  correct?
Gareth_Pointon

ASKER
MMM I'm certainly not getting this as I have pasted the code in to SQL Manager and got the error on the screenshot.....
Screen-Shot-2013-04-03-at-09.34..png
PortletPaul

my bad: my very bad, apologies
SELECT ID
	, title
	, firstname
	, surname
	, Department
	, Position
	, suspend
FROM dbo.tblUsers
WHERE (Department = N'Site Engineer')
	AND (suspend = 0)
	AND (
		NOT EXISTS (
			SELECT Eng_ID
				, start
				, stop
			FROM dbo.qryEng_Booked_Check
			WHERE (Eng_ID = dbo.tblUsers.ID)
				AND (
					start >= '2013-01-01'
						AND start < '2013-20-05'
					) 
				AND (
					stop >= '2013-01-01'
						AND stop < '2013-20-05'
					)
			)
		)

Open in new window

Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Gareth_Pointon

ASKER
How do I pass the Variables over to the SQP Procedure from the ASP page ?
Gareth_Pointon

ASKER
OK I now have it working to a degree but I need it to include the Stop date and not be before ...

SELECT     ID, username, password, title, firstname, surname, Department, Position, email, admin, suspend, Timestamp, last_login, confirmcode, Preptime
FROM         dbo.tblUsers
WHERE     (Department = N'Site Engineer') AND (NOT EXISTS
                          (SELECT     Eng_ID
                            FROM          dbo.qryEng_Booked_Check
                            WHERE      (Eng_ID = dbo.tblUsers.ID) AND (start >= '2012-19-06') AND (start < '2013-22-06') AND (stop >= '2012-19-06') AND (stop < '2013-22-06')))

Open in new window

PortletPaul

>>to include the Stop date and not be before

not be before.... this? (the "stop date")

language is a peculiar thing, but I'm not entirely sure what you mean - sorry.


How does this query (just above) NOT meet your expectations?
There is a filter on a field called 'stop' - is this what you mean? lines 20+21
SELECT    
  ID
, username
, password, title
, firstname
, surname
, Department
, Position
, email
, admin, suspend, Timestamp, last_login, confirmcode, Preptime
FROM         dbo.tblUsers
WHERE     (Department = N'Site Engineer') 
AND (NOT EXISTS
                  (
                    SELECT     Eng_ID
                    FROM          dbo.qryEng_Booked_Check
                    WHERE      (Eng_ID = dbo.tblUsers.ID) 
                    AND (start >= '2012-19-06') 
                    AND (start < '2013-22-06') 
                    AND (stop >= '2012-19-06') 
                    AND (stop  < '2013-22-06')
                  )
     )

Open in new window

Your help has saved me hundreds of hours of internet surfing.
fblack61
Gareth_Pointon

ASKER
Hi Sorry.

I have Start and Stop fields.

The above will work if I do < Stop but not on <= Stop.

This means it is not including the actual end data I'm passing over. Its doing before...
I'm tring to get this to work in the statement:

NOT (start >= CONVERT(DATETIME, '2013-06-19 00:00:00', 102) AND start <= CONVERT(DATETIME, '2013-06-20 00:00:00', 102)) AND 
                      (NOT (stop >= CONVERT(DATETIME, '2013-06-19 00:00:00', 102) AND stop <= CONVERT(DATETIME, '2013-06-20 00:00:00', 102)

Open in new window

PortletPaul

>>The above will work if I do < Stop but not on <= Stop.

include the = symbol

                    AND (start >= '2012-19-06')
                    AND (start <= '2013-22-06')  -- now include 2013-22-06 00:00:00
                    AND (stop >= '2012-19-06')
                    AND (stop <= '2013-22-06') -- now include 2013-22-06 00:00:00

are you saying this does not alter the result?
I can see none of your data - so I'm flying blind

if your data contains both date and time,
then finding data that actually equals 2013-22-06 00:00:00 could be rare
Gareth_Pointon

ASKER
Table qryEng_Booked_Check
ENG_ID    Start                                         Stop                                         Surname
31              2013-06-19 00:00:00.000      2013-06-21 23:59:59.000      Boon
25              2013-06-19 00:00:00.000      2013-06-21 23:59:59.000      Clowes
20            2013-05-21 00:00:00.000      2013-05-23 23:59:59.000      Everill

If I use this code it will remove the Engineers from the USER Table that appear in the qryRng_Booked_Check View:
SELECT     ID, username, password, title, firstname, surname, Department, Position, email, admin, suspend, Timestamp, last_login, confirmcode, Preptime
FROM         dbo.tblUsers
WHERE     (Department = N'Site Engineer') AND (NOT EXISTS
                          (SELECT     Eng_ID
                            FROM          dbo.qryEng_Booked_Check
                            WHERE      (Eng_ID = dbo.tblUsers.ID)))

Open in new window


What I would like to Add is the Data range so we can select the START and STOP Dates and add them to the filter so if engineers are booked for a job it will not show them in a drop down box. This will then only show the available engineers.

I hope this makes more sense.

Sorry for the confusion.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
PortletPaul

and stop > getdate()

i.e. as at June 20, only  the following is 'available'
20            2013-05-21 00:00:00.000      2013-05-23 23:59:59.000      Everill
SELECT ID
	, username
	, password
	, title
	, firstname
	, surname
	, Department
	, Position
	, email
	, admin
	, suspend
	, TIMESTAMP
	, last_login
	, confirmcode
	, Preptime
FROM dbo.tblUsers
WHERE (Department = N'Site Engineer') AND (
		NOT EXISTS (
			SELECT Eng_ID
			FROM dbo.qryEng_Booked_Check
			WHERE (Eng_ID = dbo.tblUsers.ID)
and stop > getdate()
			)
		)

Open in new window

note there is a NOT exists, so we need to find things we do NOT want (stop > getdate() is NOT wanted)
Gareth_Pointon

ASKER
Sorry what do you mean not wanted ....

I need this code somehow incorporating in to the code as its a data range.

NOT (start >= CONVERT(DATETIME, '2013-06-19 00:00:00', 102) AND start <= CONVERT(DATETIME, '2013-06-20 00:00:00', 102)) AND 
                      (NOT (stop >= CONVERT(DATETIME, '2013-06-19 00:00:00', 102) AND stop <= CONVERT(DATETIME, '2013-06-20 00:00:00', 102)

Open in new window


So if I pass two date (PassStart and PassStop) I need to interrigate the Database's START and STOP to remove and entries that fall with in these dates as the engineer will not be available to do the job.

Thanks
PortletPaul

WHERE (Department = N'Site Engineer') AND (
            NOT EXISTS (

the subquery that follows the above is contained within a NOT EXISTS

so IF the subquery located available people, then NOT EXISTS would reverse that, so you would end-up with UNavailable people (in the final result)

the innermost subquery needs to locate things you do NOT want (in the final result)
is that clearer now?

It's entirely up to you, you could flip the whole thing around - but I'm way too tired right now. (off to bed now)
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
Gareth_Pointon

ASKER
Sorry I'm just lost on this ...
PortletPaul

I don't not understand

that's a double negative (i.e. I do understand)

you want available people (I think/hope), so

where NOT EXISTS ( available people ) -- would be incorrect

where NOT EXISTS( unavailable people ) -- would be correct

so the logic inside the ( ... ) needs to find unavailable people

I cannot think of any way to explain this better - sorry.
Gareth_Pointon

ASKER
OK .. I just want the available people to be shown with in the search. I know if I do the NOT() command against the table with the booked dates in it then filteres the users who are not in the booked table.

I then need to add the data search to the booked dates table so I can just search that part of the date range.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Gareth_Pointon

ASKER
This is the SQL Code I have last.

Are you saying that the Date filter has to be before the NOT EXISTS?

FROM:

SELECT     ID, title, firstname, surname, Department, Position, suspend
FROM         dbo.tblUsers
WHERE     (Department = N'Site Engineer') AND (suspend = 0) AND (NOT EXISTS
                          (SELECT     Eng_ID, start, stop
                            FROM          dbo.qryEng_Booked_Check
                            WHERE      (Eng_ID = dbo.tblUsers.ID) AND (start >= '2013-19-06') AND (start < '2013-21-06') AND (stop >= '2013-19-06') AND (stop < '2013-21-06')))

TO:

SELECT     ID, title, firstname, surname, Department, Position, suspend
FROM         dbo.tblUsers
WHERE     (Department = N'Site Engineer') AND (suspend = 0) AND (AND (start >= '2013-19-06') AND (start < '2013-21-06') AND (stop >= '2013-19-06') AND (stop < '2013-21-06')) AND (NOT EXISTS
                          (SELECT     Eng_ID, start, stop
                            FROM          dbo.qryEng_Booked_Check
                            WHERE      (Eng_ID = dbo.tblUsers.ID) ))
PortletPaul

It is not necessary to place the "not exists subquery" in a specific location of the where clause.

Looks to me like the first of the 2 listed is more likely to be correct as the fields [start] and [stop] belong to  dbo.qryEng_Booked_Check I  expect. This looks the most correct.

the second query does not look as if the date fields in the right place
Gareth_Pointon

ASKER
Well the first or second do not work so I'm stuck ....
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
PortletPaul

can you provide some data from the tables involved? (i does not need to be a big sample)
Gareth_Pointon

ASKER
table one just has dates in with engineer ID and active or not and the other has engineers ID and engineers names. nothing overly complicated.
PortletPaul

It is really such a same you haven't provided data as I could test, now (again) I am left to guess :(
It truly makes a difference if data is provided.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
PortletPaul

>>The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.:
                        AND (start >= '2013-19-06')
                        AND (start < '2013-21-06')
                        AND (stop >= '2013-19-06')
                        AND (stop < '2013-21-06')

You have used a YYYY-DD-MM sequence which isn't correct

the best sequence is always YYYY-MM-DD, and in fact the very best format is without delimiters YYYYMMDD
PortletPaul

this worked for me on some dummy data (here: http://sqlfiddle.com/#!3/1195d/1 )
SELECT
       ID
     , title
     , firstname
     , surname
     , Department
     , Position
     , suspend
FROM dbo.tblUsers
WHERE (Department = N'Site Engineer')
     AND (suspend = 0)
     AND (
          NOT EXISTS (
               SELECT
                      Eng_ID
                    , start
                    , stop
               FROM dbo.qryEng_Booked_Check
               WHERE (Eng_ID = dbo.tblUsers.ID)
                    AND (start >= '2013-07-01')
                    AND (start  < '2013-07-04')
                    AND (stop  >= '2013-07-01')
                    AND (stop   < '2013-07-04')
               )
          )
;

Open in new window

PortletPaul

by the way its not necessary for the exists subquery to return fields, this works too:
SELECT
       ID
     , title
     , firstname
     , surname
     , Department
     , Position
     , suspend
FROM dbo.tblUsers
WHERE (Department = N'Site Engineer')
     AND (suspend = 0)
     AND (
          NOT EXISTS (
               SELECT 1
               FROM dbo.qryEng_Booked_Check
               WHERE (Eng_ID = dbo.tblUsers.ID)
                    AND (start >= '2013-07-01')
                    AND (start  < '2013-07-04')
                    AND (stop  >= '2013-07-01')
                    AND (stop   < '2013-07-04')
               )
          )
;

Open in new window

I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
Gareth_Pointon

ASKER
Ok I will try and get data off the server.
Gareth_Pointon

ASKER
Sorry again but is there a simple SQL command that will take the table structure and data. That was you can see the data type also.
PortletPaul

did you try this url?
http://sqlfiddle.com/#!3/1195d/1 

all I wanted was a few records from each table, as csv or Excel
really it's because it shows how your data is stored
- without it we have to guess.

Have you tried either of the last 2 queries above?
if these are not producing any results it may be the nature of the multiple AND conditions you are using for the dates

try without conditions on the [stop] field. Does that produce results?
                    AND (start >= '2013-07-01')
                    AND (start  < '2013-07-04')
--                    AND (stop  >= '2013-07-01')
--                    AND (stop   < '2013-07-04')

still no results? try with no date conditions
--                    AND (start >= '2013-07-01')
--                    AND (start  < '2013-07-04')
--                    AND (stop  >= '2013-07-01')
--                    AND (stop   < '2013-07-04')

if there are still no results, then it probably IS a "data issue" and I really will need to see some valid data from you.

alternatively, go to that sqlfiddle url, and try to replicate a sample of your data there and let me know the url.

btw: not interested in getting private data (e.g. people's names etc.)  - they can be altered, but would like to see data that really represents what you have to deal with.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Gareth_Pointon

ASKER
dbo.qryEng_Booked_Check

ENG_ID      Start            Stop            Username      Department            Firstname            Surname
20      2013-07-03 00:00:00.000      2013-07-05 23:00:00.000      User1      Site Engineer      User      one
31      2013-07-23 00:00:00.000      2013-07-25 23:00:00.000      User2      Site Engineer      User      Two
22      2013-08-12 00:00:00.000      2013-08-14 23:00:00.000      User3      Site Engineer      User      Three
31      2013-07-02 00:00:00.000      2013-07-04 23:00:00.000      User2      Site Engineer      User      Two
20      2013-07-02 00:00:00.000      2013-07-04 23:00:00.000      User1      Site Engineer      User      One
NULL      NULL      NULL      NULL      NULL      NULL      NULL

dbo.tblUsers

ID      Username      Password      title      firstname      surname      department      position      email      admin      suspend
20      User1      password      Mr      user      1      Site Engineer      Gas Quality Engineer      email      NULL      0
22      User2      password      Mr      user      2      Site Engineer      Gas Quality Engineer      email      NULL      0
31      User3      password      Mr      user      3      Site Engineer      Gas Quality Engineer      email      NULL      0
Gareth_Pointon

ASKER
in addition that URL does not load for me ..

Hope the data help. I appreciate your help.

Thanks
PortletPaul

excellent, just excellent - thank you! results from that sample data:
ID	TITLE	FIRSTNAME	SURNAME	DEPARTMENT	POSITION		SUSPEND
20	Mr	user		1	Site Engineer	Gas Quality Engineer	0
22	Mr	user		2	Site Engineer	Gas Quality Engineer	0
31	Mr	user		3	Site Engineer	Gas Quality Engineer	0

Open in new window

from this query:
SELECT
      ID
	, title
	, firstname
	, surname
	, Department
	, Position
	, suspend
FROM dbo.tblUsers
WHERE (Department = N'Site Engineer')
	AND (suspend = 0)
	AND (
		NOT EXISTS (
			SELECT Eng_ID
				, start
				, stop
			FROM dbo.qryEng_Booked_Check
			WHERE (Eng_ID = dbo.tblUsers.ID)
				AND (start >= '2013-07-01')
				AND (start < '2013-07-04')
				AND (stop >= '2013-07-01')
				AND (stop < '2013-07-04')
			)
		)
;

Open in new window

see: http://sqlfiddle.com/#!3/7c1f6/1
sqlfiddle works fro me and others, maybe it's your browser type/version? (I use Chrome / Firefox mostly), anyway by recording those URL's I can get back to latest if I need to - but do try again - its can be very helpful.
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
Gareth_Pointon

ASKER
Hi,

This is great .. Fantastic...

I can see the code working on the site OK also.

However I have now found that the server is using YYYY-DD-MM formatting. This is why I was not fully seeing the right data. I changed the year on the above code to 2012 and 2014 and this then worked.

My last question to resolve this fully will be how to I change the date formatting on the server to YYYY-MM-DD.

I thought I was setup for British in both IIS and SQL.

Thanks again for this help. Learned a lot on this already.
Gareth_Pointon

ASKER
OK I have fixed the formatting issue now.. bloody UK as YYYY-DD-MM ..

However on the code if a start date is 2013-07-02 and end sate is 2013-07-05 and I search for :

 WHERE      (Eng_ID = dbo.tblUsers.ID) AND (start >= '2013-07-02') AND (start <= '2013-07-02') AND (stop >= '2013-07-02') AND (stop <= '2013-07-02')))

Open in new window


Then this should not show the users with this date. However it is. I was told not to use between as there are issue with that way. As the above dates fill with in this the user shold not be shown.

How to I fix this.

Thanks
PortletPaul

to change the database date format see:
http://msdn.microsoft.com/en-us/library/ms189491(v=sql.105).aspx

However the database default date format does not matter, please don't think that date information is stored in a format - they aren't (in fact date/time information is saved as integers).

YYY-DD-MM isn't a British format by the way - it is a very unusual French format I believe.
We use dd/mm/yyyy as a basic standard in 'British' areas of the world.

:) & mm/dd/yyyy is a bloody nuisance too if you ask me (Aussie's tend to place bloody in front of everything).

In SQL Server the MOST safe date format to use in queries is with NO separator, so if you wanted 2nd of July 2013 to 5th July 2031 then the safest option would be to use these in the query:

'20130702'
'20130705'

Afraid I didn't understand this:
"Then this should not show the users with this date. However it is. I was told not to use between as there are issue with that way. As the above dates fill with in this the user should not be shown."
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Gareth_Pointon

ASKER
OK.

What I'm trying to say is if an engineer is booked from 2013-01-01 to 2013-01-05 and I search to filter with 2113-01-01 to 2013-01-01 then that engineer should not be shown as it falls with in the data range.
PortletPaul

please try this: http://sqlfiddle.com/#!3/3c749/3
 
DECLARE @from AS date, @until AS date
SET @from  = '20130701'
SET @until = '20130701'

SELECT
       ID
     , title
     , firstname
     , surname
     , Department
     , POSITION
     , suspend
FROM dbo.tblUsers
WHERE (Department = N'Site Engineer')
     AND (suspend = 0)
     AND (
          NOT EXISTS (
               SELECT Eng_ID
                    , START
                    , stop
               FROM dbo.qryEng_Booked_Check
               WHERE (Eng_ID = dbo.tblUsers.ID)
                    AND (
                          (START >= @from AND stop < @until)
                        OR
                          (@from >= START AND @until < stop )
                        )
               )
          )
;

Open in new window

note: the bracket arrangement is important here
Gareth_Pointon

ASKER
Hi,

Thanks for this. I can see that it works in SQL view but not in ASP.

I have to change the code a little for ASP but its much the same:

strSQL = "SELECT ID, title, firstname, surname, Department, POSITION, suspend FROM dbo.tblUsers WHERE (Department = N'Site Engineer') AND (suspend = 0)AND ( NOT EXISTS ( SELECT Eng_ID, START, stop FROM dbo.qryEng_Booked_Check WHERE (Eng_ID = dbo.tblUsers.ID) AND ( ([START] >= CONVERT(DATETIME,'"&STARTa&"',103) AND [stop] < CONVERT(DATETIME,'"&FINISHEDa&"',103)) OR (CONVERT(DATETIME,'"&STARTa&"',103) >= [START] AND CONVERT(DATETIME,'"&FINISHEDa&"',103) < [stop] ))))"

Open in new window


Its not filtering the users as it is in SQL view.

Is it due to the START and FINISH SQL fields been DATETIME and not DATE ?

Thanks
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
ASKER CERTIFIED SOLUTION
PortletPaul

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Gareth_Pointon

ASKER
Found it.
The table value was DATETIME. Then I added another two columns that were just DATES it then worked.

Thanks for the information and help.