DateTime

[Eddy]

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

[John Bell]

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" <ed**@atwork4u.be> wrote in message
news:3f***********************@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

[David Portas]

> 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
--

[Eddy]

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

[Eddy]

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" <ed**@atwork4u.be> wrote in message
news:3f***********************@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

[David Portas]

> 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
--

[Guinness Mann]

In article <16********************@giganews.com>,
RE****************************@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

[John Bell]

Hi

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

John

"Eddy" <ed**@atwork4u.be> wrote in message
news:3f***********************@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" <ed**@atwork4u.be> wrote in message
news:3f***********************@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