Scott, your code is giving me an error, but I corrected it by using only string functions.

STUFF(LEFT(REPLACE(SomeVal, '.', '') + '000000', 5), 4, 0, '.')

Dixie's solution seems simple, but won't work for dates before 1950.

I've done this that would work for any date between today and 100 years in the past (almost).

And a simple...

I had to read twice to realize that there was a COALESCE(NULL, NULL).

It's good to know how this is wrong, but it shouldn't be in real code. Why would someone...

This might help you, considering your second post. But you won't be able to use a decimal(3,2), I'm assuming you meant decimal(5,2) that will support values like 499.99

;with data (SomeVal)...

A simple reply is enough, there are no extra points for correct answers.

When you spend more time in this forum, you'll find out that answers that might seem correct, could...

This might help you, however, you need to understand what is doing before implementing it.

It was great that you posted DDL and sample data.;-)

;WITH rCTE AS(

SELECT 1 AS level,

AccountName,

GroupName,...

Ron,

Even if the Excel file is better than an image, it's not the best option to post sample data.

There are people that won't download any files (due to company policies...

That's basically the same that I posted, with a simple mistake based on the difference of the behaviour of LEN and DATALENGTH.

DATALENGTH won't work fine with trailing spaces or unicode...

A simple improvement on Sean solution.

;with SampleData (val) as

(

select '51079-216-20' union all

select '3456-435623-45-34'

)

SELECT REPLACE( LEFT( val, LEN( val) - 2), '-', '')

FROM SampleData

That was implemented on SQL Server 2008.

In 2005 or previous versions, you need to use several inserts or Selects with UNION ALL. Example:

insert COSTING (ID,GROUP1,ROUP2,GROUP3)

SELECT 'ACS1-1-0010','ACS1-2-0010','ACS1-3-0010','' UNION ALL

SELECT 'ACS1-1-0020','ACS1-2-0010','ACS1-3-0010',''UNION ALL

SELECT...

I can help you to write the update statement. But I would like to know why are you doing this? This will help you with 3 days but what happens...

That can help you,...

🙁 Unfortunately, yes. However, he pays me to use the pictures and we're all volunteers...

Assuming you have DATEFIRST set to 1, change GETDATE() to your date value.

DATEADD( dd, 5 - DATEPART( dw, GETDATE()), GETDATE())

Have you tried the Cross tabs method?

It's in the link provided by Charles. I find it much easier to understand and apply.