DateTime

I have to check whether a given date is between a day and a month.
A guided tour is only scheduled from november 1st until april 1st.
when i want to make a reservation for the tour Today the query should
tell me that today is out of range.

In the database we store this as seperate fields:
from day integer 1 to 7
from month integer 1 to 12

till day integer 1 to 7
till month integer 1 to 12

when Today is between november 1st and march 30 then NoGood
when Today is not between november 1st and march 30 then Proceed

I have tried a number of variations but without any results yet.
I hope someone can give a tip on how to solve this particular case
Many thanks in advance
eddyHi

You could try something like:

DECLARE @.baseDate datetime
SET @.baseDate = '20021231'

SELECT *
FROM Tours
WHERE getdate() BETWEEN dateadd(Month, [From Month], dateadd ( day, [From
Day], @.baseDate ) )
AND dateadd(Month, [To Month], dateadd ( day, [To Day], @.baseDate ) )

John

"Eddy" <eddy@.atwork4u.be> wrote in message
news:3f819965$0$25656$ba620e4c@.reader0.news.skynet .be...
> I have to check whether a given date is between a day and a month.
> A guided tour is only scheduled from november 1st until april 1st.
> when i want to make a reservation for the tour Today the query should
> tell me that today is out of range.
> In the database we store this as seperate fields:
> from day integer 1 to 7
> from month integer 1 to 12
> till day integer 1 to 7
> till month integer 1 to 12
> when Today is between november 1st and march 30 then NoGood
> when Today is not between november 1st and march 30 then Proceed
> I have tried a number of variations but without any results yet.
> I hope someone can give a tip on how to solve this particular case
> Many thanks in advance
> eddy|||> In the database we store this as seperate fields:
> from day integer 1 to 7
> from month integer 1 to 12

Why? Not a very effective way to store dates when you have a proper DATETIME
datatype to do the job. Change your columns to DATETIME.

If you want to ignore the year then just put a dummy year number on your
dates:

CREATE TABLE Sometable (fromdate DATETIME NOT NULL, todate DATETIME, PRIMARY
KEY (fromdate), CHECK (fromdate<todate))
INSERT INTO Sometable VALUES ('19001101','19010401')

Then do the query like this:

DECLARE @.some_date DATETIME
SET @.some_date = CURRENT_TIMESTAMP

SELECT *
FROM Sometable
WHERE DATEADD(YEAR,YEAR(fromdate)-YEAR(@.some_date),@.some_date) BETWEEN
fromdate AND todate
OR DATEADD(YEAR,YEAR(fromdate)-YEAR(@.some_date)+1,@.some_date) BETWEEN
fromdate AND todate

Of course, if the year is relevant you can just do:

SELECT *
FROM Sometable
WHERE @.some_date BETWEEN fromdate AND todate

--
David Portas
----
Please reply only to the newsgroup
--|||Thanks for the tip...
I have considered datetime fields a while ago...
It is more or less working adding a dummy year into the equation...
but i'll reconsider.
Having a year means the users will have to add a record for the coming
years. But it could prove to be more flexible this way.
Anyway thanks for the help...

David Portas wrote:

>>In the database we store this as seperate fields:
>>from day integer 1 to 7
>>from month integer 1 to 12
>
> Why? Not a very effective way to store dates when you have a proper DATETIME
> datatype to do the job. Change your columns to DATETIME.
> If you want to ignore the year then just put a dummy year number on your
> dates:
> CREATE TABLE Sometable (fromdate DATETIME NOT NULL, todate DATETIME, PRIMARY
> KEY (fromdate), CHECK (fromdate<todate))
> INSERT INTO Sometable VALUES ('19001101','19010401')
> Then do the query like this:
> DECLARE @.some_date DATETIME
> SET @.some_date = CURRENT_TIMESTAMP
> SELECT *
> FROM Sometable
> WHERE DATEADD(YEAR,YEAR(fromdate)-YEAR(@.some_date),@.some_date) BETWEEN
> fromdate AND todate
> OR DATEADD(YEAR,YEAR(fromdate)-YEAR(@.some_date)+1,@.some_date) BETWEEN
> fromdate AND todate
> Of course, if the year is relevant you can just do:
> SELECT *
> FROM Sometable
> WHERE @.some_date BETWEEN fromdate AND todate|||Hi,
thanks for the reply.
need to think this through though ... what date is @.BaseDate

getdate() could be about any date the user choses

thanks
eddy galle

John Bell wrote:

> Hi
> You could try something like:
> DECLARE @.baseDate datetime
> SET @.baseDate = '20021231'
> SELECT *
> FROM Tours
> WHERE getdate() BETWEEN dateadd(Month, [From Month], dateadd ( day, [From
> Day], @.baseDate ) )
> AND dateadd(Month, [To Month], dateadd ( day, [To Day], @.baseDate ) )
> John
> "Eddy" <eddy@.atwork4u.be> wrote in message
> news:3f819965$0$25656$ba620e4c@.reader0.news.skynet .be...
>>I have to check whether a given date is between a day and a month.
>>A guided tour is only scheduled from november 1st until april 1st.
>>when i want to make a reservation for the tour Today the query should
>>tell me that today is out of range.
>>
>>In the database we store this as seperate fields:
>>from day integer 1 to 7
>>from month integer 1 to 12
>>
>>till day integer 1 to 7
>>till month integer 1 to 12
>>
>>when Today is between november 1st and march 30 then NoGood
>>when Today is not between november 1st and march 30 then Proceed
>>
>>I have tried a number of variations but without any results yet.
>>I hope someone can give a tip on how to solve this particular case
>>Many thanks in advance
>>eddy
>>|||> Having a year means the users will have to add a record for the coming
> years. But it could prove to be more flexible this way.

Have another look at my example. You don't have to do this - the query works
without it.

--
David Portas
----
Please reply only to the newsgroup
--|||In article <16mdnUmGHswBIRyiRVn-vg@.giganews.com>,
REMOVE_BEFORE_REPLYING_dportas@.acm.org says...
> > Having a year means the users will have to add a record for the coming
> > years. But it could prove to be more flexible this way.
> Have another look at my example. You don't have to do this - the query works
> without it.

I would think that it would be important to have a year because
depending on the year, the difference between two identical dates can
differ.

-- Rick|||Hi

Basedate is the date that you used to get the offset for your month and year
columns.

John

"Eddy" <eddy@.atwork4u.be> wrote in message
news:3f81a7da$0$24180$ba620e4c@.reader0.news.skynet .be...
> Hi,
> thanks for the reply.
> need to think this through though ... what date is @.BaseDate
> getdate() could be about any date the user choses
> thanks
> eddy galle
> John Bell wrote:
> > Hi
> > You could try something like:
> > DECLARE @.baseDate datetime
> > SET @.baseDate = '20021231'
> > SELECT *
> > FROM Tours
> > WHERE getdate() BETWEEN dateadd(Month, [From Month], dateadd ( day,
[From
> > Day], @.baseDate ) )
> > AND dateadd(Month, [To Month], dateadd ( day, [To Day], @.baseDate ) )
> > John
> > "Eddy" <eddy@.atwork4u.be> wrote in message
> > news:3f819965$0$25656$ba620e4c@.reader0.news.skynet .be...
> >>I have to check whether a given date is between a day and a month.
> >>A guided tour is only scheduled from november 1st until april 1st.
> >>when i want to make a reservation for the tour Today the query should
> >>tell me that today is out of range.
> >>
> >>In the database we store this as seperate fields:
> >>from day integer 1 to 7
> >>from month integer 1 to 12
> >>
> >>till day integer 1 to 7
> >>till month integer 1 to 12
> >>
> >>when Today is between november 1st and march 30 then NoGood
> >>when Today is not between november 1st and march 30 then Proceed
> >>
> >>I have tried a number of variations but without any results yet.
> >>I hope someone can give a tip on how to solve this particular case
> >>Many thanks in advance
> >>eddy
> >