Unfortunately, it's a little more complicated, since SQL Server doesn't have a valid dateformat like the one you need to use as an input...

SELECT CAST(LEFT(@t,8) +' ' +SUBSTRING(@t,9,2)+':' +SUBSTRING(@t,11,2)+':' +SUBSTRING(@t,13,2)...

Please read and follow the first link in my signature.

Providing ready to use table DDL, sample data, expected result and what you've tried so far is the best way to...

We're using XML files only for getting data from a DB2 into SQL Server through a (required) 3rd party middleware.

The reason we're using XML is to be flexible in terms...

Would you please clarify your requirement?

Where did you get the 30min steps from?

Please provide ready to use sample data as described in the the first link of my signature.

Also, please...

ColdCoffee (6/15/2010)

---

...

Lutz, got a question here ; from the OP's queries, if you could see, he/she is using 3 different tables to get the values ; so I guess,...

It's usally a better idea to start a new thread if you have a different question.

The chances are low to find somebody providing a date difference solution if the title...

It would be much more helpful if you could post your data in a ready to use format as described in the link I pointed you at.

There are some people...

Unfortunately, the original question got out of focus in most of the current posts.

So, may I kindly ask to focus on the subject?

Whoever is willing to continue the "challenge discussion"...

Seems like you've got trapped by a "semi-optimal" table design...

Storing different dates and corresponding values in several columns violates normalization. As a "side effect" it makes queris like you're trying...

David Webb-200187 (6/15/2010)

---

The '+' is used as a mathematical operator and a string concatenation operator. If the datatypes of the arguments on both sides are numerical (int, smallint, numeric,...

My fault. Sorry....

I didn't realize the alias within your original query.

Change FROM (...) tAdjustmentEx AS I

to FROM (...) I.

The most probable reason: each and every row in your query contains values where cma_completed_date2 > cma_completed_date1.

A CASE statement will set the return value according to the first (sequential) true...

Your code looks correct, assuming rxo.RX_NUMBER and rxf.REFILL_NUMBER are both character values. Otherwise SQL Server will assume that you're trying to do some math. So, the result should not be...

Please post table def, sample data and expected result based on the sample data as described in the first link in my signature. There are some people around (including me)...

I just stumbled across the concept a few days ago when reading a thread related to the same issue. Unfortunately, neither can I find the thread nor the original author....