tricky sql problem - 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

tricky sql problem

Author

Topic

robert323
Starting Member

3 Posts

Posted - 2006-07-26 : 16:54:51

Hello all,
I'm very new to SQL, but find myself with this problem that i've been working on for a while, but I just can't figure out how to work through it.
I've got a database of appointments for vehicles. This database holds start time, month, day, year, end day month, year... etc.

I want to make a query that allows users to select a begining year, month day etc. and ending month year etc.

so i've got this query (I'm using PHP, so the $...'s are just $_Post variables.

so here's my query:
$query = "SELECT * FROM `appointments` WHERE start_year BETWEEN $start_year AND $end_year AND start_month >= $start_month AND end_month <= $end_month AND start_day >= $start_day ORDER BY start_year, start_month, start_day, start_time ASC";

When I try to run this query, if the start month is january, and the end month is january (regardless of year), it returns nothing (because nothing is between january and january, and it's not factoring in the year change. How can I factor in the year?)

Any input is greatly appreciated, I hope you understand where I'm running into trouble.
Thanks again!
-Robert

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-07-26 : 17:49:58

It would be a lot easier if your dates were stored in the table in DATETIME columns.

The best way with what you have will be to convert the columns to DATETIME on the fly. You can use this function:


create function MDate (@Year int, @Month int, @Day int)
returns datetime
as
begin
return dateadd(day,@Day-1,dateadd(month,((@Year-1900)*12)+@Month-1,0))
end
go

Then your query would be something like this:


SELECT
	*
FROM
	appointments 
WHERE
	MDate (start_year,start_month,start_day)
	between
	MDate ($start_year,$start_month,$start_day)
	and
	MDate ($end_year,$end_month,$end_day)

ORDER BY
	start_year,
	start_month,
	start_day,
	start_time ASC

CODO ERGO SUM

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-07-26 : 17:53:16

Don't cross Post:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=69662

CODO ERGO SUM

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources