|
|
You're in the
sql server (alternate)
group:DateTime
sql server (alternate):
REMOVE_BEFORE_REPLYING_dportas@acm.org says... [quoted text, click to view] > > 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
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 [quoted text, click to view] "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 >
[quoted text, click to view]
> 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 --
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
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... [quoted text, click to view] 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 [quoted text, click to view] 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 >> > > >
[quoted text, click to view]
> 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 --
Hi
Basedate is the date that you used to get the offset for your month and year columns. John [quoted text, click to view] "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 > >> > > > > > > >
Don't see what you're looking for? Try a search.
|
January 2003
June 2003
July 2003
August 2003
September 2003
October 2003
November 2003
December 2003
January 2004
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
| home · blog · groups | Questions? Comments? Contact the dn |