This looks suspiciously like homework to me! However...here's half of what you need...

DROP TABLE #Widgets

CREATE TABLE #Widgets (TranDate DATETIME, Product VARCHAR(10), quantity INT, value MONEY)

INSERT INTO #Widgets (TranDate, Product, quantity,...

DECLARE @TheDate DATETIME, @HalfDayAndaBit DECIMAL (14,13)

SET @HalfDayAndaBit = 0.5000000385803

SET @TheDate = '2008-02-05 11:59:59.993'

SELECT @TheDate

SELECT CAST(@TheDate-@HalfDayAndaBit AS INT)

SELECT CAST(CAST(@TheDate-@HalfDayAndaBit AS INT) AS DATETIME)

SET @TheDate = '2008-02-05 11:59:59.997'

SELECT @TheDate

SELECT...

DECLARE @TheDate DATETIME

SET @TheDate = '2008-02-05 23:59:59.997'

SELECT @TheDate

SELECT CAST(CAST(@TheDate-0.5000000385803 AS INT) AS DATETIME)

SET @TheDate = '2008-02-05 00:00:00.000'

SELECT CAST(CAST(@TheDate-0.5000000385803 AS INT) AS DATETIME)

😉

We're still on 2K, could be decades...

Running an update against a table with a little over 500,000 rows I get the following (average of three runs, in seconds)

1) CAST / FLOOR / CAST [2.32]

2) DATEADD /...

Not yet, Derek. Derek? Where'd he go? 😉

Have a play with the following, it should help you identify where the sp is failing:

DROP TABLE #Offboardv7

GO

CREATE TABLE #Offboardv7(

[ID] [int] NOT NULL,

[SID] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[LAST_NAME] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS...

Skinning a cat innit:

SELECT [DateOnly] = CAST(FLOOR(CAST(GETDATE() AS FLOAT)) AS DATETIME) -- CAST / FLOOR / CAST

SELECT [DateOnly] = dateadd(dd,datediff(dd, 0, getdate()), 0) -- DATEADD / DATEDIFF

SELECT [DateOnly] = CAST(CAST(GETDATE()...

It's perhaps worth a look. The error message states something about nested cases, but case nesting isn't necessary for the error - just more than 10 options in one case...

This is probably a long shot Paul, but what is the maximum number of options you have in a single CASE construct? There's a limit, when running a query against...

You're very welcome Brian, and many thanks for the feedback.

Ian Yates (1/31/2008)

---

It will definitely be some sort of index rebuild. Another thing it could be is if you run a command similar to (but much larger in scope)

update...

Matt Miller (1/31/2008)

---

persnickety is a kind word to describe Lawson....:) But still - you may find it doesn't need that much maintenance.

Actually - I thought QWIRKY was the layout...

Good point Matt, I don't know the answer but your suggestion is pretty easy to check out. The system is Lawson which I'm finding out is kinda "pernickity" and quirky...

Recovery model is "Full"

Hi Todd & Gail

I'm looking into this now. Index rebuilds are 3am. It's an odd system, most columns are covered, and one of our accounts tables has 30m rows. Will...