SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 sql help - dates
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

esthera
Flowing Fount of Yak Knowledge

1393 Posts

Posted - 07/18/2004 :  07:49:49  Show Profile  Reply with Quote
I have a table called subscribers that has a startdate and an enddate (of subscribtion)(date fields -- this is in access)

I am trying to query a month and year to see if that person is subscribed for that month.

Here's my sql. For some odd reason it is not returning records

select * from subscribers where startdate>=#7/1/2004# and enddate<=#7/1/2004#

It should return anyone whose subscriptions month includes 7/1/004.

What am I doing wrong?

Please help?

Kristen
Test

United Kingdom
22415 Posts

Posted - 07/18/2004 :  08:30:01  Show Profile  Reply with Quote
Maybe try
select * from subscribers where startdate>=#7/1/2004# and enddate<#7/2/2004#

Kristen
Go to Top of Page

esthera
Flowing Fount of Yak Knowledge

1393 Posts

Posted - 07/18/2004 :  08:33:05  Show Profile  Reply with Quote
nope same problem. It doesn't return records although there are records that match.
Go to Top of Page

Kristen
Test

United Kingdom
22415 Posts

Posted - 07/18/2004 :  08:55:51  Show Profile  Reply with Quote
Sorry, didn't spot that there were different column name for Start and End of range.

Perhaps I'm not understanding properly, but how can BOTH the Start date be on, or after, 1st July AND the End date be on, or before, 1st July?

Doesn't seem like much of a subscription!

Ah ... Perhaps you need

select * from subscribers where startdate<=#7/1/2004# and enddate>=#7/1/2004#

Kristen
Go to Top of Page

esthera
Flowing Fount of Yak Knowledge

1393 Posts

Posted - 07/18/2004 :  09:42:13  Show Profile  Reply with Quote
Now i'm getting confused.
Basically db has list of subscribers.
If subscriber is only for July then his subscripition starts july and ends july. If subscriber is for july to september then starts july and finished september.
What is the correct sql statement that I need to select all users that are subscribed for July?
Go to Top of Page

Kristen
Test

United Kingdom
22415 Posts

Posted - 07/18/2004 :  10:03:41  Show Profile  Reply with Quote
Well lets try a sensibility-test against my previous query
select * from subscribers where startdate<=#7/1/2004# and enddate>=#7/1/2004#

Your subscriber 1: 1st July to 31st July - test:
where "1st July" <=#7/1/2004# and "31st july" >=#7/1/2004#
will give TRUE

Your subscriber 2: 1st July to 30th September - test:
where "1st July" <=#7/1/2004# and "30th September" >=#7/1/2004#
will give TRUE

To check for all users who are subscribed in July it depends a bit on how the Start / End dates are stored. Are they always First and Last day of the month?, or if I subscribe on the 15th is my Start date the 15th and my end date the 14th?

I expect that the safest method, if subscription dates can be within the month, but subscription is for WHOLE MONTHS (i.e. a different method will be needed for a Weekly or Daily publication) is

select * from subscribers where startdate<#8/1/2004# and enddate>=#7/1/2004#

In English: "My start date is Before August 2004, and my End date is after July 2004 or some time IN July 2004"

But if subscription dates are always stored as the 1st of the month then my previous query should be fine

select * from subscribers where startdate<=#7/1/2004# and enddate>=#7/1/2004#

Again, in English: "My start date is on, or before, 1st July 2004 and my End Date is 1st July 2004 or later"

Kristen
Go to Top of Page

esthera
Flowing Fount of Yak Knowledge

1393 Posts

Posted - 07/18/2004 :  11:29:21  Show Profile  Reply with Quote
Thanks for your help. I think it is working now.
Go to Top of Page

Kristen
Test

United Kingdom
22415 Posts

Posted - 07/18/2004 :  11:33:21  Show Profile  Reply with Quote
Excellent - well done!
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000