Day of the week

I have a table whcih contains order Id (orderid_c), and order date
(orderdate_d).
Is there anywhere I can program to count the number of order from Monday to
the day the report is run, for example, when I run the report on Wednesday,
the report will cover from Monday to Wednesday and when I run the report on
Thursday, the report will cover from Monday to Thursday. I will have to run
the report several time during the business hour.
Thanks,set datefirst 1
select count(orderid_c) from table
where datepart(wk,orderdate_d) = datepart(wk,getdate())
"qjlee" <qjlee@.discussions.microsoft.com> wrote in message
news:9FCC02A9-29B8-48B1-B888-091BBC502CFD@.microsoft.com...
> I have a table whcih contains order Id (orderid_c), and order date
> (orderdate_d).
> Is there anywhere I can program to count the number of order from Monday
to
> the day the report is run, for example, when I run the report on
Wednesday,
> the report will cover from Monday to Wednesday and when I run the report
on
> Thursday, the report will cover from Monday to Thursday. I will have to
run
> the report several time during the business hour.
>
> Thanks,
>|||sp_who will tell you who and what database
"qjlee" wrote:

> I have a table whcih contains order Id (orderid_c), and order date
> (orderdate_d).
> Is there anywhere I can program to count the number of order from Monday t
o
> the day the report is run, for example, when I run the report on Wednesday
,
> the report will cover from Monday to Wednesday and when I run the report o
n
> Thursday, the report will cover from Monday to Thursday. I will have to r
un
> the report several time during the business hour.
>
> Thanks,
>|||On Thu, 18 Aug 2005 10:31:01 -0700, qjlee wrote:

>I have a table whcih contains order Id (orderid_c), and order date
>(orderdate_d).
>Is there anywhere I can program to count the number of order from Monday to
>the day the report is run, for example, when I run the report on Wednesday,
>the report will cover from Monday to Wednesday and when I run the report on
>Thursday, the report will cover from Monday to Thursday. I will have to ru
n
>the report several time during the business hour.
Hi qjlee,
Here's how to select data between "last monday" and "now":
SELECT ...
FROM ...
WHERE TheDate >= DATEADD(day, DATEDIFF(day, '20050103',
CURRENT_TIMESTAMP) / 7 * 7, '20050103')
AND TheDate <= CURRENT_TIMESTAMP
AMD ...
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)sql

datetime query

my database contains 1 field with "datetime" in sql server
hw can i query the database to just compare the date section of that field ? and not the time
WHERE CONVERT(VARCHAR(10),datecolumn,101) >= '08/06/2005'
|||Although Dinakar's suggestion will work, it will not perform as well as something like this:

WHERE datecolumn >= '20050806' AND datecolumn < '20050807'

When you perform a function on a column (such as CONVERT) this willmake the query non-sargable and will therefore not take advantage of anindex on the column.
For more tips on performance, seeSQL Server Transact-SQL WHERE Clause.

DateTime Formatting

Have a cell in a table that is being populated by a field in database that
contains the date and time in this format mm/dd/yyyy hh:mm AM. I need it to
be the otherway around ie. dd/mm/yyyy hh:mm AM/PM. But when i try and use
=Format(DateTime.Value, "dd/MM/yyyy hh:mm") is get an error saying that the
hh is not declared.
Any help on this would be greatly appreciated.Forget the format function. Right click the textbox and go to properties,
under format section click the custom radio button now you can define what
you want: "dd/MM/yyyy hh:mm tt" or whatever.
--
Message posted via http://www.sqlmonster.com

Datetime constraint: is there a better way to do this?

I have a table that contains a datetime field. It's meant to keep track of
monthly equipment inspections.
There can be only one inspection per month, and I need to keep track of the
day it happens. Currently I have one datetime field that keeps track of the
date the inspection happened.
The trick is the constraint that there's only one inspection a month. I know
I could put a "Year" and "Month" column in the table and put a unique index
on those two fields. However, I was wondering if there was a better way to d
o
this - I'd rather not have to maintain two extra columns just for the index.you could make them computed columns and still use a unique constraint
then you wouldn't have to write code to update them when the date is
added [or changed if entered wrong at first, etc.]
e.g. [minimal ddl]
create table inspections (
insp_date datetime not null,
insp_year as year(insp_date),
insp_month as month(insp_date),
unique (insp_year, insp_month)
)
BLetts wrote:
> I have a table that contains a datetime field. It's meant to keep track of
> monthly equipment inspections.
> There can be only one inspection per month, and I need to keep track of th
e
> day it happens. Currently I have one datetime field that keeps track of th
e
> date the inspection happened.
> The trick is the constraint that there's only one inspection a month. I kn
ow
> I could put a "Year" and "Month" column in the table and put a unique inde
x
> on those two fields. However, I was wondering if there was a better way to
do
> this - I'd rather not have to maintain two extra columns just for the index.[/colo
r]|||You could enforce this constaint through the use of an INSTEAD OF trigger
CREATE TRIGGER checkInspectionDate ON EquipmentInspections
FOR INSERT, UPDATE
AS
DECLARE @.timediff int
SELECT @.timediff = MIN(DATEDIFF(mm, e.InspectionDate, i.InspectionDate))
FROM EquipmentInspection e, inserted i
IF (@.timediff = 0)
BEGIN
RAISERROR ('Only a single inspection can occur in each month.', 16, 1)
ROLLBACK TRANSACTION
END
Keep in mind that this will allow an inspection to be inserted on February
1, 2006 , even if there is another inspection scheduled on January 30, 2006.
If you want it to be more like a real month (that is, 30 days), try
CREATE TRIGGER checkInspectionDate ON EquipmentInspections
FOR INSERT, UPDATE
AS
DECLARE @.timediff int
SELECT @.timediff = MIN(DATEDIFF(dd, e.InspectionDate, i.InspectionDate))
FROM EquipmentInspection e, inserted i
IF (@.timediff < 30)
BEGIN
RAISERROR ('Only a single inspection can occur in each month.', 16, 1)
ROLLBACK TRANSACTION
END
"BLetts" wrote:
> I have a table that contains a datetime field. It's meant to keep track of
> monthly equipment inspections.
> There can be only one inspection per month, and I need to keep track of th
e
> day it happens. Currently I have one datetime field that keeps track of th
e
> date the inspection happened.
> The trick is the constraint that there's only one inspection a month. I kn
ow
> I could put a "Year" and "Month" column in the table and put a unique inde
x
> on those two fields. However, I was wondering if there was a better way to
do
> this - I'd rather not have to maintain two extra columns just for the index.[/colo
r]|||Sorry, my trigger code requires a little modification; need to throw in the
ABS function to get the absolute value of the time-difference.
CREATE TRIGGER checkInspectionDate ON EquipmentInspections
FOR INSERT, UPDATE
AS
DECLARE @.timediff int
SELECT @.timediff = MIN(ABS(DATEDIFF(mm, e.InspectionDate, i.InspectionDate))
)
FROM EquipmentInspection e, inserted i
IF (@.timediff = 0)
BEGIN
RAISERROR ('Only a single inspection can occur in each month.', 16, 1)
ROLLBACK TRANSACTION
END
I tested the following trigger on the employee table in the pubs database...
CREATE TRIGGER checkHireDate ON employee
FOR INSERT
AS
DECLARE @.timediff int
SELECT @.timediff = MIN(ABS(DATEDIFF(mm, e.hire_date, i.hire_date)))
FROM employee e, inserted i
IF (@.timediff = 0)
BEGIN
RAISERROR('Cannot hire two people in the same month',16,1)
ROLLBACK TRANSACTION
END
--This will violate the constraint in the trigger
INSERT INTO employee VALUES
('MCD77999M','Mark','O','Williams',1,10,
'0736','1992-08-30')
If you posted to this forum through TechNet, and you found my answers
helpful, please mark them as answers.
"BLetts" wrote:
> I have a table that contains a datetime field. It's meant to keep track of
> monthly equipment inspections.
> There can be only one inspection per month, and I need to keep track of th
e
> day it happens. Currently I have one datetime field that keeps track of th
e
> date the inspection happened.
> The trick is the constraint that there's only one inspection a month. I kn
ow
> I could put a "Year" and "Month" column in the table and put a unique inde
x
> on those two fields. However, I was wondering if there was a better way to
do
> this - I'd rather not have to maintain two extra columns just for the index.[/colo
r]|||Here's a repro that does what Trey suggests, but using a datetime
to keep track of the month. The ISNULL is so the engine knows
that the month column is not null:
create table inspections (
item int not null, -- references a table of inspectables
dt datetime not null default getdate(),
dt_month as isnull(dateadd(month,datediff(month,0,dt
),0),0),
primary key(item,dt_month)
)
go
insert into inspections(item) values(101)
insert into inspections(item) values(102)
go
insert into inspections(item) values(102)
go
select * from inspections
go
drop table inspections
Steve Kass
Drew University
BLetts wrote:

>I have a table that contains a datetime field. It's meant to keep track of
>monthly equipment inspections.
>There can be only one inspection per month, and I need to keep track of the
>day it happens. Currently I have one datetime field that keeps track of the
>date the inspection happened.
>The trick is the constraint that there's only one inspection a month. I kno
w
>I could put a "Year" and "Month" column in the table and put a unique index
>on those two fields. However, I was wondering if there was a better way to
do
>this - I'd rather not have to maintain two extra columns just for the index
.
>|||actually, i tried that first, but got this error:
Server: Msg 1933, Level 16, State 1, Line 1
Cannot create index because the key column 'dt_month' is
non-deterministic or imprecise.
which is odd to me, since the docs state that dateadd and datediff are
deterministic.
Steve Kass wrote:
> Here's a repro that does what Trey suggests, but using a datetime
> to keep track of the month. The ISNULL is so the engine knows
> that the month column is not null:
> create table inspections (
> item int not null, -- references a table of inspectables
> dt datetime not null default getdate(),
> dt_month as isnull(dateadd(month,datediff(month,0,dt
),0),0),
> primary key(item,dt_month)
> )
> go
> insert into inspections(item) values(101)
> insert into inspections(item) values(102)
> go
> insert into inspections(item) values(102)
> go
> select * from inspections
> go
> drop table inspections
> Steve Kass
> Drew University
> BLetts wrote:
>|||Sorry. I only tested this on 2005, where it works. There have
been some changes in how determinism is determined... On 2000, I
guess this solution just won't work. :( On 2005, the output is this:
Server: Msg 2627, Level 14, State 1, Line 1
Violation of PRIMARY KEY constraint 'PK__inspections__7C6F7215'. Cannot
insert duplicate key in object 'dbo.inspections'.
The statement has been terminated.
item dt
dt_month
-- ---
---
101 2006-01-05 17:16:28.780
2006-01-01 00:00:00.000
102 2006-01-05 17:16:28.797
2006-01-01 00:00:00.000
SK
Trey Walpole wrote:
> actually, i tried that first, but got this error:
> Server: Msg 1933, Level 16, State 1, Line 1
> Cannot create index because the key column 'dt_month' is
> non-deterministic or imprecise.
> which is odd to me, since the docs state that dateadd and datediff are
> deterministic.
>
> Steve Kass wrote:
>|||Steve Kass (skass@.drew.edu) writes:
> Here's a repro that does what Trey suggests, but using a datetime
> to keep track of the month. The ISNULL is so the engine knows
> that the month column is not null:
And here is a variation of that repro that works on SQL 2000:
create table inspections (
item int not null, -- references a table of inspectables
dt datetime not null default getdate(),
dt_month as isnull(convert(char(6), dt, 112), '')
primary key(item,dt_month)
)
go
insert into inspections(item) values(101)
insert into inspections(item) values(102)
go
insert into inspections(item) values(102)
go
select * from inspections
go
drop table inspections
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Datetime and other stuff

Overview. Machine data is fed into DOWNTIMELOG via a Cimplicity. I do not have the ability to change the way that it goes in. It contains the time that a machine has stopped and where the stoppage has occurred. Via a web page I want to give the operator the amount of time the machine stopped. From that they will provide some more detail. As they enter time, I need to subtract what they entered from the total (DOWNTIMELOG). That is the reason I created ENTERED_TIME. [PDNTOTAL_TEMP_VAL0] hold the total time for each stoppage, [TOTTIME] is intended to hold the value that has been accounted for. [DWNCATEGORY_TEMP_VAL0] holds the category and matches up with [DWNCATEGORY]. Also, I need to match Date and Shift. The problem is that DOWNTIMELOG does not capture shift. However, 1st shift occurs between 700 and 1500, 2nd shift occurs between 1500 and 2300, 3rd shift occurs between 2300 and 700. 3rd shift presents a problem as it occurs across 2 dates. Could someone please help me with a query that provides the net between the two tables that is matched by date, shift and category?
CREATE TABLE [dbo].[DOWNTIMELOG] (
[timestamp] [datetime] NOT NULL ,
[DWNTIMESTAMP_VAL0] [varchar] (25) NULL ,
[UPTIMESTAMP_VAL0] [varchar] (25) NULL ,
[PDNHOUR_VAL0] [int] NULL ,
[PDNMIN_VAL0] [int] NULL ,
[PDNSEC_VAL0] [int] NULL ,
[PDNTOTAL_TEMP_VAL0] [int] NULL ,
[DWNMSG_TEMP_VAL0] [int] NULL ,
[DWNCATEGORY_TEMP_VAL0] [varchar] (50) NULL

CREATE TABLE [dbo].[ENTERED_TIME] (
[REC_ID] [int] IDENTITY (1, 1) NOT NULL ,
[DWNCATEGORY] [varchar] (50) NULL ,
[DWNMSG] [int] NULL ,
[ENTRYDATE] [smalldatetime] NULL ,
[SHIFT] [int] NULL ,
[TOTTIME] [int] NULLWould someone please review this approach to getting a shift from a timestamp AND changing the date to the following when the Hour is greater than 23:00? I keep getting nulls for hours outside 7 and 14.
SELECT [timestamp] AS thaTimeStamp, (CASE WHEN DATEPART(hh, [timestamp])
= 23 THEN CAST(FLOOR(CAST(DATEADD(d, 1, [timestamp]) AS Float(53))) AS DateTime) ELSE CAST(FLOOR(CAST([timestamp] AS Float(53)))
AS DateTime) END) AS thaDate, (CASE WHEN DATEPART(hh, [timestamp]) > 6 AND DATEPART(hh, [timestamp]) < 15 THEN 1 WHEN DATEPART(hh,
[timestamp]) > 14 AND DATEPART(hh, [timestamp]) < 23 THEN 2 WHEN CAST(DATEPART(hh, [timestamp]) AS INT) > 23 AND CAST(DATEPART(hh,
[timestamp]) AS INT) < 7 THEN 3 END) AS thaShift, DATEPART(hh, [timestamp]) AS thaOutPut
FROM DOWNTIMELOG
WHERE ([timestamp] >= CONVERT(DATETIME, '2005-12-10 00:00:00', 102))

Out put
thaTimeStamp thaDate thaShift thaOutPut
12/10/2005 6:29:05 AM 12/10/2005 <NULL> 6
12/10/2005 7:18:03 AM 12/10/2005 1 7
12/10/2005 7:22:07 AM 12/10/2005 1 7
12/10/2005 7:24:01 AM 12/10/2005 1 7
12/10/2005 7:24:39 AM 12/10/2005 1 7
12/12/2005 6:06:46 AM 12/12/2005 <NULL> 6
12/12/2005 6:19:20 AM 12/12/2005 <NULL> 6
12/12/2005 6:25:28 AM 12/12/2005 <NULL> 6
12/12/2005 7:12:41 AM 12/12/2005 1 7|||SELECT [timestamp] AS thaTimeStamp
, (CASE
WHEN DATEPART(hh, [timestamp]) = 23 THEN CAST(FLOOR(CAST(DATEADD(d, 1, [timestamp]) AS Float(53))) AS DateTime)
ELSE CAST(FLOOR(CAST([timestamp] AS Float(53))) AS DateTime) END) AS thaDate
, (CASE
WHEN DATEPART(hh, [timestamp]) > 6 AND DATEPART(hh, [timestamp]) < 15 THEN 1
WHEN DATEPART(hh, [timestamp]) > 14 AND DATEPART(hh, [timestamp]) < 23 THEN 2
WHEN CAST(DATEPART(hh, [timestamp]) AS INT) > 23 OR CAST(DATEPART(hh, [timestamp]) AS INT) < 7 THEN 3 END) AS thaShift
, DATEPART(hh, [timestamp]) AS thaOutPut
FROM DOWNTIMELOG
WHERE ([timestamp] >= CONVERT(DATETIME, '2005-12-10 00:00:00', 102))-PatP|||Thanks Pat,
That returns what I need. Now how/can I take this and using temp table join it to the ENTERED_TIME ( as in previous posts) table and provide the net between the two? If so can you give me an example of how to go about this?|||I figured it out. I managed by taking Pat's help and creating a view then joining it to the table. Now sure if this is the best way, but it seems to work.

Dates in a date range

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/2005Any 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" <PromisedOyster@.hotmail.com> wrote in message
news:1128748317.108113.292290@.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|||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.|||PromisedOyster (PromisedOyster@.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, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||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|||PromisedOyster (PromisedOyster@.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, esquel@.sommarskog.se

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

But I didn't ask about how to populate it.|||>> 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.|||>> 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|||--CELKO-- (jcelko212@.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, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||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|||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.|||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?|||--CELKO-- (jcelko212@.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, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||>> 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?|||--CELKO-- (jcelko212@.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, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||>> 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)?|||--CELKO-- (jcelko212@.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, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Erland Sommarskog wrote:

> --CELKO-- (jcelko212@.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...|||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.|||corey lawson (corey.lawson@.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, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Erland Sommarskog wrote:

> corey lawson (corey.lawson@.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.|||corey lawson (corey.lawson@.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, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Erland Sommarskog wrote:

> corey lawson (corey.lawson@.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?|||corey lawson (corey.lawson@.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, esquel@.sommarskog.se

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

Dates from multiple fields

I have fields that are listed as nvarchar in a table. Field1 contains
the month, Field2 contains the day, Field3 contains the year.
I need to pick stuff off this table with a "date" >= 12/10/2004.
How do you construct the "date" from the 3 fields above?
I've tried cast & converts on it, but have not hit the correct combination.
Any help appreciated.
BCBlasting,
Try:
SELECT <COLUMNS>
FROM <TABLE>
WHERE CAST(CAST(FIELD3+FIELD2+FIELD1 AS CHAR(8)) AS DATETIME) <
ETDATE() -- OR OTHER DATE
HTH
Jerry
"Blasting Cap" <goober@.christian.net> wrote in message
news:uWQkDUl1FHA.1028@.TK2MSFTNGP12.phx.gbl...
>I have fields that are listed as nvarchar in a table. Field1 contains the
>month, Field2 contains the day, Field3 contains the year.
> I need to pick stuff off this table with a "date" >= 12/10/2004.
> How do you construct the "date" from the 3 fields above?
> I've tried cast & converts on it, but have not hit the correct
> combination.
> Any help appreciated.
> BC
>|||concatenate and add '/' between the fields and convert the whole thing to a
datetime
http://sqlservercode.blogspot.com/
"Blasting Cap" wrote:

> I have fields that are listed as nvarchar in a table. Field1 contains
> the month, Field2 contains the day, Field3 contains the year.
> I need to pick stuff off this table with a "date" >= 12/10/2004.
> How do you construct the "date" from the 3 fields above?
> I've tried cast & converts on it, but have not hit the correct combination
.
> Any help appreciated.
> BC
>

Date+Time = Datetime ???

How can you concatenate two date columns .. one contains the date .. other contains the time (ie date is always '1900-01-01' ) ...
I wan t to get a datetime out of these columns .. how do i do it ?Use the following:

select cast(cast(date1 as decimal(20,10))+cast(date2 as decimal(20,10)) as datetime) from table|||Does not Work
Am providing some Sample data .......
Start Date Start Time
2003-06-03 00:00:00.000 1900-01-01 07:00:00.000
2003-06-02 00:00:00.000 1900-01-01 18:25:00.000
2003-06-04 00:00:00.000 1900-01-01 03:35:00.000
2003-06-04 00:00:00.000 1900-01-01 07:00:00.000
2003-06-03 00:00:00.000 1900-01-01 18:45:00.000|||Try This.

select convert(datetime, date1) + convert(datetime, date2)

Hope this helps!

Cheers!|||Thanks everybody ...
finally did it myself

create function concat_date_time(@.d1 datetime,@.d2 datetime)
returns datetime
as
begin
return convert(datetime,substring(convert(varchar(23),@.d1 ,121),1,10)
+substring(convert(varchar(23),@.d2,121),11,len(con vert(varchar(23),@.d2,121))-11),121)
end