By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
461,945 Members | 721 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 461,945 IT Pros & Developers. It's quick & easy.

Selecting records within a date range

P: 7
I need to put together an SQL statement and I can't think of how to make it work properly. The scenario is that I have news items in a database that have a launch time and can optionally have an expire time set. I want to select all records that are above the launch time and below their expire time (if it is set). What I have so far is:

[PHP]$rightnow = mktime();[/PHP]
Expand|Select|Wrap|Line Numbers
  1. SELECT *
  2. FROM teamshadow_news
  3. WHERE (launch_time <= $rightnow AND expire_time = "")
  4. AND NOT expire_time <= $rightnow
  5. ORDER BY timestamp DESC
That is selecting all records with no expire time set, but it is not selecting any records with an expire time set. I have been trying to figure this out for the past day or so and I am stumped. Can anyone help?
Sep 13 '07 #1
Share this Question
Share on Google+
5 Replies

10K+
P: 13,262
I need to put together an SQL statement and I can't think of how to make it work properly. The scenario is that I have news items in a database that have a launch time and can optionally have an expire time set. I want to select all records that are above the launch time and below their expire time (if it is set). What I have so far is:

$rightnow = mktime();

SELECT *
FROM teamshadow_news
WHERE (launch_time <= $rightnow AND expire_time = "")
AND NOT expire_time <= $rightnow
ORDER BY timestamp DESC

That is selecting all records with no expire time set, but it is not selecting any records with an expire time set. I have been trying to figure this out for the past day or so and I am stumped. Can anyone help?
And what do you get if you remove the AND expire_time = "" ?
Sep 13 '07 #2

mwasif
Expert 100+
P: 801
This should work for you
Expand|Select|Wrap|Line Numbers
  1. SELECT *
  2. FROM teamshadow_news
  3. WHERE (NOW() BETWEEN launch_time AND expire_time)
  4. OR (NOW() >= launch_time AND expire_time IS NULL)
  5. ORDER BY TIMESTAMP DESC
Sep 13 '07 #3

P: 7
Then the item will show up, even though it is expired
Sep 13 '07 #4

P: 7
This should work for you
Expand|Select|Wrap|Line Numbers
  1. SELECT *
  2. FROM teamshadow_news
  3. WHERE (NOW() BETWEEN launch_time AND expire_time)
  4. OR (NOW() >= launch_time AND expire_time IS NULL)
  5. ORDER BY TIMESTAMP DESC
Thanks that helped a ton!!! I had to modify it a little bit it works now! Thanks, you have no idea how happy this makes me!!! :)
Sep 13 '07 #5

mwasif
Expert 100+
P: 801
You are welcome. Post back whenever you need help.
Sep 13 '07 #6

Post your reply

Sign in to post your reply or Sign up for a free account.