none
Extraer fecha de nacimiento de un DNI RRS feed

  • Pregunta

  • Buenas tardes compañeros, quisiera ver si me pueden ayudar con el siguiente caso en una tabla de empleados tengo un campo denominado "cedula" aca en Nicaragua este numero de cedula esta compuesto de la siguiente forma : 001-281068-0001A o 401-140493-0004E ; este numero esta compuesto por nuestras fechas de nacimientos y siempre son los 6 dijitos despues de los 3 primeros a como se muestra en la siguiente imagen:

    Entonces la idea es extraer esos 6 dijitos del DNI de cada cliente grabado en la BD y convertirlos a fecha para poder saber el dia de su cumpleaños, con esto lo que quiero es mostrar una lista que muestre los empleados de la semana actual.

    muchas gracias de antemanos.


    martes, 2 de febrero de 2021 19:45

Respuestas

  • Hola Reynaldo Sanchez:

    Vamos a dividir el problema en varias partes para poder obtener una solución óptima y adecuada a tú planteamiento.

    La primera parte es dividir el varchar en partes, para poder extraer la fecha de nacimiento.

    Existen varias alternativas a esto, yo te voy a poner una con dos funciones, que funciona muy bien y que además puedes aprovechar para muchos desarrollos.

    CREATE FUNCTION [dbo].[GetNums]
    (
    @low AS BIGINT, 
    @high AS BIGINT
    ) RETURNS TABLE
    AS
    RETURN
      WITH
      L0   AS (SELECT c FROM (SELECT 1 UNION ALL SELECT 1) AS D(c)),
      L1   AS (SELECT 1 AS c FROM L0 AS A CROSS JOIN L0 AS B),
      L2   AS (SELECT 1 AS c FROM L1 AS A CROSS JOIN L1 AS B),
      L3   AS (SELECT 1 AS c FROM L2 AS A CROSS JOIN L2 AS B),
      L4   AS (SELECT 1 AS c FROM L3 AS A CROSS JOIN L3 AS B),
      L5   AS (SELECT 1 AS c FROM L4 AS A CROSS JOIN L4 AS B),
      Nums AS (SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) 
    AS rownum
              FROM L5)
      SELECT TOP(@high - @low + 1) @low + rownum - 1 AS n
      FROM Nums
      ORDER BY rownum;

    Esta primera función nos devuelve una columna con números entre el rango facilitado como parámetros de la función.

    https://tsql.solidq.com/SourceCodes/GetNums.txt

    La segunda función utiliza esta como parte del desarrollo

    -- Erland's split_me
    CREATE FUNCTION [dbo].[ufn_inline_split_me]
    (
    @param nvarchar(MAX), 
    @delimiter nchar(1) = N','
    )
    RETURNS table AS
    RETURN (
    SELECT
        ROW_NUMBER() OVER(ORDER BY n) AS pos,
        LTRIM(RTRIM(CONVERT(nvarchar(4000),
        SUBSTRING(@param, n,
        CHARINDEX(@delimiter, 
            @param + CONVERT(nvarchar(MAX), 
        @delimiter), n) - n)))) 
        AS Value
    FROM
        dbo.GetNums(1, LEN(@param))
    WHERE
        SUBSTRING(CONVERT(nvarchar(MAX), @delimiter) 
       + @param, n, 1) 
        = @delimiter
    );

    Con estas dos funciones, ya podemos partir un varchar.

    Select * 
    	From dbo.ufn_inline_split_me
    		('401-140493-0004E','-') FN;


    Entonces obtniendo de las filas que devuelve la función, la que corresponde con pos=2 ya disponemos de las fechas de nacimiento.

    Segunda parte importante, es la semana.

    Aquí depende un poco de lo que quieras obtener, y de cuando. Porque la semana en us-english empieza el domingo y en español, empieza en lunes. Pero además el script lo puedes aplicar ....¿qué día?.

    Escenario:

    Create Table dbo.UsersNicaragua 
    (
    	id int identity(1,1) primary key
      , nombre varchar(100)
      , dni varchar(16)
    );
    Go
    Insert Into dbo.UsersNicaragua
    (nombre, dni)
    values
    ('Bea1','401-140493-0004E'),
    ('Bea2','xxx-010278-yyyyy'),
    ('Bea3','xxx-020273-yyyyy'),
    ('Bea4','xxx-030271-yyyyy'),
    ('Bea5','xxx-040292-yyyyy'),
    ('Bea6','xxx-050294-yyyyy'),
    ('Bea3','xxx-070253-yyyyy'),
    ('Bea4','xxx-080261-yyyyy'),
    ('Bea5','xxx-310182-yyyyy'),
    ('Bea6','xxx-300165-yyyyy');
    Go

    Como no nos importa la parte izquierda y la derecha del varchar, en todos los registros va lo mismo.

    Set dateFirst 1;--Indica cual es el primer día de la semana
    
    ;with c as (
    	Select * from dbo.UsersNicaragua u
    		cross apply dbo.ufn_inline_split_me(u.dni,'-') fn
    	where fn.pos = 2 
    	)
    Select * from c

    Con esto tenemos los datos fuentes y el valor spliteado.

    Ahora vamos a continuar con los datos relativos a la semana

    Select 
    	Cast(Cast((DateAdd(DAY,7-( DATEPART(dw,getdate())),getdate())) as date) as datetime)  as FechaLimiteSemana,
    	Cast(Cast(GETDATE() as date) as datetime) as FechaInicioSemana -- hoy
    


    Esto que vamos a meter en un conjunto de una tabla de expresión común, es la parte en la que tendrás que pensar como ejecutar. Yo he establecido, como inicio de la semana el día de hoy, y como fin de la semana el domingo correspondiente a esta semana.

    Otra parte que hay que obtener es como bien indicabas la fecha como un date.

    Set dateFirst 1;--Indica cual es el primer día de la semana
    
    ;with c as (
    	Select * from dbo.UsersNicaragua u
    		cross apply dbo.ufn_inline_split_me(u.dni,'-') fn
    	where fn.pos = 2 
    	)
    	, DatosActuales as (
    Select 
    	Cast(Cast((DateAdd(DAY,7-( DATEPART(dw,getdate())),getdate())) as date) as datetime)  as FechaLimiteSemana,
    	Cast(Cast(GETDATE() as date) as datetime) as FechaInicioSemana -- hoy
    	)
    	, FechasNacimiento as (
    	Select 
    		c.id,
    		c.value,
    		LEFT (c.value,2) as dia,
    		SUBSTRING(c.value,3,2) as mes,
    		DATEFROMPARTS(YEAR(getdate()),Cast((SUBSTRING(c.value,3,2)) as int),Cast((LEFT (c.value,2)) as int)) as fecha
    	From c 
    	)
    	Select * from FechasNacimiento

    Como puedes observar tenemos en la columna fecha, la fecha de nacimiento, pero con el año actual, para poder hacer una restricción donde la fecha sea mayor o igual que la semana inicial, y menor o igual que la semana final. (Esta parte queda a tú criterio)

    Ya para finalizar lo único que nos queda es mezclar los conjuntos.

    Set dateFirst 1;--Indica cual es el primer día de la semana
    Declare @fechas table (id int, fechas date, fechaInicio date, fechafin date);
    
    ;with c as (
    	Select * from dbo.UsersNicaragua u
    		cross apply dbo.ufn_inline_split_me(u.dni,'-') fn
    	where fn.pos = 2 
    	)
    	, DatosActuales as (
    	Select 
    		Cast((DateAdd(DAY,7-( DATEPART(dw,getdate())),getdate())) as date)  as FechaLimiteSemana,
    		Cast(GETDATE() as date) as FechaInicioSemana -- hoy
    	)
    	, FechasNacimiento as (
    	Select 
    		c.id,
    		c.value,
    		LEFT (c.value,2) as dia,
    		SUBSTRING(c.value,3,2) as mes,
    		Cast(CONCAT((Cast(year(getdate()) as varchar(4))),SUBSTRING(c.value,3,2),LEFT (c.value,2)) as date) as miFecha
    	From c 
    	)
    	Insert into @fechas (id, fechas, fechaInicio, fechafin)
    
    	Select f.id, f.miFecha, d.FechaInicioSemana, d.FechaLimiteSemana
    	
    	from FechasNacimiento f
    		cross join DatosActuales d
    	
    
    	Select f.id, f.fechas from @fechas  f
    		where f.fechas >= f.fechaInicio 
    		and f.fechas <= f.fechafin


    Y solo encajan los registros 3,4,5,6 y 7.

    Split

    https://javifer2.wordpress.com/2019/10/27/string-split-separar-cadenas/

    martes, 2 de febrero de 2021 21:44

Todas las respuestas

  • La fecha seria 14 de Abril de 1993, ¿Cómo se representa 14 de Abril de 2020?



    IIslas Master Consultant SQL Server

    martes, 2 de febrero de 2021 20:04
  • Hola Reynaldo Sanchez:

    Vamos a dividir el problema en varias partes para poder obtener una solución óptima y adecuada a tú planteamiento.

    La primera parte es dividir el varchar en partes, para poder extraer la fecha de nacimiento.

    Existen varias alternativas a esto, yo te voy a poner una con dos funciones, que funciona muy bien y que además puedes aprovechar para muchos desarrollos.

    CREATE FUNCTION [dbo].[GetNums]
    (
    @low AS BIGINT, 
    @high AS BIGINT
    ) RETURNS TABLE
    AS
    RETURN
      WITH
      L0   AS (SELECT c FROM (SELECT 1 UNION ALL SELECT 1) AS D(c)),
      L1   AS (SELECT 1 AS c FROM L0 AS A CROSS JOIN L0 AS B),
      L2   AS (SELECT 1 AS c FROM L1 AS A CROSS JOIN L1 AS B),
      L3   AS (SELECT 1 AS c FROM L2 AS A CROSS JOIN L2 AS B),
      L4   AS (SELECT 1 AS c FROM L3 AS A CROSS JOIN L3 AS B),
      L5   AS (SELECT 1 AS c FROM L4 AS A CROSS JOIN L4 AS B),
      Nums AS (SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) 
    AS rownum
              FROM L5)
      SELECT TOP(@high - @low + 1) @low + rownum - 1 AS n
      FROM Nums
      ORDER BY rownum;

    Esta primera función nos devuelve una columna con números entre el rango facilitado como parámetros de la función.

    https://tsql.solidq.com/SourceCodes/GetNums.txt

    La segunda función utiliza esta como parte del desarrollo

    -- Erland's split_me
    CREATE FUNCTION [dbo].[ufn_inline_split_me]
    (
    @param nvarchar(MAX), 
    @delimiter nchar(1) = N','
    )
    RETURNS table AS
    RETURN (
    SELECT
        ROW_NUMBER() OVER(ORDER BY n) AS pos,
        LTRIM(RTRIM(CONVERT(nvarchar(4000),
        SUBSTRING(@param, n,
        CHARINDEX(@delimiter, 
            @param + CONVERT(nvarchar(MAX), 
        @delimiter), n) - n)))) 
        AS Value
    FROM
        dbo.GetNums(1, LEN(@param))
    WHERE
        SUBSTRING(CONVERT(nvarchar(MAX), @delimiter) 
       + @param, n, 1) 
        = @delimiter
    );

    Con estas dos funciones, ya podemos partir un varchar.

    Select * 
    	From dbo.ufn_inline_split_me
    		('401-140493-0004E','-') FN;


    Entonces obtniendo de las filas que devuelve la función, la que corresponde con pos=2 ya disponemos de las fechas de nacimiento.

    Segunda parte importante, es la semana.

    Aquí depende un poco de lo que quieras obtener, y de cuando. Porque la semana en us-english empieza el domingo y en español, empieza en lunes. Pero además el script lo puedes aplicar ....¿qué día?.

    Escenario:

    Create Table dbo.UsersNicaragua 
    (
    	id int identity(1,1) primary key
      , nombre varchar(100)
      , dni varchar(16)
    );
    Go
    Insert Into dbo.UsersNicaragua
    (nombre, dni)
    values
    ('Bea1','401-140493-0004E'),
    ('Bea2','xxx-010278-yyyyy'),
    ('Bea3','xxx-020273-yyyyy'),
    ('Bea4','xxx-030271-yyyyy'),
    ('Bea5','xxx-040292-yyyyy'),
    ('Bea6','xxx-050294-yyyyy'),
    ('Bea3','xxx-070253-yyyyy'),
    ('Bea4','xxx-080261-yyyyy'),
    ('Bea5','xxx-310182-yyyyy'),
    ('Bea6','xxx-300165-yyyyy');
    Go

    Como no nos importa la parte izquierda y la derecha del varchar, en todos los registros va lo mismo.

    Set dateFirst 1;--Indica cual es el primer día de la semana
    
    ;with c as (
    	Select * from dbo.UsersNicaragua u
    		cross apply dbo.ufn_inline_split_me(u.dni,'-') fn
    	where fn.pos = 2 
    	)
    Select * from c

    Con esto tenemos los datos fuentes y el valor spliteado.

    Ahora vamos a continuar con los datos relativos a la semana

    Select 
    	Cast(Cast((DateAdd(DAY,7-( DATEPART(dw,getdate())),getdate())) as date) as datetime)  as FechaLimiteSemana,
    	Cast(Cast(GETDATE() as date) as datetime) as FechaInicioSemana -- hoy
    


    Esto que vamos a meter en un conjunto de una tabla de expresión común, es la parte en la que tendrás que pensar como ejecutar. Yo he establecido, como inicio de la semana el día de hoy, y como fin de la semana el domingo correspondiente a esta semana.

    Otra parte que hay que obtener es como bien indicabas la fecha como un date.

    Set dateFirst 1;--Indica cual es el primer día de la semana
    
    ;with c as (
    	Select * from dbo.UsersNicaragua u
    		cross apply dbo.ufn_inline_split_me(u.dni,'-') fn
    	where fn.pos = 2 
    	)
    	, DatosActuales as (
    Select 
    	Cast(Cast((DateAdd(DAY,7-( DATEPART(dw,getdate())),getdate())) as date) as datetime)  as FechaLimiteSemana,
    	Cast(Cast(GETDATE() as date) as datetime) as FechaInicioSemana -- hoy
    	)
    	, FechasNacimiento as (
    	Select 
    		c.id,
    		c.value,
    		LEFT (c.value,2) as dia,
    		SUBSTRING(c.value,3,2) as mes,
    		DATEFROMPARTS(YEAR(getdate()),Cast((SUBSTRING(c.value,3,2)) as int),Cast((LEFT (c.value,2)) as int)) as fecha
    	From c 
    	)
    	Select * from FechasNacimiento

    Como puedes observar tenemos en la columna fecha, la fecha de nacimiento, pero con el año actual, para poder hacer una restricción donde la fecha sea mayor o igual que la semana inicial, y menor o igual que la semana final. (Esta parte queda a tú criterio)

    Ya para finalizar lo único que nos queda es mezclar los conjuntos.

    Set dateFirst 1;--Indica cual es el primer día de la semana
    Declare @fechas table (id int, fechas date, fechaInicio date, fechafin date);
    
    ;with c as (
    	Select * from dbo.UsersNicaragua u
    		cross apply dbo.ufn_inline_split_me(u.dni,'-') fn
    	where fn.pos = 2 
    	)
    	, DatosActuales as (
    	Select 
    		Cast((DateAdd(DAY,7-( DATEPART(dw,getdate())),getdate())) as date)  as FechaLimiteSemana,
    		Cast(GETDATE() as date) as FechaInicioSemana -- hoy
    	)
    	, FechasNacimiento as (
    	Select 
    		c.id,
    		c.value,
    		LEFT (c.value,2) as dia,
    		SUBSTRING(c.value,3,2) as mes,
    		Cast(CONCAT((Cast(year(getdate()) as varchar(4))),SUBSTRING(c.value,3,2),LEFT (c.value,2)) as date) as miFecha
    	From c 
    	)
    	Insert into @fechas (id, fechas, fechaInicio, fechafin)
    
    	Select f.id, f.miFecha, d.FechaInicioSemana, d.FechaLimiteSemana
    	
    	from FechasNacimiento f
    		cross join DatosActuales d
    	
    
    	Select f.id, f.fechas from @fechas  f
    		where f.fechas >= f.fechaInicio 
    		and f.fechas <= f.fechafin


    Y solo encajan los registros 3,4,5,6 y 7.

    Split

    https://javifer2.wordpress.com/2019/10/27/string-split-separar-cadenas/

    martes, 2 de febrero de 2021 21:44