Dates in a date range

[PromisedOyster]

Is there a way that I can get a resultset that contains unique dates in
a given date range without the need to have a temporary table and a
cursor?

perhaps something like:

declare @start_date as datetime
declare @end_date as datetime
set @start_date as '1/1/2005'
set @end_date as '1/1/2006'
select fn_getuniquedate(@start_date, @end_date)
1/1/2005
1/2/2005
1/3/2005
...
12/31/2005

[David Portas]

Any reason why you can't create a permanent Calendar table in your database?
Calendars are useful for many types of query so it makes sense to have one
if you need to do anything with dates.

SELECT cal_date
FROM Calendar
WHERE cal_date BETWEEN @start_date AND @end_date ;

Otherwise, you could write an iterative table-valued function to generate
the data. Unlikely to perform better than a permanent table in most cases
though.

--
David Portas
SQL Server MVP
--
"PromisedOyster" <Pr************@hotmail.com> wrote in message
news:11**********************@g49g2000cwa.googlegr oups.com...

Is there a way that I can get a resultset that contains unique dates in
a given date range without the need to have a temporary table and a
cursor?

perhaps something like:

declare @start_date as datetime
declare @end_date as datetime
set @start_date as '1/1/2005'
set @end_date as '1/1/2006'
select fn_getuniquedate(@start_date, @end_date)
1/1/2005
1/2/2005
1/3/2005
..
12/31/2005

[--CELKO--]

Get a copy of SQL FOR SMARTIES and look up the uses for a Calendar
table. You need to stop thinking about functions and start thinking in
terms of tables and joins.

[Erland Sommarskog]

PromisedOyster (Pr************@hotmail.com) writes:

Is there a way that I can get a resultset that contains unique dates in
a given date range without the need to have a temporary table and a
cursor?

perhaps something like:

declare @start_date as datetime
declare @end_date as datetime
set @start_date as '1/1/2005'
set @end_date as '1/1/2006'
select fn_getuniquedate(@start_date, @end_date)

As David and Celko said, better store this in a table once for all.
What they didn't say was how to fill it. Here is how I fill our dates
table with dates from 1990 to 2150. Adapt as you like:

TRUNCATE TABLE dates
go
-- Get a temptable with numbers. This is a cheap, but not 100% reliable.
-- Whence the query hint and all the checks.
SELECT TOP 80001 n = IDENTITY(int, 0, 1)
INTO #numbers
FROM sysobjects o1
CROSS JOIN sysobjects o2
CROSS JOIN sysobjects o3
CROSS JOIN sysobjects o4
OPTION (MAXDOP 1)
go
-- Make sure we have unique numbers.
CREATE UNIQUE CLUSTERED INDEX num_ix ON #numbers (n)
go
-- Verify that table does not have gaps.
IF (SELECT COUNT(*) FROM #numbers) = 80001 AND
(SELECT MIN(n) FROM #numbers) = 0 AND
(SELECT MAX(n) FROM #numbers) = 80000
BEGIN
DECLARE @msg varchar(255)

-- Insert the dates:
INSERT dates (thedate)
SELECT dateadd(DAY, n, '19800101')
FROM #numbers
WHERE dateadd(DAY, n, '19800101') < '21500101'

SELECT @msg = 'Inserted ' + ltrim(str(@@rowcount)) +
' rows into #numbers'
PRINT @msg
END
ELSE
RAISERROR('#numbers is not contiguos from 0 to 80001!', 16, -1)
go
DROP TABLE #numbers

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[PromisedOyster]

Thanks for your help, but I do not have CREATE TABLE permissions for
this particular database, hence the request. I only have SELECT
permission.

Thanks Erland for your assistance on populating the dates table, but
that seems a very complicated way to do it. I normally stick it in a
while loop and do a DateAdd. Sure, it might not be efficient but that
is not an issue

[Erland Sommarskog]

PromisedOyster (Pr************@hotmail.com) writes:

Thanks for your help, but I do not have CREATE TABLE permissions for
this particular database, hence the request. I only have SELECT
permission.
So use a table varaible or a temp table.
Thanks Erland for your assistance on populating the dates table, but
that seems a very complicated way to do it. I normally stick it in a
while loop and do a DateAdd. Sure, it might not be efficient but that
is not an issue

Complicated? Well, if dateadd() is good enough to you, why did you
even bother to ask? :-)

The script uses a table of numbers, which is a common way to solve SQL
problems where you need a range of values.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[PromisedOyster]

Thanks.

But I didn't ask about how to populate it.

[--CELKO--]

>> I normally stick it in a WHILE loop and do a DateAdd. <<

Sure, why learn SQL and RDBMS when you can use proprietary code that
looks like BASIC, Cobol or your native 3GL language?

Sure, it might not be efficient but that is not an issue <<

Do you put that on your resume or tell your boss that "my code sucks.
but it is not an issue"? Wow!!

Pretend that you are a professional programmer. Go to the guy with
permissions add tables to the schema and get himto do what he should
have done if he had been a professional, so you have a calendar and a
sequence table. This is so fundamental I cannot understand why they
are not there.

[--CELKO--]

>> Thanks Erland for your assistance on populating the dates table, but that seems a very complicated way to do it. <<

Could you please show us the portable, un-complicated code for
determining Easter, Chinese New Year and the Jewish holiidays? The
150+ fiscal calendars under GAAP?

Build a calendar table with one column for the calendar data and other
columns to show whatever your business needs in the way of temporal
information. Do not try to calculate holidays in SQL -- Easter alone
requires too much math.

CREATE TABLE Calendar
(cal_date DATE NOT NULL PRIMARY KEY,
fiscal_year SMALLINT NOT NULL,
fiscal_month SMALLINT NOT NULL,
week_in_year SMALLINT NOT NULL, -- SQL server is not ISO standard
holiday SMALLINT NOT NULL
CHECK(holiday IN (0,1)),
day_in_year SMALLINT NOT NULL,
...);
A calendar table for US Secular holidays can be built from the data at
this website, so you will get the three-day weekends:

http://www.smart.net/~mmontes/ushols.html

[Erland Sommarskog]

--CELKO-- (jc*******@earthlink.net) writes:

Thanks Erland for your assistance on populating the dates table, but
that seems a very complicated way to do it. <<

Could you please show us the portable, un-complicated code for
determining Easter, Chinese New Year and the Jewish holiidays? The
150+ fiscal calendars under GAAP?

I don't think that he was asking for. He only wanted the days flat out,
no mention of holidays or anything.
Build a calendar table with one column for the calendar data and other
columns to show whatever your business needs in the way of temporal
information. Do not try to calculate holidays in SQL -- Easter alone
requires too much math.

And he didn't have the privs to create tables.

Rather than using canned rants, try to read people posts. If you ever
care to be helpful, that is.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[PromisedOyster]

Why would I want to show you portable, un-complicated code for
determining Easter, Chinese New Year and the Jewish holiidays? These
are of no relevance or interest to us whatsoever as Erland also pointed
out.

--CELKO-- wrote:

Thanks Erland for your assistance on populating the dates table, but that seems a very complicated way to do it. <<

Could you please show us the portable, un-complicated code for
determining Easter, Chinese New Year and the Jewish holiidays? The
150+ fiscal calendars under GAAP?

Build a calendar table with one column for the calendar data and other
columns to show whatever your business needs in the way of temporal
information. Do not try to calculate holidays in SQL -- Easter alone
requires too much math.

CREATE TABLE Calendar
(cal_date DATE NOT NULL PRIMARY KEY,
fiscal_year SMALLINT NOT NULL,
fiscal_month SMALLINT NOT NULL,
week_in_year SMALLINT NOT NULL, -- SQL server is not ISO standard
holiday SMALLINT NOT NULL
CHECK(holiday IN (0,1)),
day_in_year SMALLINT NOT NULL,
...);
A calendar table for US Secular holidays can be built from the data at
this website, so you will get the three-day weekends:

http://www.smart.net/~mmontes/ushols.html

[PromisedOyster]

Well Celko, I have been in the IT industry for a number of years now
and without a doubt you must be one of the rudest and most arrogant
people I have came across.

Reading some other postings, I am not alone in my view.

[--CELKO--]

The Calendar table is not just for this one problem. It is an
auxiliary table that serves the ENTIRE schema. Think in terms of
general, global code instead of handling each problem as a
self-contained one-shot. A data model is a whole, not disjoint parts.

So the ability to use a fiscal calendar is not required by your
accouting department? Your Human Resources department does not care
about holidays?

[Erland Sommarskog]

--CELKO-- (jc*******@earthlink.net) writes:

The Calendar table is not just for this one problem. It is an
auxiliary table that serves the ENTIRE schema. Think in terms of
general, global code instead of handling each problem as a
self-contained one-shot. A data model is a whole, not disjoint parts.

So the ability to use a fiscal calendar is not required by your
accouting department? Your Human Resources department does not care
about holidays?

Tell me Celko, in your previous life when you sold vacuum cleaner's
at people's doors, were you really successful only because you were
so tiresome insisting that they bought one only to get rid of you?

We have no idea what business problem PromisedOyster has, so it's
quite pointless to cram that calender table down this throat.
Particularly, since when we know he does not have have privileges
to create tables anyway.)

And for that matter, our database has a dates table which is a single-column
table with all dates from 1990-01-01 to 2150-01-01. Simply your table of
numbers, but with dates. We need to be able to insert/update data into
historic tables over a date range. Holidays etc? Yes, there is table
for this as well, but it only has entries for Mon-Fri that are not
business days, and it's maintained by users. This table could never
serves as the date table that I mentioned previously, can you see why?

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[--CELKO--]

>> We have no idea what business problem PromisedOyster has, so it's quite pointless to cram that calender table down this throat. <<

Okay, what are the odds that he lives in a world without time and aksed
this question?

Particularly, since when we know he does not have have privileges to create tables anyway.<<
So the right answer to temporal problems change depending on your
privileges? No, it does not. The abiltiy to do the right thing might
change depending on your privileges, but the answer does not. And as a
professional it is your duty to speak the truth.
Simply your table of numbers, but with dates. <<
When I do a Sequence table, I often have other columns such as number
names, a random number, a weird function, etc..
Holidays etc? Yes, there is table for this as well, but it only has entries for Mon-Fri that are not business days, and it's maintained by users. <<

Keeping temporal data in two places as you proposed sounds like
attribute splitting. Can you tell me why a holiday is a logically
different kind of thing from anyother date?

[Erland Sommarskog]

--CELKO-- (jc*******@earthlink.net) writes:

We have no idea what business problem PromisedOyster has, so it's quite pointless to cram that calender table down this throat. <<

Okay, what are the odds that he lives in a world without time and aksed
this question?
Few people live in a world without food and water. Does that mean
that as soon as we design a database, we must have food and warer in ir?
So the right answer to temporal problems change depending on your
privileges? No, it does not. The abiltiy to do the right thing might
change depending on your privileges, but the answer does not. And as a
professional it is your duty to speak the truth.
As a professional it is our duty to help people with the problems they
present. Not the problems we invent outselves. All we know is that
Promised Oyster needs is a temporary table of some sort that gives
him all dates in an interval.

It is also our professional duty to behavely politely and respectfully
towards people.
Holidays etc? Yes, there is table for this as well, but it only has

entries for Mon-Fri that are not business days, and it's maintained by
users. <<
Keeping temporal data in two places as you proposed sounds like
attribute splitting. Can you tell me why a holiday is a logically
different kind of thing from anyother date?

The two tables serves different purposes. The table with all the dates
puts no attributes on the dates, and is only a help table for some
operations. The other table lists only holidays. Neither that table
has any other data beside the primary key, beside auditing data. But
there is an important difference between the two tables, let's see if
you can spot it!
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[--CELKO--]

>> All we know is that Promised Oyster needs is a temporary table of some sort that gives him all dates in an interval. <<

No. We have experence and have seen this before. This is like a
doctor who treats every pain with a dose of drugs versus a doctor who
actually diagnoses the problem and looks for a long-term solution.

The table with all the dates puts no attributes on the dates, and is only a help table for some operations. The other table lists only holidays. <<

Would you also design a schema with a table for male employees and one
for female employees? That wouild be splitting the entit on the gender
attribute. The holidays and non-holidays are still days. The holiday
attribute can change by decree or by definition (Easter, Chinese New
Years or other lunar-solar calendar holidays). Nobody can stop time or
skip a day.

As a simple test, when you have a printed calendar, do you put the
holidays on the pages of the calendar or on a separate piece of paper
on the other side fo the room? Can a holiday exist without a date
(ii.e. Can I put Christmas in a bottle by itself and pull it out as
needed)?

[Erland Sommarskog]

--CELKO-- (jc*******@earthlink.net) writes:

All we know is that Promised Oyster needs is a temporary table of some sort that gives him all dates in an interval. <<

No. We have experence and have seen this before. This is like a
doctor who treats every pain with a dose of drugs versus a doctor who
actually diagnoses the problem and looks for a long-term solution.
And you very much go for the former, I see.
The table with all the dates puts no attributes on the dates, and is

only a help table for some operations. The other table lists only holidays.
<<
Would you also design a schema with a table for male employees and one
for female employees? That wouild be splitting the entit on the gender
attribute. The holidays and non-holidays are still days. The holiday
attribute can change by decree or by definition (Easter, Chinese New
Years or other lunar-solar calendar holidays). Nobody can stop time or
skip a day.

As a simple test, when you have a printed calendar, do you put the
holidays on the pages of the calendar or on a separate piece of paper
on the other side fo the room? Can a holiday exist without a date
(ii.e. Can I put Christmas in a bottle by itself and pull it out as
needed)?

Oh, you still don't get it! Here you come with canned responses
about calendar tables, and then you cannot model them properly. So,
OK, the proper definition depends on the business needs. And,
no, this have nothing to do whether there it is a sparse table with
only the holidays, or if all days of the year is in table. It's another
issue that makes it impossible for me to have the holidays in the
same table that has a single row for each day from 1990-01-01 to
2149-12-31. Try to use a little imagination, it's not difficult at
all!
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[corey lawson]

Erland Sommarskog wrote:

--CELKO-- (jc*******@earthlink.net) writes:

Thanks Erland for your assistance on populating the dates table, but
that seems a very complicated way to do it. <<
Could you please show us the portable, un-complicated code for
determining Easter, Chinese New Year and the Jewish holiidays? The
150+ fiscal calendars under GAAP?

Store them in a table. Why bother calculating them? It's not worth the
effort.


I don't think that he was asking for. He only wanted the days flat out,
no mention of holidays or anything.
Yep, and the simplest way to do this is to create a table just with
dates in it from 1/1/2000 to whenever, like JCelko pointed out.

Build a calendar table with one column for the calendar data and other
columns to show whatever your business needs in the way of temporal
information. Do not try to calculate holidays in SQL -- Easter alone
requires too much math.

And he didn't have the privs to create tables.

So what? Shouldn't he be able to ASK someone with the privs to get the
table created? It has worked well for me in the past as a contractor at
other companies...
Rather than using canned rants, try to read people posts. If you ever
care to be helpful, that is.

Whatever floats your boat...

[corey lawson]

Erland Sommarskog wrote:

The two tables serves different purposes. The table with all the dates
puts no attributes on the dates, and is only a help table for some
operations. The other table lists only holidays. Neither that table
has any other data beside the primary key, beside auditing data. But
there is an important difference between the two tables, let's see if
you can spot it!

Yes, using the second table with just holidays is a PITA. Flagging dates
in the calendar as various holidays is far easier to use. The list of
holidays can be derived from the calendar table easily enough.

[Erland Sommarskog]

corey lawson (co**********@ayeteatea.net) writes:

Erland Sommarskog wrote:

The two tables serves different purposes. The table with all the dates
puts no attributes on the dates, and is only a help table for some
operations. The other table lists only holidays. Neither that table
has any other data beside the primary key, beside auditing data. But
there is an important difference between the two tables, let's see if
you can spot it!

Yes, using the second table with just holidays is a PITA. Flagging dates
in the calendar as various holidays is far easier to use.

No. Well, in the case you only care about one country, it is. We need
to keep track of non-business days in several countries.


--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[corey lawson]

Erland Sommarskog wrote:

corey lawson (co**********@ayeteatea.net) writes:

Erland Sommarskog wrote:

The two tables serves different purposes. The table with all the dates
puts no attributes on the dates, and is only a help table for some
operations. The other table lists only holidays. Neither that table
has any other data beside the primary key, beside auditing data. But
there is an important difference between the two tables, let's see if
you can spot it!

Yes, using the second table with just holidays is a PITA. Flagging dates
in the calendar as various holidays is far easier to use.

No. Well, in the case you only care about one country, it is. We need
to keep track of non-business days in several countries.

So you add other fields to help identify different holidays, right?
At the very least, Chicago celebrates Kasmir Pulaski Day (it's an
official Chicago holiday, in that schools and city offices are closed.
I'll refrain from making any derisive comments about Hizzoner Daley).

As far as quickly populating a calendar table, sometimes it's just
quicker to fire up Excel, start in A1 with "1/1/2005", and drag it down
to A65535 or so to autofill the dates forward, and then import it into a
table.

It's just so much easier working with a calendar table like this (theta
joins work pretty dang good), so that for the person who asked, the DBA
should be able to find SOMEWHERE in the database, even in the master
database (gasp! shock! horror!). If done right, it'll be static for
quite some time (years), and should be relatively obvious for a database
geek to realize if it's getting near the end of time to extend it again.

Besides, if you're using non-calendar accounting periods (i.e., 4-4-5,
13-wk qtrs, etc), it's about the only way to make them sane, that is, if
you're not an accountant.

[Erland Sommarskog]

corey lawson (co**********@ayeteatea.net) writes:

So you add other fields to help identify different holidays, right?
At the very least, Chicago celebrates Kasmir Pulaski Day (it's an
official Chicago holiday, in that schools and city offices are closed.
I'll refrain from making any derisive comments about Hizzoner Daley).
This far we have not had reason to care why there is a holiday. All we
care about is whether this is a day when the stock exchange and the
clearing houses are open. There might be need for changes further down
the road, but this model has served us well since 1992.

The bottom line is that different systems have different needs, and
believing that there is a universal defintion of a calendar that fits
all systems is a fallacy. Some systems have no need of a calendar at
all. Other systems only needs to cover the local customs, others need
to cover local holidays like those in Chicago. And ours need to work
only country level, but maybe one day we might have to move it to be
by market place and clearing house. Etc.

And since needs are different, one should not cram down a calendar table
down the throat of anyone who is asking.
It's just so much easier working with a calendar table like this (theta
joins work pretty dang good), so that for the person who asked, the DBA
should be able to find SOMEWHERE in the database, even in the master
database (gasp! shock! horror!). If done right, it'll be static for
quite some time (years), and should be relatively obvious for a database
geek to realize if it's getting near the end of time to extend it again.

Maybe there is one. May there isn't one. Maybe the DBA for political
reasons will not let use the table. Again, please stop cramming down
solutions down people's throat, when they clearly tell you that the
solution you have is not applicable!

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[corey lawson]

Erland Sommarskog wrote:

corey lawson (co**********@ayeteatea.net) writes:

So you add other fields to help identify different holidays, right?
At the very least, Chicago celebrates Kasmir Pulaski Day (it's an
official Chicago holiday, in that schools and city offices are closed.
I'll refrain from making any derisive comments about Hizzoner Daley).

This far we have not had reason to care why there is a holiday. All we
care about is whether this is a day when the stock exchange and the
clearing houses are open. There might be need for changes further down
the road, but this model has served us well since 1992.

The bottom line is that different systems have different needs, and
believing that there is a universal defintion of a calendar that fits
all systems is a fallacy. Some systems have no need of a calendar at
all. Other systems only needs to cover the local customs, others need
to cover local holidays like those in Chicago. And ours need to work
only country level, but maybe one day we might have to move it to be
by market place and clearing house. Etc.

And since needs are different, one should not cram down a calendar table
down the throat of anyone who is asking.

It's just so much easier working with a calendar table like this (theta
joins work pretty dang good), so that for the person who asked, the DBA
should be able to find SOMEWHERE in the database, even in the master
database (gasp! shock! horror!). If done right, it'll be static for
quite some time (years), and should be relatively obvious for a database
geek to realize if it's getting near the end of time to extend it again.

Maybe there is one. May there isn't one. Maybe the DBA for political
reasons will not let use the table. Again, please stop cramming down
solutions down people's throat, when they clearly tell you that the
solution you have is not applicable!

If he can't create a calendar table, how's he gonna get a Holidays table
created?

[Erland Sommarskog]

corey lawson (co**********@ayeteatea.net) writes:

If he can't create a calendar table, how's he gonna get a Holidays table
created?

If you care to review the thread, you will find that he never asked for
one, and that he also said that he didn't need the holidays. All he
wanted was the dates for one single year. The holidays were invented by
other people that, rather than trying to help, answered some imaginary
question.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp