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.
What is the SGDB you are using? What kind of column? What have you tried to do?
– David
Copulated? Are you sure?
– Sorack
@Sorack able even, look at the year:
12-oct-69
- out that ta full of spouse, companion, and have child also, which sustains the thesis.– Bacco
@Sorack guy is a bank with information about registered users of the system , I get a file from a public body and play on the bench , and now I need to filter for over 50 years and this way this can not
– Flavio Ss
@Flavioss we want to know which database is (
MySQL
,SQL Server
,Oracle
, etc? ). It’s also interesting that you add what kind of data in your column:VARCHAR
,DATE
,DATETIME
? Which one is it? Once this is done it would be interesting for you to give some information if you just want to search for the data in another way, because if you are already inDATE
the data type is already correct.– Sorack
@Sorack is Sqlserver , the dating field is a varchar of (9)
– Flavio Ss
@Flavioss important [Dit] the post with as much detail as you can, and where exactly is the difficulty in solving, because perhaps being more elaborate is possible the community reopen. The comment field is for more "volatile" things so to speak.
– Bacco
@Bacco gave an edited question , I think it was explained more now , thanks
– Flavio Ss
@Flavioss and to add something more to what Bacco quoted, what you want is not even turn the die, you want to filter. When you have any questions, try to put your original question and not what you think it would solve. In the case of your bank I would say that to transform the date we would have a big problem that is to identify in which century the date is persevering. if the date is 16-JUN-19 will be 16/06/1919 or 16/06/2019?
– Sorack
@Sorack then that’s the problem I’m not sure either , but I’m assuming that there will be nobody with 100 years , so for me it would be 2019
– Flavio Ss
@Flavioss puts this in his question
– Sorack
@Flavioss, another thing, you pasted on the internet sensitive information, try to obfuscate or shuffle the data, put example closer to reality, but data as full name CPF, are delicate.
– David
@David understood, I fixed the image
– Flavio Ss
I suggest you change the question to, how to change a date varchar in format '15-JUL-82' to another varchar in format '15-07-82' in T-SQL in SQL Server, so I understood the doubt is just this, correct?
– Edson Vicente Carli Junior