Good article, but I've been surprised with the use of COALESCE() for getting the 'Grand Total' in Listing 3. I think it's better to use the GROUPING() function to know...

Nobody seems to care about the maybe remote possibility of having the searched string splitted between two rows of syscomments, if the code is big enough to span over 4000...

In a few words, the difference between sp & functions is that functions yields an expression and so it can be used in a expression context (in SELECT clause, b.e.),...

Dear friends:

I'm a little disappointed after reading the comments of this forum. Few people have liked or commented on the anomalous behavior described, and most, rude apart, are concentrated...

By the way, my cleaver colleague reminds me we had got this behavior sometimes, when sending SQL code in Lotus Notes emails.

Fortunately, in those cases the behavior led to...

For those who propose alternative writtings to the sample code, you must consider that code is only a construction to verify the actual behavior of a query.

The original code was...

I'm sorry for your lost points, guys. If it's a consolation for you, I spent two mornings completely puzzled with this issue, to the point I asked for help in...

I feel it's easier truncating a CONVERT(..., ..., 121), then CASTing again and adding the unit if the two dates differ - you can easily add a flag for ceiling/truncating:

CREATE...

Thank you guys. I was obfuscated -- too much time at work --- Now it's clear crystal, but this morning it wasn't. Thanks again,

More compact query:

[font="Courier New"]DECLARE @TABLE TABLE(

ID INT IDENTITY,

InTime DATETIME,

OutTime DATETIME)

INSERT INTO @TABLE

SELECT '2010-11-09 02:15:00', '2010-11-09 02:45:00'

UNION ALL SELECT '2010-11-09 01:01:00', '2010-11-09 01:45:00'

UNION ALL SELECT '2010-11-08 00:01:00', '2010-11-09 12:45:00'

SELECT id,

Convert(...

Errata: what is lacking is 0A (line feed, NL , CHAR(10)), not carriage return 0D.

SQLZ (8/13/2010)

---

This was an interesting article with an even more interesting discussion.

The thing I'd like to pick up on, that drew my attention, was your reasons for moving away from...

dml_std (8/12/2010)

---

Nice solution, but what if the values are not known? you still have to do it with a while loop.

In fact in all the code I have written I...

In...

I was about to tell you. It operates only on fixed length...