sql help - dates - 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

SQL Server 2000 Forums

Transact-SQL (2000)

sql help - dates

Author

Topic

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2004-07-18 : 07:49:49

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

22859 Posts

Posted - 2004-07-18 : 08:30:01

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

Kristen

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2004-07-18 : 08:33:05

nope same problem. It doesn't return records although there are records that match.

Kristen
Test

22859 Posts

Posted - 2004-07-18 : 08:55:51

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

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2004-07-18 : 09:42:13

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?

Kristen
Test

22859 Posts

Posted - 2004-07-18 : 10:03:41

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

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2004-07-18 : 11:29:21

Thanks for your help. I think it is working now.

Kristen
Test

22859 Posts

Posted - 2004-07-18 : 11:33:21

Excellent - well done!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources