I just wanted to play around a little bit and this might be a good option to clean the data using an inline table-valued function.

The function:

CREATE FUNCTION dbo.CleanNames( @Name varchar(8000))

RETURNS...

Would you mind trying with a different version of the function?

ALTER FUNCTION [dbo].[ProperCase](@String [varchar](4000))

RETURNS [varchar](4000) WITH EXECUTE AS CALLER

AS

BEGIN

----------------------------------------------------------------------------------------------------

DECLARE @Position INT

;

--===== Update the first character no matter what...

Could you post the function? Have you installed any updates/SP?

The problem is with your pivot alias. You need to use square brackets if you want to use a reserved word or, even better, change the alias into something less...

Would Contained databases be what you're looking for?

http://msdn.microsoft.com/en-us/library/ff929071.aspx

MMartin1 (10/8/2014)

---

ChrisM makes a good point as a numeric column will not give the expected results...

Either the column is a char/varchar or will have to be cast at select time...

Assuming ExpiryDate is a date/time data type column, I would use the following.

ExpiryDate >= DATEADD(dd, DATEDIFF( dd, 0, getdate()), 0)

It's basically the same as

ExpiryDate >= cast(getdate() as date)

But the...

You're right, I just wanted to point out that there's no guarantee of the order of the results.

a4apple (10/8/2014)

---

I would use Datediff instead of the cast because I don't want to convert the date again and again. Instead I will just check if the difference of the...

DATEDIFF will avoid any usage of indexes on ExpiryDate column.

DATEPART(dd, date) will return wrong results because you're missing month and year.

That cast might allow using indexes if the column is...

No need for a while loop, you just need to use REPLICATE() function.

SELECT REPLICATE('_', [LEVEL]) + NAME

FROM @Tree

ScottPletcher (10/8/2014)

---

Hmm, seems to be that code would list the same customer, the "first" one, over and over rather than listing every individual customer in the state. Are you...

That's weird, I'm sure that integers would be implicitly converted into strings as shown on the examples from BOL.

rohit.kumar.barik (10/8/2014)

---

Hi John,

I faced such issue in my work.

I used two row_number() function

1st row_number() with partition by docno and date1

2nd row_number() with partition by docno.

But i could not order them...

Where do you want to include CAST or CONVERT?