DateTime

**John Bell** · Jul 20 '05, 01:41 AM

Re: DateTime

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.s kynet.be...[color=blue]
> 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
>[/color]

**David Portas** · Jul 20 '05, 01:41 AM

Re: DateTime

> In the database we store this as seperate fields:[color=blue]
> from day integer 1 to 7
> from month integer 1 to 12[/color]

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<todat e))
INSERT INTO Sometable VALUES ('19001101','19 010401')

Then do the query like this:

DECLARE @some_date DATETIME
SET @some_date = CURRENT_TIMESTA MP

SELECT *
FROM Sometable
WHERE DATEADD(YEAR,YE AR(fromdate)-YEAR(@some_date ),@some_date) BETWEEN
fromdate AND todate
OR DATEADD(YEAR,YE AR(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** · Jul 20 '05, 01:41 AM

Re: DateTime

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:
[color=blue][color=green]
>>In the database we store this as seperate fields:
>>from day integer 1 to 7
>>from month integer 1 to 12[/color]
>
>
> 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<todat e))
> INSERT INTO Sometable VALUES ('19001101','19 010401')
>
> Then do the query like this:
>
> DECLARE @some_date DATETIME
> SET @some_date = CURRENT_TIMESTA MP
>
> SELECT *
> FROM Sometable
> WHERE DATEADD(YEAR,YE AR(fromdate)-YEAR(@some_date ),@some_date) BETWEEN
> fromdate AND todate
> OR DATEADD(YEAR,YE AR(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
>[/color]

**Eddy** · Jul 20 '05, 01:42 AM

Re: DateTime

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:
[color=blue]
> 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.s kynet.be...
>[color=green]
>>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
>>[/color]
>
>
>[/color]

**David Portas** · Jul 20 '05, 01:42 AM

Re: DateTime

> Having a year means the users will have to add a record for the coming[color=blue]
> years. But it could prove to be more flexible this way.[/color]

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** · Jul 20 '05, 01:42 AM

Re: DateTime

In article <16mdnUmGHswBIR yiRVn-vg@giganews.com >,
REMOVE_BEFORE_R EPLYING_dportas @acm.org says...[color=blue][color=green]
> > 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.[/color]
>
> Have another look at my example. You don't have to do this - the query works
> without it.[/color]

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** · Jul 20 '05, 01:43 AM

Re: DateTime

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.s kynet.be...[color=blue]
> 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:
>[color=green]
> > Hi
> >
> > You could try something like:
> >
> > DECLARE @baseDate datetime
> > SET @baseDate = '20021231'
> >
> > SELECT *
> > FROM Tours
> > WHERE getdate() BETWEEN dateadd(Month, [From Month], dateadd ( day,[/color][/color]
[From[color=blue][color=green]
> > 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.s kynet.be...
> >[color=darkred]
> >>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
> >>[/color]
> >
> >
> >[/color]
>[/color]

DateTime

DateTime

Comment

Comment

Comment

Comment

Comment

Comment

Comment