I've 100s of 1000's of rows with four date posts which were imported as a result:

Mon Nov 14 14:52:46 PST 2011
Fri Nov 04 07:50:21 PDT 2011
Thu Dec 01 00:00:00 PST 2011

You will find three several weeks: November, 12 ,, Jan. I would like my new date format saved to new posts within this format:

11-14-2011, 11-04-2011, 12-01-2011 and so forth....

I am able to perform this with an update statement that suits the substring month title and replaces it using the matching number. In order to get it done with something similar to this:

UPDATE tabel 
   set col1 = REPLACE(substring(col2, 5,3) 'Nov', 11)

And delay pills work for that first month I run. However when Then i add 12 , and Jan, it only works best for individuals several weeks and starts over the string for that three letter several weeks in most another rows I simply did.

I'm not sure how you can execute this UPDATE and REPLACE command correctly therefore it matches the 3 several weeks within the database after which creates the new format towards the new column like I would like. I am a bit a new comer to T-SQL and to date I am overcome considering the variety of documentation available. I have attempted putting the 3 several weeks into a number of UPDATE claims which didn't work. It must go row by row, test an ailment after which execute the update whether it matches.

Maybe such as this:

IF @dateColSubString = 'Jan' Then
   UPDATE table
         set newDateCol = REPLACE(substring(col2, 5,3) 'Jan', 01) + '-2012'
   COMMIT
IF @dateColSubString = 'Dec' Then
   UPDATE table
         set newDateCol = REPLACE(substring(col2, 5,3) 'Dec', 12) + '-2011'
   COMMIT

to create values such as this within the new column '01-2011' after which have individuals 't be totally reset to there old values after i run exactly the same update request 'Nov' after which 'Dec' to alter individuals strings.

I really hope this will make sense.

Do this and do anything you want together with your datetime

DECLARE @t TABLE(str varchar(100))
INSERT @t
VALUES('Mon Nov 14 14:52:46 PST 2011'),('Fri Nov 04 07:50:21 PDT 2011'),('Thu Dec 01 00:00:00 PST 2011')

SELECT 
    CAST(DATEPART(MONTH, CAST(SUBSTRING(str, 5, 6) +' '+RIGHT(str, 4) AS DATETIME)) AS VARCHAR) +'-'+ RIGHT(str, 4),
    CAST(SUBSTRING(str, 5, 6) +' '+RIGHT(str, 4) AS DATETIME),
    CONVERT(VARCHAR, CAST(SUBSTRING(str, 5, 6) +' '+RIGHT(str, 4) AS DATETIME), 110)
FROM @t

Inside your situation, a suppose, it is just like this - you just determine which outcome is more appropriate for you personally and adopt the related query row:

SELECT 
    CAST(DATEPART(MONTH, CAST(SUBSTRING(order_send, 5, 6) +' '+RIGHT(order_send, 4) AS DATETIME)) AS VARCHAR) +'-'+ RIGHT(order_send, 4),
    CAST(SUBSTRING(order_send, 5, 6) +' '+RIGHT(order_send, 4) AS DATETIME),
    CONVERT(VARCHAR, CAST(SUBSTRING(order_send, 5, 6) +' '+RIGHT(order_send, 4) AS DATETIME), 110)
FROM YourTableName