CODEWITHSUNDEEP
sqlsql-serversql-server-2012
Prayag Sagar
Prayag Sagar

Reputation: 671

Replace only a part of the string in SQL

I have a table with a nvarchar column having DateTime data in YYYY:MM:DD HH:MM:SS format. I want to convert and update this data into YYYY-MM-DD HH:MM:SS format.

The problem i am facing is, If i use REPLACE, all : gets replaced by -, something like YYYY-MM-DD HH-MM-SS format.

How can i achieve this?

Existing data : 2016:10:27 15:39:33

Desired data format after REPLACE: 2016-10-27 15:39:33

EDIT : I forgot to mention that the column has data in other DateTime formats too, like

Upvotes: 2

Views: 1135

Answers (5)

Tanner
Tanner

Reputation: 22733

Here's a sample that handles your multiple date formats:

CREATE TABLE #Data
(
    date_strings NVARCHAR(20)
)

INSERT INTO #Data
        ( date_strings)
    SELECT N'2016:10:27 15:39:33'
    UNION ALL 
    SELECT N'1912:10:27 15:39:33'
    UNION ALL 
    SELECT N'20:11:2010 15:39:33'
    UNION ALL 
    SELECT N'12:10:1912 15:39:33'
    UNION ALL 
    SELECT N'2016-12-14 15:39:33'

SELECT  date_strings ,
    CASE 
      WHEN CHARINDEX(':', LEFT(date_strings, 10)) > 0
      THEN CASE 
            WHEN CHARINDEX(':', LEFT(date_strings, 10)) = 3
            THEN CONVERT(DATETIME, STUFF(STUFF(date_strings, 3, 1, '-'), 6, 1, '-'), 103)
            ELSE CONVERT(DATETIME, STUFF(STUFF(date_strings, 5, 1, '-'), 8, 1, '-'))
           END
        ELSE CONVERT(DATETIME,date_strings)
    END AS ConvertedValues
FROM    #Data

DROP TABLE #Data

This basically uses case statements to handle the dates in the different formats by looking for the position or presence of the : in the date portion of the value.

Case 1 - contains ::

CHARINDEX(':', LEFT(date_strings, 10)) > 0 -- needs conversion

Case 2:

CHARINDEX(':', LEFT(date_strings, 10)) = 3 -- formatted DD:MM:YYYY HH:MM:SS

Case 3: 

formatted YYYY:MM:DD HH:MM:SS

Case 4:

Simply convert it, it's already in the correct format.

Note, the basic STUFF, syntax was taken from Tim's answer.

Upvotes: 0

Gottfried Lesigang
Gottfried Lesigang

Reputation: 67311

Try it like this:

DECLARE @s VARCHAR(100)='2016:10:27 15:39:33'

SELECT REPLACE(LEFT(@s,10),':','-') + RIGHT(@s,9)

UPDATE You found more different formats...

The following should convert them all

DECLARE @tbl TABLE(StringDate VARCHAR(100), RealDate DATETIME);
INSERT INTO @tbl(StringDate) VALUES('2016:10:27 15:39:33')
                                 ,('2016/03/23 12:33:44') 
                                 ,('2016-04-24 00:11:22') 
                                 ,('24/03/2016 11:22:33'); 

WITH Cut AS
(
    SELECT REPLACE(REPLACE(LEFT(StringDate,10),':','-'),'/','-') AS DateString
          ,RIGHT(StringDate,8) AS TimeString
          ,RealDate
    FROM @tbl
)
,UpdateableCTE AS
(
    SELECT RealDate
          ,DateString
          ,CASE WHEN CHARINDEX('-',DateString,1)=5 
                THEN CONVERT(DATETIME,DateString + 'T' + TimeString,126) --ISO8601
                ELSE CONVERT(DATETIME,DateString,103) + TimeString END AS ConvertedDate
    FROM Cut
)
UPDATE UpdateableCTE SET RealDate=ConvertedDate;

SELECT * FROM @tbl

Upvotes: 2

Vicky_Burnwal
Vicky_Burnwal

Reputation: 981

You can do it with multiple ways. My way with substring. 

DECLARE @column1 varchar(max) = 'YYYY:MM:DD HH:MM:SS'
      SELECT
      REPLACE(SUBSTRING(@column1, 1, CHARINDEX(' ', @column1) - 1), ':' , '-') + ' ' +   SUBSTRING(@column1, CHARINDEX(' ', @column1) + 1, 8000)

Upvotes: 0

Sandip - Frontend Developer
Sandip - Frontend Developer

Reputation: 14669

DECLARE @a varchar(max) = 'YYYY:MM:DD HH:MM:SS'

SELECT 
  stuff(stuff(@a, charindex(':', @a),1, '-'), 
  charindex(':', @a, charindex(':', @a) + 1), 1, '-')

Upvotes: 1

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521239

As @jarlh wisely pointed out, you absolutely should avoid storing date information as varchar, for so many reasons. You won't be able to take advantage of any of the power which SQL Server has for working with dates and timestamps.

But if you really just need to do this for formatting reasons, STUFF might come in handy:

SELECT STUFF(STUFF(col, 5, 1, '-'), 8, 1, '-')
FROM yourTable

Upvotes: 3

Related Questions