Updating table based on global substring match via IF statement or CASE

Question

I have hundreds of thousands of rows with four date columns that were imported as such:

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

There are three months: Nov, Dec, Jan. I want my new date format saved to new columns in this format:

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

I'm able to do this via an update statement that matches the substring month name and replaces it with the matching numeric. So I can do it with something like this:

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

And it works for the first month I run. But when I then add Dec and Jan, then it only works for those months and resets the string for the three letter months in all the other rows I just did.

I don't know how to execute this UPDATE and REPLACE command in the right way so it matches all three months in the database and then writes out the new format to the new column like I want. I'm a bit new to T-SQL and so far I'm overwhelmed with the amount of documentation out there. I've tried putting all three months into a series of UPDATE statements and that did not work. I think it needs to go row by row, test a condition and then execute the update if it matches.

Maybe like 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 make values like this in the new column '01-2011' and then have those not be reset to there old values when I run the same update request for 'Nov' and then 'Dec' to change those strings.

I hope this makes sense.

Answer 1

Try this and do whatever you want 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

In your case, a suppose, it is like this - you only have to choose which result is more suitable for you and adopt the corresponding 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