How do I get the next/top 5 events based on a date range?

Donnie Walker
Donnie Walker used Ask the Experts™
on
I have a database of events for the next 12 months.

How do I query the top 5 events based on today's date for the next 6 months?

What is the syntax for the today's date and six months from now?

I've tried this:

$query = 'SELECT TOP 5 date
                        ,startTime
                        ,endTime
                        ,title
                        ,details
                        ,room
                  FROM
                        WWW.dbo.Events
                         WHERE date BETWEEN (today) and (six months from today)
                   ORDER BY date DESC';
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Current date: GETDATE()

6 months from now: DATEADD(month, 6, GETDATE())

Author

Commented:
when I do that it says:

Notice: Array to string conversion

how would I use the dates in the query above?

Author

Commented:
ok, I can get the dates I need in the proper format this way:

$today = date("Y-m-d");
echo $today;

$sixmonths = mktime(0,0,0,date("m"),date("d")+180,date("Y"));
echo "<br>Six Months is ".date("Y-m-d", $sixmonths);

but when I add them to my query they say they are not columns?
Learn SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

Should be the following:

SELECT TOP 5 date ,startTime ,endTime ,title ,details ,room
FROM WWW.dbo.Events
WHERE date BETWEEN GETDATE() and DATEADD(month, 6, GETDATE())
ORDER BY date DESC';

Author

Commented:
Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[42S22]: [Microsoft][SQL Server Native Client 11.0][SQL Server]Invalid column name '$today'.' in C:\inetpub\wwwroot\db2.php:72 Stack trace: #0 C:\inetpub\wwwroot\db2.php(72): PDO->query('SELECT TOP 5 e....') #1 {main} thrown in C:\inetpub\wwwroot\db2.php on line 72
Doing it that way:

$query = "SELECT TOP 5 date, startTime, endTime, title, details, room
FROM WWW.dbo.Events
WHERE date BETWEEN '" . $today . "' and '" . $sixmonths . "'
ORDER BY date DESC;"

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial