none
Como gerar uma Data dentro de uma Stored Procedure RRS feed

  • Pergunta

  • Boa tarde, amigos.

    Minha dúvida pode ser bem boba, mas estou há um bom tempo trabalhando no processo sem nenhum resultado.

    Meu problema é o seguinte:

    Estou montando uma stored procedure para automatizar o lançamento de mensalidades em um banco de dados. O processo todo transcorre dentro de uma SP e estou usando um Cursor para percorrer uma tabela de clientes e gerar as mensalidades. O meu problema é a data de vencimento, que pelo que vi, após a execução de todo o processo está ficando nula.

    Através da leitura do cursor, armazeno em uma variável, o dia do vencimento escolhido pelo cliente e faço uma concatenação para gerar a data. Não está gerando erro, mas a data de vencimento pelo visto não está sendo aceita pelo banco de dados.

    set @Vencimento=convert(datetime,replicate(0,2-len(@DiaPag)) + convert(varchar,@DiaPag) + '/' + replicate(0,2-len(month(getdate()))) + convert(varchar,month(getdate())) + '/' + convert(varchar,year(getdate())),103)
    

    Como faço para gerar esta data de vencimento?

    Obrigado pela atenção.

    sexta-feira, 12 de julho de 2019 20:04

Todas as Respostas

  • José,

    Vamos por partes:

    1 - Qual é a versão do SQL Server?

    2 - Qual deve ser o formato desta data de vencimento?

    3 - Existe algum critério que devemos levar em consideração para gerar a data?

    Poderia nos explicar o porque você esta realizando as concatenações e conversões?


    Pedro Antonio Galvão Junior [MVP | MCC | MSTC | MIE | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados Relacional e Data Warehouse | Professor Universitário | @JuniorGalvaoMVP | http://pedrogalvaojunior.wordpress.com]

    sexta-feira, 12 de julho de 2019 20:47
  • Boa tarde.

    1) A versão é 2005 express.

    2) O formato utilizado é MM/dd/yyyy.

    3) Como disse, na stored procedure, ao percorrer uma tabela com os registros dos clientes, é capturado o dia do vencimento de cada cliente, que na instrução está descrito como @DiaPag.

    As concatenações usei para montar a data com a variável: @DiaPag +  mês corrente + o ano. As conversões, porque estava apresentando erro, e pelo que entendi, era devido ao uso de valores sendo concatenados com as barras '/'. Aplicando as conversões o erro parou.

    Será que ficou claro?

    sexta-feira, 12 de julho de 2019 21:02
  • Deleted
    sexta-feira, 12 de julho de 2019 22:57
  • Bom dia.

    Obrigado pelas informações.

    Mas infelizmente ainda continua gerando nulo ao gravar os dados no banco.

    Bom, a variável @Vencimento está declarada como Datetime e a @DiaPag como int.

    Ocorre o seguinte, após gerar a data de vencimento, é chamada uma outra SP que faz a gravação dos dados no banco com os dados que são gerados nesta SP. Todos os dados estão sendo gravados corretamente exceto a data do vencimento que está ficando nula. Na SP que é chamada a variável que recebe @Vencimento também está declarada como DATETIME e esta SP está funcionando, porque em alguns casos, ela é chamada diretamente e grava os dados corretamente.

    Fiz alguns teste aqui, e cheguei a um ponto interessante:

    1) copiei o código da SP_2 que é chamada pela SP_1, para dentro da SP_1, não ocorrendo mais a chamada com passagem de parâmetros, a resposta do processo foi a mesma, data de vencimento nula;

    2) deduzi então que há algo errado na gravação dos dados, mas funciona quando a aplicação cliente efetua o processo direto, chamando a SP_2. Fiz o seguinte teste: mudei o tipo da variável de DATETIME para VARCHAR(10) e fixei um valor '07/15/2019'. Quando executei a SP_1 com todo os código, gravou perfeitamente. Voltei e comentei a linha onde fixava o valor do vencimento e executei novamente a SP_1, onde agora a data do vencimento é gerada conforme a instrução anterior, e o resultado foi a gravação do NULO no banco.

    Alguém consegue me explicar o que está ocorrendo?

    Se @Vencimento='07/15/2019' -> grava correto

    Se @Vencimento=cast(month(getdate()) as varchar(2)) + '/' + cast(@DiaPag as varchar(2)) + '/' + 
    cast(year(getdate()) as varchar(4)) -> data nula no banco

    Obrigado pela atenção.


    José Acílio

    sábado, 13 de julho de 2019 13:33
  • José,

    Aparentemente o problema esta relacionado justamente ao formato que a data esta sendo passada via aplicação ou stored procedure para o SQL Server. Talvez você não saiba, mas o SQL Server por padrão trabalha com o idioma Inglês - Estados Unidos, e a formatação de valores de data, hora e dinheiro respeitam justamente este idioma.

    Vamos fazer um teste, tente passar os valores de data neste formato:

    '20190715' ou '2019-07-15'

    Veja qual dos dois esta dando certo, se funcionar, poderemos então pensar em aplicar uma conversão explícita utilizando os padrões de formatação de valores.


    Pedro Antonio Galvão Junior [MVP | MCC | MSTC | MIE | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados Relacional e Data Warehouse | Professor Universitário | @JuniorGalvaoMVP | http://pedrogalvaojunior.wordpress.com]

    sábado, 13 de julho de 2019 14:00
  • Bom dia.

    Testei com as suas sugestões e o resultado ainda é o mesmo, data nula no bd.

    Com relação ao padrão da data o servidor SQL estão no idioma Inglês.

    Alguma outra sugestão?


    José Acílio

    sábado, 13 de julho de 2019 14:20
  • José,

    Algumas perguntas:

    1 - Qual é o linguagem do usuário?

    2 - Por acaso durante o processamento desta query esta ocorrendo alguma mudança de idioma, saberia nos dizer se alguma instrução Set Language esta sendo executada?

    Poderia nos postar os resultados destes blocos de códigos:

    -- Código 1 --
    Declare @Data DateTime
    Set @Data = GetDate()
    
    Select @Data As 'Código 1'
    
    -- Código 2 --
    Set @Data = '2019-07-13'
    Select @Data As 'Código 2'
    
    -- Código 3 --
    Set @Data = '2019/07/13'
    Select @Data As 'Código 3.1'
     
    Select Convert(Varchar(10),@Data,102) As 'Código 3.2'
    
    Select Convert(Varchar(10),@Data,103) As 'Código 3.3'
    Go


    Pedro Antonio Galvão Junior [MVP | MCC | MSTC | MIE | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados Relacional e Data Warehouse | Professor Universitário | @JuniorGalvaoMVP | http://pedrogalvaojunior.wordpress.com]

    sábado, 13 de julho de 2019 14:30
  • Deleted
    sábado, 13 de julho de 2019 14:35
  • No banco, a coluna está definida como DATETIME.

    Na verdade, dentro da base de dados, as datas são apresentadas no formato yyyy-mm-dd, o formato citado é o formato que aplicação cliente envia a data para o banco, e em todas as tabelas que tem campo data, a data é registrada corretamente.

    Como eu disse, na resposta anterior, por que quando eu fixo a data em uma variável VARCHAR ela grava corretamente no banco sem sequer ser necessário a conversão para o formato DATETIME? E a data que está sendo gerada em uma variável DATETIME não registra?

    Este é meu pesadelo...


    José Acílio

    sábado, 13 de julho de 2019 14:50
  • Deleted
    sábado, 13 de julho de 2019 14:51
  • Segue:

    ALTER PROCEDURE [dbo].[proc_Clientes_GerarMensalidades] 
    	-- Add the parameters for the stored procedure here
    	@Valor2x money,
    	@Valor3x money,
    	@Valor4x money,
    	@Valor5x money,
    	@ValorDupla2x money,
    	@ValorDupla3x money,
    	@FormaPag int
    AS
    BEGIN
    	-- SET NOCOUNT ON added to prevent extra result sets from
    	-- interfering with SELECT statements.
    	SET NOCOUNT ON;
    	
    	declare @Cliente int, @DiaPag int, @Documento nvarchar(9)
    	declare @Vencimento datetime
    	declare @Mensalidade int
    
    -- incluido em 13/07/2019
    	declare @NumVezes as int;
    	declare @vMensalidade as money;
    	declare @ProxLanc int;
    
    	-- cria o cursor que será percorrido
    	declare cursor_objects CURSOR FOR 
    		select idcliente, diapagamento from clientes where clienteativo='S'
    
    	-- abre o cursor para leitura
    	OPEN cursor_objects
    
    	-- lendo a proxima linha
    	FETCH NEXT FROM cursor_objects INTO @Cliente, @DiaPag
    
    	-- percorrendo as linhas do cursor
    	WHILE @@FETCH_STATUS=0
    	BEGIN
    
    -- incluido em 13/07/19----------------------------------
    	set @NumVezes=(select count(clientes_atividades.idatividade) 
    					from clientes_atividades 
    					where idaluno=@Cliente)
    	
    	if @NumVezes>0
    	begin
    
    	if @NumVezes=2 or @NumVezes=1
    		set @vMensalidade=@Valor2x
    	else if @NumVezes=3
    		set @vMensalidade=@Valor3x
    	else if @NumVezes=4
    		set @vMensalidade=@Valor4x
    	else if @NumVezes=5
    		set @vMensalidade=@Valor5x
    ----------------------------------------------------------
    	
    		set @Mensalidade=(select idtitulo from titulosr 
    						where idaluno=@Cliente and month(dtvencimento)=month(getdate()) and year(dtvencimento)=year(getdate()))
    		if @Mensalidade is null
    		begin
    			-- gera o numero do documento do cliente (codigo do cliente-mes)
    			set @Documento=replicate(0,6-len(@cliente)) + convert(varchar,@Cliente) + '-' + REPLICATE(0,2-len(month(getdate()))) + 
    								convert(varchar,month(getdate()));
    			
    			-- gera a data de vencimento	
    			--set @Vencimento=convert(datetime,replicate(0,2-len(@DiaPag)) + convert(varchar,@DiaPag) + '/' + replicate(0,2-len(month(getdate()))) + 
    			--					convert(varchar,month(getdate())) + '/' + convert(varchar,year(getdate())),103)
    
    			--set @Vencimento=replicate(0,2-len(month(getdate()))) + 	convert(varchar,month(getdate())) + '/' + 
    			--					replicate(0,2-len(@DiaPag)) + convert(varchar,@DiaPag) + '/' + convert(varchar,year(getdate()))
    			
    			--set @Vencimento=convert(datetime,cast(@DiaPag as varchar(2)) + '/' + cast(month(getdate()) as varchar(2)) + '/' + cast(year(getdate()) as varchar(4)),103);
    
    			--set @Vencimento=cast(month(getdate()) as varchar(2)) + '/' + cast(@DiaPag as varchar(2)) + '/' + 
    			--				cast(year(getdate()) as varchar(4)) --,103);
    			
    			set @Vencimento=convert(datetime,cast(year(getdate()) as varchar(4)) + '/' + cast(month(getdate()) as varchar(2)) + '/' + cast(@DiaPag as varchar(2)),103)
    							 --,103);
    
    			--print @Vencimento
    
    -- incluido em 13/07/19			
    	-- identifica o codigo do proximo lancamento
    	set @ProxLanc=(select max(idtitulo) as ultimo from titulosr)
    	if @ProxLanc is null
    		set @ProxLanc=1
    	else
    		Set @ProxLanc=@ProxLanc+1
    
    	declare @Pagamento varchar(10)
    	set @Pagamento='07/15/2019'
    	--set @Vencimento='07/15/2019'
    
    	insert into titulosr (idtitulo, data, numdocumento, idaluno, dtvencimento, valor, idformapag, dtpagamento)
    				values (@ProxLanc, getdate(), @Documento, @Cliente, @Vencimento, @vMensalidade, @FormaPag, @Pagamento)
    --------------------------------------------------------------------------------------------------------------
    			/*
    			-- chama a stored procedure passando os parametros necessarios
    			EXEC proc_Clientes_GerarFinanceiro_Aluno @Cliente, @Documento, @Vencimento, @Valor2x, @Valor3x, @Valor4x, @Valor5x, @ValorDupla2x, 
    														@ValorDupla3x , @FormaPag
    			*/
    	end -- @NumVezes
    
    		end
    		-- lendo a proxima linha
    		FETCH NEXT FROM cursor_objects INTO @Cliente, @DiaPag
    	END
    	-- fechando o cursor para leitura
    	CLOSE cursor_objects
    	-- desalocanco o cursor
    	DEALLOCATE cursor_objects
    END
    


    José Acílio

    sábado, 13 de julho de 2019 14:54
  • Segue:

    ALTER PROCEDURE [dbo].[proc_Clientes_GerarMensalidades] 
    	-- Add the parameters for the stored procedure here
    	@Valor2x money,
    	@Valor3x money,
    	@Valor4x money,
    	@Valor5x money,
    	@ValorDupla2x money,
    	@ValorDupla3x money,
    	@FormaPag int
    AS
    BEGIN
    	-- SET NOCOUNT ON added to prevent extra result sets from
    	-- interfering with SELECT statements.
    	SET NOCOUNT ON;
    	
    	declare @Cliente int, @DiaPag int, @Documento nvarchar(9)
    	declare @Vencimento datetime
    	declare @Mensalidade int
    
    -- incluido em 13/07/2019
    	declare @NumVezes as int;
    	declare @vMensalidade as money;
    	declare @ProxLanc int;
    
    	-- cria o cursor que será percorrido
    	declare cursor_objects CURSOR FOR 
    		select idcliente, diapagamento from clientes where clienteativo='S'
    
    	-- abre o cursor para leitura
    	OPEN cursor_objects
    
    	-- lendo a proxima linha
    	FETCH NEXT FROM cursor_objects INTO @Cliente, @DiaPag
    
    	-- percorrendo as linhas do cursor
    	WHILE @@FETCH_STATUS=0
    	BEGIN
    
    -- incluido em 13/07/19----------------------------------
    	set @NumVezes=(select count(clientes_atividades.idatividade) 
    					from clientes_atividades 
    					where idaluno=@Cliente)
    	
    	if @NumVezes>0
    	begin
    
    	if @NumVezes=2 or @NumVezes=1
    		set @vMensalidade=@Valor2x
    	else if @NumVezes=3
    		set @vMensalidade=@Valor3x
    	else if @NumVezes=4
    		set @vMensalidade=@Valor4x
    	else if @NumVezes=5
    		set @vMensalidade=@Valor5x
    ----------------------------------------------------------
    	
    		set @Mensalidade=(select idtitulo from titulosr 
    						where idaluno=@Cliente and month(dtvencimento)=month(getdate()) and year(dtvencimento)=year(getdate()))
    		if @Mensalidade is null
    		begin
    			-- gera o numero do documento do cliente (codigo do cliente-mes)
    			set @Documento=replicate(0,6-len(@cliente)) + convert(varchar,@Cliente) + '-' + REPLICATE(0,2-len(month(getdate()))) + 
    								convert(varchar,month(getdate()));
    			
    			-- gera a data de vencimento	
    			--set @Vencimento=convert(datetime,replicate(0,2-len(@DiaPag)) + convert(varchar,@DiaPag) + '/' + replicate(0,2-len(month(getdate()))) + 
    			--					convert(varchar,month(getdate())) + '/' + convert(varchar,year(getdate())),103)
    
    			--set @Vencimento=replicate(0,2-len(month(getdate()))) + 	convert(varchar,month(getdate())) + '/' + 
    			--					replicate(0,2-len(@DiaPag)) + convert(varchar,@DiaPag) + '/' + convert(varchar,year(getdate()))
    			
    			--set @Vencimento=convert(datetime,cast(@DiaPag as varchar(2)) + '/' + cast(month(getdate()) as varchar(2)) + '/' + cast(year(getdate()) as varchar(4)),103);
    
    			--set @Vencimento=cast(month(getdate()) as varchar(2)) + '/' + cast(@DiaPag as varchar(2)) + '/' + 
    			--				cast(year(getdate()) as varchar(4)) --,103);
    			
    			set @Vencimento=convert(datetime,cast(year(getdate()) as varchar(4)) + '/' + cast(month(getdate()) as varchar(2)) + '/' + cast(@DiaPag as varchar(2)),103)
    							 --,103);
    
    			--print @Vencimento
    
    -- incluido em 13/07/19			
    	-- identifica o codigo do proximo lancamento
    	set @ProxLanc=(select max(idtitulo) as ultimo from titulosr)
    	if @ProxLanc is null
    		set @ProxLanc=1
    	else
    		Set @ProxLanc=@ProxLanc+1
    
    	declare @Pagamento varchar(10)
    	set @Pagamento='07/15/2019'
    	--set @Vencimento='07/15/2019'
    
    	insert into titulosr (idtitulo, data, numdocumento, idaluno, dtvencimento, valor, idformapag, dtpagamento)
    				values (@ProxLanc, getdate(), @Documento, @Cliente, @Vencimento, @vMensalidade, @FormaPag, @Pagamento)
    --------------------------------------------------------------------------------------------------------------
    			/*
    			-- chama a stored procedure passando os parametros necessarios
    			EXEC proc_Clientes_GerarFinanceiro_Aluno @Cliente, @Documento, @Vencimento, @Valor2x, @Valor3x, @Valor4x, @Valor5x, @ValorDupla2x, 
    														@ValorDupla3x , @FormaPag
    			*/
    	end -- @NumVezes
    
    		end
    		-- lendo a proxima linha
    		FETCH NEXT FROM cursor_objects INTO @Cliente, @DiaPag
    	END
    	-- fechando o cursor para leitura
    	CLOSE cursor_objects
    	-- desalocanco o cursor
    	DEALLOCATE cursor_objects
    END


    José Acílio

    Perceba que, a variável @Pagamento está fixada para que eu veja que está funcionando a gravação no campo com uma variável do tipo VARCHAR.


    José Acílio

    sábado, 13 de julho de 2019 14:58
  • Deleted
    sábado, 13 de julho de 2019 15:15
  • Todo o conteúdo da procedure GerarFinanceiro_Aluno já está na procedure principal. Fiz isso para não ter dúvidas sobre o funcionamento. É mais fácil analisar o problema em um mesmo local. Você pode ver que ela está comentada.

    Todo o processo válido esta descrito no código que informei. No atual momento, não há mais nenhuma chamada a outra procedure.


    José Acílio

    sábado, 13 de julho de 2019 15:20
  • José Acílio,

    Porque a variável @Pagamento esta declarada como Varchar(10) e a @Vencimento como DateTime?

    Você chegou a executar os exemplos de código que eu te mandei como forma de passagem de valores na sua aplicação?


    Pedro Antonio Galvão Junior [MVP | MCC | MSTC | MIE | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados Relacional e Data Warehouse | Professor Universitário | @JuniorGalvaoMVP | http://pedrogalvaojunior.wordpress.com]

    sábado, 13 de julho de 2019 23:31
  • Deleted
    domingo, 14 de julho de 2019 12:21
  • Bom dia, Pedro.

    Seguem os resultados:

    Código 1 = 2019-07-19 09:40:20.070

    Código 2 = 2019-07-13 00:00:00.000

    Código 3.1 = 2019-07-13 00:00:00.000

    Código 3.2 = 2019.07.13

    Código 3.3 = 13/07/2019


    José Acílio

    sexta-feira, 19 de julho de 2019 12:44
  • José Acílio,

    Obrigado pelo retorno, os resultados comprovam que o SQL Server esta reconhecendo e apresentando os dados no formato correto, sendo assim, verifique as sugestões que o José Diz destacou na estrutura do código da sua Stored Procedure.

    Principalmente neste trecho:

    "Ao passar a variável @Pagamento como valor no comando INSERT, ocorrerá conversão implícita de varchar para datetime e a interpretação dependerá das definições de language e/ou dateformat da sessão. Sugiro que não faça esse tipo de testes, pois eles somente geram ainda mais confusão. Se quer testar com um valor fixo,  faça o seguinte:

         declare @Pagamento datetime;
         set @Pagamento= convert (datetime, '15/7/2019', 103);  -- dd/mm/aaaa"


    Pedro Antonio Galvão Junior [MVP | MCC | MSTC | MIE | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados Relacional e Data Warehouse | Professor Universitário | @JuniorGalvaoMVP | http://pedrogalvaojunior.wordpress.com]

    sexta-feira, 19 de julho de 2019 15:39