CODEWITHSUNDEEP
sql-servert-sqlsql-server-2014
JM1
JM1

Reputation: 1715

TSQL: How to swap characters in a string?

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?

enter image description here

Here is the code:

CREATE TABLE #D (
DateAsString varchar (10)
)

INSERT INTO #D (DateAsString)

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

SELECT 
DataAsString

FROM #D

Thank you.

Upvotes: 0

Views: 4473

Answers (5)

Mark Kram
Mark Kram

Reputation: 5842

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

enter image description here

Upvotes: 1

AntDC
AntDC

Reputation: 1917

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

Upvotes: 0

StackUser
StackUser

Reputation: 5398

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

Upvotes: 3

Krishnraj Rana
Krishnraj Rana

Reputation: 6656

Try this -

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

Result

DateAsString    SwapDateAsString
20160601        20160106
20160127        20162701
20160129        20162901

Upvotes: 3

Alexei - check Codidact
Alexei - check Codidact

Reputation: 23108

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))

Upvotes: 2

Related Questions