TSQL: How to swap characters in a string?

Question

I've stored a date as a string in a test DB and the text was stored with the mm and dd swapped. I have a lot of records and I would like to automate this if possible. Is there a way in SQL to swap characters based on the position?

Here is the code:

CREATE TABLE #D (
DateAsString varchar (10)
)

INSERT INTO #D (DateAsString)

VALUES 
('20160601'),
('20160127'),
('20160129')

SELECT 
DataAsString

FROM #D

Thank you.

Look at the SUBSTRING function – AntDC Mar 31 '16 at 13:38 — AntDC, Mar 31 '16 at 13:38
Thanks @AntDC, I'll check it out. – JM1 Mar 31 '16 at 13:38 — JM1, Mar 31 '16 at 13:38

score 3 · Answer 1 · answered Mar 31 '16 at 13:45

3

Try this -

Update #D 
    SET SwapDateAsString = FORMAT(CAST(Dateasstring as DATE), 'yyyyddMM')

Result

DateAsString    SwapDateAsString
20160601        20160106
20160127        20162701
20160129        20162901

answered Mar 31 '16 at 13:45

Krishnraj Rana

6,516
2
29
36

Thanks @KrishnrajRana. – JM1 Mar 31 '16 at 13:54
Nice Solution but you are assuming that he is using SQL 2012 or newer. – Mark Kram Apr 01 '16 at 13:48
1

@MarkKram: OP have added tag of `SQL Server 2014` in his question. So my solution perfectly worked. – Krishnraj Rana Apr 01 '16 at 15:27

score 3 · Accepted Answer · answered Mar 31 '16 at 13:54

Try like this,

DECLARE @MyTable TABLE (DateAsString VARCHAR(10))

INSERT INTO @MyTable (DateAsString)
VALUES ('20160601')
    ,('20162701')
    ,('20162901')

SELECT DateAsString
    ,substring(DateAsString, 1, 4) + substring(DateAsString, 7, 2) + + substring(DateAsString, 5, 2) AS DesiredResultAsString
    ,Convert(DATE, substring(DateAsString, 1, 4) + substring(DateAsString, 7, 2) + + substring(DateAsString, 5, 2)) AS DesiredResultAsDate
FROM @MyTable

score 2 · Answer 3 · edited May 23 '17 at 12:31

2

Since SQL Server does not allow arbitrarily string to date/time parsing (like TryParse or Parse in .NET), you must construct your dates from pieces:

declare @dateStr VARCHAR(8) = '20162701'
select DATEFROMPARTS(substring(@dateStr, 1, 4), substring(@dateStr, 7, 2), substring(@dateStr, 5, 2))

edited May 23 '17 at 12:31

Community

1
1

answered Mar 31 '16 at 13:41

Alexei - check Codidact

22,016
16
145
164

actually you should only concat substrings in different order, OP doesn't ask to convert such stirng as `DATE` – teran Mar 31 '16 at 13:43
Yes, but it is strongly recommended to store the date as DATE or at least DATETIME2, not as VARCHAR. Since he takes the time to make this correction, I expect to try to do it fully. – Alexei - check Codidact Mar 31 '16 at 14:10
Agreed, nonetheless I always recommend using native/built in functions. – Mark Kram Apr 01 '16 at 15:31

Mark Kram · Answer 4 · 2016-04-01T15:40:29.557

I found a function at this LINK called fnFormatDate and since you didn't state which version of SQL Server your were using this solution should work for all versions of SQL from 2005 and newer. BTW I will be adding to my ETL Functions Library. Here is my solution:

IF OBJECT_ID(N'dbo.fnFormatDate', 'FN') IS NOT NULL
    DROP FUNCTION dbo.fnFormatDate
GO

CREATE FUNCTION dbo.fnFormatDate (@Datetime DATETIME, @FormatMask VARCHAR(32))
    RETURNS VARCHAR(32)
AS
    BEGIN
        DECLARE @StringDate VARCHAR(32)
        SET @StringDate = @FormatMask
        IF (CHARINDEX ('YYYY',@StringDate) > 0)
            SET @StringDate = REPLACE(@StringDate, 'YYYY',
                                DATENAME(YY, @Datetime))
        IF (CHARINDEX ('YY',@StringDate) > 0)
            SET @StringDate = REPLACE(@StringDate, 'YY',
                                RIGHT(DATENAME(YY, @Datetime),2))
        IF (CHARINDEX ('Month',@StringDate) > 0)
            SET @StringDate = REPLACE(@StringDate, 'Month',
                                DATENAME(MM, @Datetime))
        IF (CHARINDEX ('MON',@StringDate COLLATE SQL_Latin1_General_CP1_CS_AS)>0)
            SET @StringDate = REPLACE(@StringDate, 'MON',
                                LEFT(UPPER(DATENAME(MM, @Datetime)),3))
        IF (CHARINDEX ('Mon',@StringDate) > 0)
            SET @StringDate = REPLACE(@StringDate, 'Mon',
                                            LEFT(DATENAME(MM, @Datetime),3))
        IF (CHARINDEX ('MM',@StringDate) > 0)
            SET @StringDate = REPLACE(@StringDate, 'MM',
                        RIGHT('0'+CONVERT(VARCHAR,DATEPART(MM, @Datetime)),2))
        IF (CHARINDEX ('M',@StringDate) > 0)
            SET @StringDate = REPLACE(@StringDate, 'M',
                                CONVERT(VARCHAR,DATEPART(MM, @Datetime)))
        IF (CHARINDEX ('DD',@StringDate) > 0)
            SET @StringDate = REPLACE(@StringDate, 'DD',
                                RIGHT('0'+DATENAME(DD, @Datetime),2))
        IF (CHARINDEX ('D',@StringDate) > 0)
            SET @StringDate = REPLACE(@StringDate, 'D',
                                            DATENAME(DD, @Datetime))   
        RETURN @StringDate
    END
GO

IF OBJECT_ID(N'Tempdb..#D', 'U') IS NOT NULL
    DROP TABLE #D

CREATE TABLE #D(DateAsString varchar (10))
GO

INSERT INTO #D (DateAsString)
    VALUES 
            ('20160601')
            ,('20160127')
            ,('20160129')
            ,('20160229')
            ,('20161231')
    GO

SELECT 
    DateAsString
    ,NewDateAsString = dbo.fnFormatDate(dbo.fnFormatDate(DateAsString, 'YYYYMMDD'), 'YYYYDDMM') 
 FROM #D

Thanks @MarkKram! (My tag had 2014, but I'll but that in my title or in my question moving forward.) Thanks for the feedback. — JM1, Apr 01 '16 at 17:45

score 0 · Answer 5 · answered Mar 31 '16 at 13:58

0

For example

DECLARE @d VARCHAR(8) = '20160601'
SELECT  @d as BasdDate, SUBSTRING(@d, 1, 4) + SUBSTRING(@d, 7, 2) + SUBSTRING(@d, 5, 2) as GoodDate

answered Mar 31 '16 at 13:58

AntDC

1,807
14
23

TSQL: How to swap characters in a string?

5 Answers5