SQL Query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

SQL Query

Author

Topic

nigeldavenport
Starting Member

12 Posts

Posted - 2006-09-05 : 15:25:47

I have a theatre events database that I am trying to write a query for that will not work and I cant see why the query isnt working - still returns dates before the today function strSQL = "SELECT * FROM theatres INNER JOIN theatreevents ON theatres.theatre_id = theatreevents.theatre_id WHERE theatre_event_date_from>="& Date() &" ORDER BY theatre_event_date_from"

It is designed to limit the recordset to dates = today and in the futfuture - any ideas where I am going wrong?

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2006-09-05 : 16:00:50

The biggest mistake you are making is not using parameters. Please read this carefully:

http://weblogs.sqlteam.com/jeffs/archive/2006/07/21/10728.aspx

You should never concatenate together a SQL string like you are. Also be sure to read the quick link given about SQL Injection. Simply using parameters (and/or stored procedures) will make your life much easier and your code much cleaner.

- Jeff

nigeldavenport
Starting Member

12 Posts

Posted - 2006-09-05 : 16:40:57

Thanks Jeff - unfortunately I am not a programmer I have worked with access and sql statements for years I only know as much as this. I agree entirely with your point however about using sloppy code I can remember using an old act sirius with accounting software on 4 360K floppies which included the data for a small business - thats what I call good programming practice. However I am still stuck with this damn query and none the wiser about whats gone wrong.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2006-09-05 : 17:33:54

The link I directed you to explains exactly what is wrong if you follow the example closely and relate it to your own .... clue: it has to do with delimiters and date formatting. When in doubt, print out your SQL string instead of executing it to see how it is being constructed.

- Jeff

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-06 : 00:59:38

SELECT * FROM theatres INNER JOIN theatreevents ON theatres.theatre_id = theatreevents.theatre_id WHERE theatre_event_date_from > DATEADD(day, -1, GETDATE()) ORDER BY theatre_event_date_from

Peter Larsson
Helsingborg, Sweden

nigeldavenport
Starting Member

12 Posts

Posted - 2006-09-06 : 02:53:33

Thanks Jeff but I really havent time to learnat the moment but will do as soon as this project is out of the way because its a pretty neat idea.

That was great Peter copied and pasted the query into some asp code - it worked first time.

I have another question for you involving the same query (I have never been good with dates)I have a date_from and date_to field (a play will run for several days). I want the query to return a result for the period from and including both date_from and date_to dates.

Any ideas

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-06 : 03:30:12

You mean that the start date is before the wanted date, and the end date is after the wanted date?
You want the games that have begun, but not ended yet?

Peter Larsson
Helsingborg, Sweden

nigeldavenport
Starting Member

12 Posts

Posted - 2006-09-06 : 03:45:32

Yes thats correct except its Plays at the Theatre. The play will run from lets say 23/09/2006 (start_date) up until 30/09/2006 (end_date)
If I query the database between either of these two dates and inclusive of the two dates I want to see a record returned for the play.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-06 : 04:12:38

[code]SELECT *
FROM theatres
INNER JOIN theatreevents ON theatres.theatre_id = theatreevents.theatre_id
WHERE theatre_event_date_from <= @SpecificDate
AND theatre_event_date_to >= @SpecificDate
ORDER BY theatre_event_date_from[/code]or[code]SELECT *
FROM theatres
INNER JOIN theatreevents ON theatres.theatre_id = theatreevents.theatre_id
WHERE @SpecificDate BETWEEN theatre_event_date_from AND theatre_event_date_to
ORDER BY theatre_event_date_from[/code]

Peter Larsson
Helsingborg, Sweden

nigeldavenport
Starting Member

12 Posts

Posted - 2006-09-06 : 04:59:54

Not sure that either of those will work.

The query in plain english would be:

SELECT *
FROM theatres
INNER JOIN theatreevents ON theatres.theatre_id = theatreevents.theatre_id
WHERE theatre_event_date_from > DATEADD(day, -1, GETDATE())

This is great as it returns all dates where date_from is true and into the future.

I think the next statement should be an OR and the description would be -

If the date_from has expired but the date_to is true add these records to the recordset

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-06 : 05:56:35

Have you tried before you tell me the query will not work?

DECLARE @SpecificDate DATETIME

SELECT @SpecificDate = DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0) -- Or any other date you prefer to search for

SELECT		*
FROM		theatres
INNER JOIN	theatreevents ON theatres.theatre_id = theatreevents.theatre_id
WHERE		theatre_event_date_from <= @SpecificDate
		AND theatre_event_date_to >= @SpecificDate
ORDER BY	theatre_event_date_from

SELECT		*
FROM		theatres
INNER JOIN	theatreevents ON theatres.theatre_id = theatreevents.theatre_id
WHERE		@SpecificDate BETWEEN theatre_event_date_from AND theatre_event_date_to
ORDER BY	theatre_event_date_from

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources