Oh my, so much code! My laziness wants to write less.

declare @SSDate datetime = '20140109'

declare @CharSSDate char(10)

SELECT CONVERT( char(10), @SSDate, 103), --4-digit year

STUFF( CONVERT( char(10), @SSDate, 103), 7, 2,...

Thomas Abraham (1/9/2014)

---

Let's try to simplify:

By default, "Options 1, 2 and 4 are equivalent and identical to the original code" is the remaining answer that has not already been proven...

eli.misael (1/8/2014)

---

other solution is using the CTE and PIVOT like the follow script, in the sample I define the tables, but you can use your defined tables and only use...

That was very common at my first job to avoid writing the definition of...

I'm sure that this is a running total problem that I would love to try with SQL 2012, but I don't have it at work.

There's another option that works great...

Here's an example:

select *

INTO #TableB

from sys.all_objects

WHERE 1=2

select top 1000 *

INTO #TableA

from sys.all_objects

WHILE @@ROWCOUNT > 0

BEGIN

INSERT INTO #TableB

SELECT TOP 100 *

FROM #TableA a

WHERE NOT EXISTS(SELECT * FROM #TableB b...

Why would you do that? How can you identify which rows you have inserted and which rows you haven't?

Change

FOR XML PATH( '' )), 1, 1, '' )

To

FOR XML PATH(''), TYPE).value('.','varchar(max)'),1,1,'')

You could set up a job in the SQL Server Agent.

You actually don't need the following line

DECLARE @cmd VARCHAR(MAX)

As said, you would be better deleting the information from the table. You could use TRUNCATE TABLE for performance, but it has...

You can't assign the result of a formula directly to a variable. You would need to use dynamic code.

Here's an example:

DECLARE @MYFOR AS NVARCHAR(100), @Result int

SET @MYFOR ='V6*2*3'

SELECT @MYFOR =...

You have a problem with your nesting. You're opening your cast before the case and closing it in the middle of the case. It has to be either all out...

What about importing last column as a string and using a derived column to validate and convert value into int?

Jeff Moden (1/7/2014)

---

eccentricDBA (1/7/2014)

---

I agree that a DBA should learn how to develop. At the very least they should learn how to use a scripting language (dos batch, bash,...

You could truncate your values to 23 positions or you could convert them to datetime2

CREATE TABLE #Test(

chardatevarchar( 50))

INSERT #Test VALUES(

'2013-01-09 11:32:00'),(

'2013-02-14 20:22:37.033000000'),(

'2013-05-05 23:11:15.013000000'),(

'2013-06-14 15:20:36.030000000'),(

'2013-01-09 11:29:00')

SELECT *, CAST(LEFT( chardate, 23)...