Replace Sql in the middle of the text

Question

SQL Server Database , Column is a Varchar(9) . And I need to fix this data because I need to filter only by people over 50 years old and that way I’m not getting , more in case someone knows a filter that fits in this way that already helps me too. I have the following situation, I have a table with a column DataNascimento, where this copulated with several dates, however this in a wrong pattern, ex: 12-oct-69, and I’d like to change to 12-10-69.

I know how to use the replace, but not in the middle of the text, I know if I use the right I can pick up a piece of text plus and the ending will be modified.

So I’d like to know if you have any commands to change only the middle of it and preserve the first 3 characters and the other characters after the 6:

Answer 1

The solution should also replace the name of the month with the number so that it is a valid date.

Below is one of the possible solutions (created an example table, Datas):

;WITH CTE(NumMes, Mes) AS
(
    SELECT  1
        ,   FORMAT(CAST('2000-01-01' AS DATE), 'MMM')
    UNION ALL
    SELECT  NumMes + 1
        ,   FORMAT(CAST('2000-' + CAST(NumMes + 1 AS NVARCHAR(2)) + '-01' AS DATE), 'MMM')
    FROM    CTE
    WHERE   NumMes < 12
)
SELECT  *
INTO    #Meses
FROM    CTE

CREATE TABLE Datas([Data] NVARCHAR(9))

INSERT INTO Datas VALUES('01-DEC-43'), ('15-JUL-82'), ('08-MAR-07')

SELECT      REPLACE([Data], SUBSTRING([Data], 4, 3), RIGHT('00' + CAST(M.Nummes AS NVARCHAR(2)), 2))
FROM        Datas   D
INNER JOIN  #Meses  M ON M.Mes = SUBSTRING([Data], 4, 3)

Answer 2

Considering the styles defined in the CONVERT() function, the data is in the 6 style. If the goal is to convert from dd-month-yy to dd-mm-yy, then it is a very simple process:

-- código #3
SELECT DNB, 
       convert (char(8), convert (date, DNB, 6), 5) as DNB2
  from tabela; 

In the above DNB code is the column Datanascimentobeneficiario.

---

But if it is necessary to add the "century", and considering the styles defined in the CONVERT() function, the data is in style 6, in principle it would be enough to make the following conversion:

-- código #1
SELECT DNB, 
       convert (date, DNB, 6) as DNB_2
  from tabela;

In the above DNB code is the column Datanascimentobeneficiario.

The CONVERT() function treats year values, adding either 19 or 20 as "century". However, the rule she uses for defining "century" is unreliable for the case. For example, 08-MAR-20 it converts to 8 March 2020: invalid date for case, which is date of birth of a person.

Considering that it is date of birth, one option is to define the century considering the current year: if the year on the date is greater than 19 (current year), then the date is from the last century; otherwise, it is from the current century.

-- código #2
declare @AnoCorrente tinyint;
set @AnoCorrente= year (current_timestamp) % 100;

with tabela_2 as (
  SELECT *, 
       (left (DNB, 7) 
        + case when cast (right (DNB, 2) as tinyint) > @AnoCorrente
                                 then '19' else '20' end 
        + right (DNB, 2)) as DNB2 
  from tabela
)  
SELECT DNB, 
       convert (date, DNB, 6) as DNB_6,
       convert (date, DNB2, 106) as DNB_106
  from tabela_2;

In the above code the date is converted using both methods and in the analysis of the results it is realized that the new rule is reliable.

The reliability of the conversion can be enhanced by also using the information in the column containing the degree of kinship.

Answer 3

First you have to convert to a format that SQL SERVER understands (see : https://docs.microsoft.com/pt-br/sql/t-sql/functions/cast-and-convert-transact-sql?view=sql-server-2017) I suggest something like :

SELECT @dataTexto  = SUBSTRING(@DataNascimentoBeneficiario, 4, 3) SUBSTRING(@DataNascimentoBeneficiario, 1, 2) + "," + SUBSTRING(@DataNascimentoBeneficiario, 8, 2)

(Documentation about Substring in https://docs.microsoft.com/pt-br/sql/t-sql/functions/substring-transact-sql?view=sql-server-2017 )

Which would convert 15-JUL-82 to JUL 15, 1982 (Type 7 date)

select @dataDateFormat = CONVERT(DATE, @dataTexto, 7)

Then convert to text again in the format you want, using datepart https://docs.microsoft.com/pt-br/sql/t-sql/functions/datepart-transact-sql?view=sql-server-2017

SELECT @novoFormatoDataEmTexto = DATEPART(dd,@dataDateFormat)+"-"+DATEPART(mm,@dataDateFormat)+"-"+DATEPART(yy,@dataDateFormat)

I used variables because it is easier and maybe even more performative using cursor, but you can replace each place that the variable appears, by its content so that everything stays in a single SELECT, although it will be difficult to understand.