Using datapart, you'll lose the leading zeros. Convert it to the format closest to what you want and then clean it up using REPLACE.

SELECT REPLACE(REPLACE(REPLACE(CONVERT(varchar(25), GETDATE(), 120), '-', ''),...

Please do not create duplicate posts for the same topic. It fragments the replies. Please respond in this thread. http://www.sqlservercentral.com/Forums/Topic1281038-392-1.aspx

Drew

T-SQL is a completely different language from English with...

If the ultimate goal is concatenation, then there's no reason to shift columns left. Just do the concatenation as normal.

with mySampleData (id,addr1,addr2,addr3)

AS

(

SELECT'1','123',NULL,'Main St.' UNION ALL

SELECT'2',NULL,NULL,NULL UNION ALL

SELECT'3',NULL,'345','Elm St.'...

Part of the problem is that the OP specified a particular route to the destination when only the destination is important. There are really only three conditions:

Between Friday at...

There are just too many variables to recommend one approach over another. You'll need to test several different approaches to see which one works best with your data.

A table...

Henry Treftz (4/6/2012)

---

Jean, I guess the first question is why do you want to force case and accent sensitivity? It will ignore the index on the field and hurt...

A CTE is part of the following INSERT, SELECT, or UPDATE statement and does not exist outside of that scope. If you need to use the same set of...

Sorry, misread the SQL.

It doesn't. As with almost everything, there are multiple ways...

I think you mean MDX. You cannot directly translate SQL to MDX. Even though there are some superficial similarities, they are completely different languages, because they query completely...

SSIS requires that each of the records have the same structure. Since your header records have a different structure than your detail records, you can't do this directly in...

Here is another possibility. Since you haven't provided test data, it's difficult to compare the various methods. This method only scans the table once, but has more logical...

I'm not...

Use FOR XML PATH('') to concatenate your string together. There are several articles on how to do this. In my quick scan, this covered the basics well. Creating...