TÓPICO 3 – USING SINGLE-ROW FUNCTIONS TO CUSTOMIZE OUTPUT

  • Subtópico 3.1 – Describe various types of functions available in SQL
  • Subtópico 3.2 – Use character, number, and date functions in SELECT statements

Continuando a nossa saga, neste post falarei sobre mais um tópico da prova 1Z0-051, que aborda as diversas funções disponíveis para uso no Oracle.

1) FUNÇÕES DISPONÍVEIS NO SQL

Funções, de uma forma geral, são usadas processar uma entrada, resultando em uma saída processada de acordo com o tipo e propósito dessa função. Eu costumo fazer a seguinte comparação meio esdrúxula: Funções são como pratos de comida, ou seja, nós entramos com os ingrediêntes (parâmetros) e o resultado é um prato pronto (informação processada pela função). As funções mais comuns e possívelmente mais usadas no mundo Oracle são as seguintes:

* FUNÇÕES DE CARACTERES

– UPPER(S1)

Retorna a string S1, inteiramente em letras maiúsculas.

[et_pb_dmb_code_snippet _builder_version=”4.0.6″ code=”LS1yZXRvcm5hIG9zIG5vbWVzIGRlIHRvZG9zIG9zIGVtcHJlZ2Fkb3MsIGVtIGxldHJhcyBtYWnDunNjdWxhcwpTRUxFQ1QgVVBQRVIoTk9NRV9FTVApCiBGUk9NIENFUlRJRklDQUNBTy5UQUJfRU1QUkVHQURPUw==” hover_enabled=”0″]LS1yZXRvcm5hIG9zIG5vbWVzIGRlIHRvZG9zIG9zIGVtcHJlZ2Fkb3MsIGVtIGxldHJhcyBtYWnDunNjdWxhcwpTRUxFQ1QgVVBQRVIoTk9NRV9FTVApCiBGUk9NIENFUlRJRklDQUNBTy5UQUJfRU1QUkVHQURPUw==[/et_pb_dmb_code_snippet]

– LOWER(S1)

Retorna a string S1, inteiramente em letras minusculas.

[et_pb_dmb_code_snippet _builder_version=”4.0.6″ code=”LS1yZXRvcm5hIG9zIG5vbWVzIGRlIHRvZG9zIG9zIGVtcHJlZ2Fkb3MsIGVtIGxldHJhcyBtaW7DunNjdWxhcwpTRUxFQ1QgTE9XRVIoTk9NRV9FTVApCiBGUk9NIENFUlRJRklDQUNBTy5UQUJfRU1QUkVHQURPUw==” hover_enabled=”0″]LS1yZXRvcm5hIG9zIG5vbWVzIGRlIHRvZG9zIG9zIGVtcHJlZ2Fkb3MsIGVtIGxldHJhcyBtaW7DunNjdWxhcwpTRUxFQ1QgTE9XRVIoTk9NRV9FTVApCiBGUk9NIENFUlRJRklDQUNBTy5UQUJfRU1QUkVHQURPUw==[/et_pb_dmb_code_snippet]

– INITCAP(S1)

Transforma as iniciais de cada palavra de S1 em maiusculas. Retorna a string S1.

[et_pb_dmb_code_snippet _builder_version=”4.0.6″ code=”LS10cmFuc2Zvcm1hIGEgcHJpbWVpcmEgbGV0cmEgZGUgY2FkYSBub21lIHBhcmEgbWFpdXNjdWxhcwpTRUxFQ1QgSU5JVENBUChOT01FX0VNUCkKIEZST00gQ0VSVElGSUNBQ0FPLlRBQl9FTVBSRUdBRE9T” hover_enabled=”0″]LS10cmFuc2Zvcm1hIGEgcHJpbWVpcmEgbGV0cmEgZGUgY2FkYSBub21lIHBhcmEgbWFpdXNjdWxhcwpTRUxFQ1QgSU5JVENBUChOT01FX0VNUCkKIEZST00gQ0VSVElGSUNBQ0FPLlRBQl9FTVBSRUdBRE9T[/et_pb_dmb_code_snippet]

– CONCAT(S1, S2)

Concatena as strings S1 e S2 em uma única string.

[et_pb_dmb_code_snippet _builder_version=”4.0.6″ code=”LS11bmUgJ0EnIGUgJ0InIGVtIHVtYSB1bmljYSBzdHJpbmcKU0VMRUNUIENPTkNBVCgnQScsICdCJykgRlJPTSBEVUFM” hover_enabled=”0″]LS11bmUgJ0EnIGUgJ0InIGVtIHVtYSB1bmljYSBzdHJpbmcKU0VMRUNUIENPTkNBVCgnQScsICdCJykgRlJPTSBEVUFM[/et_pb_dmb_code_snippet]

– LPAD(S1, N1, [S2])

Preenche a esquerda da string S1, com o caractere determinado pela string S2, até que a string S1 fique do tamanho determinado em N1.

[et_pb_dmb_code_snippet _builder_version=”4.0.6″ code=”LS0gcHJlZW5jaGUgYSBlc3F1ZXJkYSBkZSBjYWRhIG5vbWUgZGUgZW1wcmVnYWRvLCBjb20gbyBwb250b3MgKC4pIAotLSBhdMOpIHF1ZSBhIHNvbWEgZGUgcG9udG9zICsgY2FyYWN0ZXJlcyBzZWphIGlndWFsIGEgNDAKU0VMRUNUIExQQUQoTk9NRV9FTVAsIDQwLCAnLicpCiBGUk9NIENFUlRJRklDQUNBTy5UQUJfRU1QUkVHQURPUw==” hover_enabled=”0″]LS0gcHJlZW5jaGUgYSBlc3F1ZXJkYSBkZSBjYWRhIG5vbWUgZGUgZW1wcmVnYWRvLCBjb20gbyBwb250b3MgKC4pIAotLSBhdMOpIHF1ZSBhIHNvbWEgZGUgcG9udG9zICsgY2FyYWN0ZXJlcyBzZWphIGlndWFsIGEgNDAKU0VMRUNUIExQQUQoTk9NRV9FTVAsIDQwLCAnLicpCiBGUk9NIENFUlRJRklDQUNBTy5UQUJfRU1QUkVHQURPUw==[/et_pb_dmb_code_snippet]

– RPAD(S1, N1, [S2])

Preenche a direita da string S1, com o caractere determinado pela string S2, até que a string S1 fique do tamanho determinado em N1.

[et_pb_dmb_code_snippet _builder_version=”4.0.6″ code=”LS0gcHJlZW5jaGUgYSBkaXJlaXRhIGRlIGNhZGEgbm9tZSBkZSBlbXByZWdhZG8sIGNvbSBvIHBvbnRvcyAoLikKLS0gYXTDqSBxdWUgYSBzb21hIGRlIHBvbnRvcyArIGNhcmFjdGVyZXMgc2VqYSBpZ3VhbCBhIDQwCgpTRUxFQ1QgUlBBRChOT01FX0VNUCwgNDAsICcuJykKIEZST00gQ0VSVElGSUNBQ0FPLlRBQl9FTVBSRUdBRE9T” hover_enabled=”0″]LS0gcHJlZW5jaGUgYSBkaXJlaXRhIGRlIGNhZGEgbm9tZSBkZSBlbXByZWdhZG8sIGNvbSBvIHBvbnRvcyAoLikKLS0gYXTDqSBxdWUgYSBzb21hIGRlIHBvbnRvcyArIGNhcmFjdGVyZXMgc2VqYSBpZ3VhbCBhIDQwCgpTRUxFQ1QgUlBBRChOT01FX0VNUCwgNDAsICcuJykKIEZST00gQ0VSVElGSUNBQ0FPLlRBQl9FTVBSRUdBRE9T[/et_pb_dmb_code_snippet]

– LTRIM(S1, [S2])

Remove ocorrências da string S2, da esquerda da string S1. Caso S2 seja omitido, a função remove os brancos a esquerda.

[et_pb_dmb_code_snippet _builder_version=”4.0.6″ code=”LS0gcmVtb3ZlIG9zIHRyYcOnb3MgKC0pIGEgZXNxdWVyZGEgZGEgZnJhc2UgcGFzc2FkYSBubyBwcmltZWlybyAKLS0gcGFyw6JtZXRybwoKU0VMRUNUIExUUklNKCctLS0tLU9SQUNMRScsICctJykgRlJPTSBEVUFMOw==” hover_enabled=”0″]LS0gcmVtb3ZlIG9zIHRyYcOnb3MgKC0pIGEgZXNxdWVyZGEgZGEgZnJhc2UgcGFzc2FkYSBubyBwcmltZWlybyAKLS0gcGFyw6JtZXRybwoKU0VMRUNUIExUUklNKCctLS0tLU9SQUNMRScsICctJykgRlJPTSBEVUFMOw==[/et_pb_dmb_code_snippet]

– RTRIM(S1, [S2])

Remove ocorrências da string S2, da direita da string S1. Caso S2 seja omitido, a função remove os brancos a direita.

[et_pb_dmb_code_snippet _builder_version=”4.0.6″ code=”LS1yZW1vdmUgb3MgdHJhw6dvcyAoLSkgYSBkaXJlaXRhIGRhIHN0cmluZwoKU0VMRUNUIFJUUklNKCdPUkFDTEUtLS0tLS0nLCAnLScpIEZST00gRFVBTDs=” hover_enabled=”0″]LS1yZW1vdmUgb3MgdHJhw6dvcyAoLSkgYSBkaXJlaXRhIGRhIHN0cmluZwoKU0VMRUNUIFJUUklNKCdPUkFDTEUtLS0tLS0nLCAnLScpIEZST00gRFVBTDs=[/et_pb_dmb_code_snippet]

– TRIM(INFO CHAR FROM FONTE)

Remove caracteres a esquerda, direita ou de ambos os lados de uma string. Também pode ser usado para remover caracteres em branco das extremidades de uma string. Onde:

  • info: Pode ser LEADING (Caracteres a esquerda), TRAILING (Caracteres a direita), BOTH(Caracteres de ambos os lados). Caso omitido, o padrão é BOTH.
  • char: Caractere a ser removido (Só pode ser um caractere)
  • source: A string fonte. Essa função retorna NULL caso esse parâmetro seja omitido.
[et_pb_dmb_code_snippet _builder_version=”4.0.6″ code=”LS1yZW1vdmUgb3MgdHJhw6dvcyAoLSkgcHJlc2VudGVzIGEgZGlyZWl0YSBkYSBzdHJpbmcKClNFTEVDVCBUUklNKFRSQUlMSU5HICctJyBGUk9NICctLS0tLS1PUkFDTEUtLS0tLS0nKSBGUk9NIERVQUw7CgotLXJlbW92ZSBvcyB0cmHDp29zICgtKSBwcmVzZW50ZXMgYSBlc3F1ZXJkYSBkYSBzdHJpbmcKClNFTEVDVCBUUklNKExFQURJTkcgJy0nIEZST00gJy0tLS0tLU9SQUNMRS0tLS0tLScpIEZST00gRFVBTDsKCi0tcmVtb3ZlIG9zIHRyYcOnb3MgKC0pIHByZXNlbnRlcyBlbSBhbWJvcyBvcyBsYWRvcyBkYSBzdHJpbmcKClNFTEVDVCBUUklNKEJPVEggJy0nIEZST00gJy0tLS0tLU9SQUNMRS0tLS0tLScpIEZST00gRFVBTDsKCi0tcmVtb3ZlIG9zIGNhcmFjdGVyZXMgZW0gYnJhbmRvIGRlIGFtYmFzIGFzIGV4dHJlbWlkYWRlcyBkYSBzdHJpbmcKClNFTEVDVCBUUklNKCcgT1JBQ0xFICcpIEZST00gRFVBTDs=” hover_enabled=”0″]LS1yZW1vdmUgb3MgdHJhw6dvcyAoLSkgcHJlc2VudGVzIGEgZGlyZWl0YSBkYSBzdHJpbmcKClNFTEVDVCBUUklNKFRSQUlMSU5HICctJyBGUk9NICctLS0tLS1PUkFDTEUtLS0tLS0nKSBGUk9NIERVQUw7CgotLXJlbW92ZSBvcyB0cmHDp29zICgtKSBwcmVzZW50ZXMgYSBlc3F1ZXJkYSBkYSBzdHJpbmcKClNFTEVDVCBUUklNKExFQURJTkcgJy0nIEZST00gJy0tLS0tLU9SQUNMRS0tLS0tLScpIEZST00gRFVBTDsKCi0tcmVtb3ZlIG9zIHRyYcOnb3MgKC0pIHByZXNlbnRlcyBlbSBhbWJvcyBvcyBsYWRvcyBkYSBzdHJpbmcKClNFTEVDVCBUUklNKEJPVEggJy0nIEZST00gJy0tLS0tLU9SQUNMRS0tLS0tLScpIEZST00gRFVBTDsKCi0tcmVtb3ZlIG9zIGNhcmFjdGVyZXMgZW0gYnJhbmRvIGRlIGFtYmFzIGFzIGV4dHJlbWlkYWRlcyBkYSBzdHJpbmcKClNFTEVDVCBUUklNKCcgT1JBQ0xFICcpIEZST00gRFVBTDs=[/et_pb_dmb_code_snippet]

– LENGTH(S1)

Retorna o tamanho da string S1. Ex:

[et_pb_dmb_code_snippet _builder_version=”4.0.6″ code=”LS0gcmV0b3JuYSBvIG7Dum1lcm8gZGUgY2FyYWN0ZXJlcyAobWFpcyBlc3Bhw6dvcykgZGEgc3RyaW5nLiAKLS0gTm8gY2FzbyBhYmFpeG8sIHJldG9ybmEgNgoKU0VMRUNUIExFTkdUSCgnT1JBQ0xFJykgRlJPTSBEVUFMOw==” hover_enabled=”0″]LS0gcmV0b3JuYSBvIG7Dum1lcm8gZGUgY2FyYWN0ZXJlcyAobWFpcyBlc3Bhw6dvcykgZGEgc3RyaW5nLiAKLS0gTm8gY2FzbyBhYmFpeG8sIHJldG9ybmEgNgoKU0VMRUNUIExFTkdUSCgnT1JBQ0xFJykgRlJPTSBEVUFMOw==[/et_pb_dmb_code_snippet]

– INSTR(S1, S2, [POS], [N1])

Localiza uma String (S2) dentro de outra string (S1). O parâmetro POS é a posição inicial de S1 onde se iniciará a procura da string S2. N1 é a ocorrência de S2 em S1. Essa função retorna a posição da string S2 dentro da string S1.

[et_pb_dmb_code_snippet _builder_version=”4.0.6″ code=”LS0gUmV0b3JuYSBhIHBvc2ljYW8gZGEgc3RyaW5nICdDJyBkZW50cm8gZGEgcGFsYXZyYSAnT1JBQ0xFJy4gCi0tIEVzc2UgZXhlbXBsbyByZXRvcm5hcmEgNAoKU0VMRUNUIElOU1RSKCdPUkFDTEUnLCAnQycsIDEsIDEpIEZST00gRFVBTA==” hover_enabled=”0″]LS0gUmV0b3JuYSBhIHBvc2ljYW8gZGEgc3RyaW5nICdDJyBkZW50cm8gZGEgcGFsYXZyYSAnT1JBQ0xFJy4gCi0tIEVzc2UgZXhlbXBsbyByZXRvcm5hcmEgNAoKU0VMRUNUIElOU1RSKCdPUkFDTEUnLCAnQycsIDEsIDEpIEZST00gRFVBTA==[/et_pb_dmb_code_snippet]

– SUBSTR(S1, POS, [TAM])

Extrai uma substring de S1, iniciando da posição POS, e continuando por TAM caracteres. Caso TAM seja omitido, S1 será verificada por completo até seu fim.

[et_pb_dmb_code_snippet _builder_version=”4.0.6″ code=”LS1hIHBhcnRpciBkYSBwb3NpY2FvCgpTRUxFQ1QgU1VCU1RSKCdPUkFDTEUnLCAzLCAyKSBGUk9NIERVQUw7Ck9CUzE6IFBvZGUtc2Ugc2VsZWNpb25hciBvcyDDumx0aW1vcyBjYXJhY3RlcmVzIGRlIHVtYSBzdHJpbmcsIHVzYW5kbyBuw7ptZXJvcyBuZWdhdGl2b3MuCgotLXJldG9ybmEgb3MgMiDDumx0aW1vcyBjYXJhY3RlcmVzCgpTRUxFQ1QgU1VCU1RSKCdPUkFDTEUnLCAtMikgRlJPTSBEVUFMOw==” hover_enabled=”0″]LS1hIHBhcnRpciBkYSBwb3NpY2FvCgpTRUxFQ1QgU1VCU1RSKCdPUkFDTEUnLCAzLCAyKSBGUk9NIERVQUw7Ck9CUzE6IFBvZGUtc2Ugc2VsZWNpb25hciBvcyDDumx0aW1vcyBjYXJhY3RlcmVzIGRlIHVtYSBzdHJpbmcsIHVzYW5kbyBuw7ptZXJvcyBuZWdhdGl2b3MuCgotLXJldG9ybmEgb3MgMiDDumx0aW1vcyBjYXJhY3RlcmVzCgpTRUxFQ1QgU1VCU1RSKCdPUkFDTEUnLCAtMikgRlJPTSBEVUFMOw==[/et_pb_dmb_code_snippet]

* FUNÇÕES NUMÉRICAS

– ROUND(N, [I])

Arredonda o número N até I casas decimais. Caso I seja omitido, o valor default é 0, arredondando assim N para o número inteiro mais próximo. Caso I seja negativo, N é arredondado I casas para a esquerda.:

[et_pb_dmb_code_snippet _builder_version=”4.0.6″ code=”U0VMRUNUIFJPVU5EKDEwMC45OTk2LCAyKSB8fCAnIC0tLSAnIHx8IFJPVU5EKDI1LjY1KSB8fCAnIC0tLSAnIHx8IFJPVU5EKDQ2My45OTc2LCAtMikgRlJPTSBEVUFMOw==” hover_enabled=”0″]U0VMRUNUIFJPVU5EKDEwMC45OTk2LCAyKSB8fCAnIC0tLSAnIHx8IFJPVU5EKDI1LjY1KSB8fCAnIC0tLSAnIHx8IFJPVU5EKDQ2My45OTc2LCAtMikgRlJPTSBEVUFMOw==[/et_pb_dmb_code_snippet]

– TRUNC(N, [I])

Remove as casas decimais de N, deixando apenas I casas decimais. Caso I seja omitido, remove todas as casas decimais. É importante mencionar que essa cláusula não arredonda, mas apenas remove as casas decimais:

[et_pb_dmb_code_snippet _builder_version=”4.0.6″ code=”U0VMRUNUIFRSVU5DKDEwMCkgfHwgJyAtLS0gJyB8fCBUUlVOQyg2Ny41NzQyMzQ1LCAyKSB8fCAnIC0tLSAnIHx8IFRSVU5DKDIzLjg1NDg1KSBGUk9NIERVQUw7″ hover_enabled=”0″]U0VMRUNUIFRSVU5DKDEwMCkgfHwgJyAtLS0gJyB8fCBUUlVOQyg2Ny41NzQyMzQ1LCAyKSB8fCAnIC0tLSAnIHx8IFRSVU5DKDIzLjg1NDg1KSBGUk9NIERVQUw7[/et_pb_dmb_code_snippet]

– MOD(N1, N2)

Retorna o resto da divisão de N1 por N2. Ex:

[et_pb_dmb_code_snippet _builder_version=”4.0.6″ code=”U0VMRUNUIE1PRCgxMDAsIDMpIHx8ICcgLS0tICcgfHwgCiAgICAgICBNT0QoMTAwLCA2KSB8fCAnIC0tLSAnIHx8IAogICAgICAgTU9EKDIwLCA1KSAKICBGUk9NIERVQUw=” hover_enabled=”0″]U0VMRUNUIE1PRCgxMDAsIDMpIHx8ICcgLS0tICcgfHwgCiAgICAgICBNT0QoMTAwLCA2KSB8fCAnIC0tLSAnIHx8IAogICAgICAgTU9EKDIwLCA1KSAKICBGUk9NIERVQUw=[/et_pb_dmb_code_snippet]

* FUNÇÕES DE DATAS

– SYSDATE

Retorna a data/hora baseada no sistema operacional cujo ORACLE está instalado.

[et_pb_dmb_code_snippet _builder_version=”4.0.6″ code=”U0VMRUNUIFNZU0RBVEUgRlJPTSBEVUFMOw==” hover_enabled=”0″]U0VMRUNUIFNZU0RBVEUgRlJPTSBEVUFMOw==[/et_pb_dmb_code_snippet]

– ROUND(D1, [FOR])

Arredonda a data D1 para o valor de data mais próximo, especificado pelo formato FOR. O FOR especifica o nível de detalhes cuja data D1 será arredondado.:

[et_pb_dmb_code_snippet _builder_version=”4.0.6″ code=”LS0gY2FzbyBhIGRhdGEgYXR1YWwgc2VqYSBtYWlzIGRhIG1ldGFkZSAoID4gMTUpLCBhcnJlZG9uZGEgcGFyYSBvIAotLSB1bHRpbW8gZGlhIGRvIG1lcywgc2Vuw6NvLCBhcnJlZG9uZGEgcGFyYSBvIHByaW1laXJvIGRpYSBkbyBtZXMKClNFTEVDVCBST1VORChTWVNEQVRFLCAnREQnKSBGUk9NIERVQUwKCi0tYXJyZWRvbmRhIHBhcmEgbyBwcmltZWlybyBkaWEgZG8gcHJpbWVpcm8gbWVzIGRvIGFubwoKU0VMRUNUIFJPVU5EKENVUlJFTlRfVElNRVNUQU1QLCAnWVlZWScpIEZST00gRFVBTA==” hover_enabled=”0″]LS0gY2FzbyBhIGRhdGEgYXR1YWwgc2VqYSBtYWlzIGRhIG1ldGFkZSAoID4gMTUpLCBhcnJlZG9uZGEgcGFyYSBvIAotLSB1bHRpbW8gZGlhIGRvIG1lcywgc2Vuw6NvLCBhcnJlZG9uZGEgcGFyYSBvIHByaW1laXJvIGRpYSBkbyBtZXMKClNFTEVDVCBST1VORChTWVNEQVRFLCAnREQnKSBGUk9NIERVQUwKCi0tYXJyZWRvbmRhIHBhcmEgbyBwcmltZWlybyBkaWEgZG8gcHJpbWVpcm8gbWVzIGRvIGFubwoKU0VMRUNUIFJPVU5EKENVUlJFTlRfVElNRVNUQU1QLCAnWVlZWScpIEZST00gRFVBTA==[/et_pb_dmb_code_snippet]

– TRUNC(D1, [FOR])

Igual ao ROUND, com a diferença que de sempre se arredonda para baixo.

[et_pb_dmb_code_snippet _builder_version=”4.0.6″ code=”U0VMRUNUIFRSVU5DKFNZU0RBVEUsICdZWScpIEZST00gRFVBTDsKCi0tcmV0aXJhIGFzIGhvcmFzIGRvIHJlc3VsdGFkby4KClNFTEVDVCBUUlVOQyhTWVNEQVRFKSBGUk9NIERVQUw7″ hover_enabled=”0″]U0VMRUNUIFRSVU5DKFNZU0RBVEUsICdZWScpIEZST00gRFVBTDsKCi0tcmV0aXJhIGFzIGhvcmFzIGRvIHJlc3VsdGFkby4KClNFTEVDVCBUUlVOQyhTWVNEQVRFKSBGUk9NIERVQUw7[/et_pb_dmb_code_snippet]

– NEXT_DAY(D1, DS)

Retorna uma data válida representando a primeira ocorrência do dia DS a partir da data D1.

[et_pb_dmb_code_snippet _builder_version=”4.0.6″ code=”IC0tIENvbnN1bHRhIGV4ZWN1dGFkYSBlbSAwNS8xMS8yMDEwKFNFWFRBLUZFSVJBKQogLS0gT3Ugc2VqYSwgbyBwcm94aW1vIGRvbWluZ28sIGEgcGFydGlyIGRlIDA1LzExLCBzZXLDoSAwNy8xMQoKU0VMRUNUIE5FWFRfREFZKFNZU0RBVEUsICdET01JTkdPJykgRlJPTSBEVUFMOw==” hover_enabled=”0″]IC0tIENvbnN1bHRhIGV4ZWN1dGFkYSBlbSAwNS8xMS8yMDEwKFNFWFRBLUZFSVJBKQogLS0gT3Ugc2VqYSwgbyBwcm94aW1vIGRvbWluZ28sIGEgcGFydGlyIGRlIDA1LzExLCBzZXLDoSAwNy8xMQoKU0VMRUNUIE5FWFRfREFZKFNZU0RBVEUsICdET01JTkdPJykgRlJPTSBEVUFMOw==[/et_pb_dmb_code_snippet]

– LAST_DAY(D1)

Retorna o último dia do mês, a partir da data D1.

[et_pb_dmb_code_snippet _builder_version=”4.0.6″ code=”U0VMRUNUIExBU1RfREFZKFNZU0RBVEUpIEZST00gRFVBTDs=” hover_enabled=”0″]U0VMRUNUIExBU1RfREFZKFNZU0RBVEUpIEZST00gRFVBTDs=[/et_pb_dmb_code_snippet]

– ADD_MONTHS(D1, M)

Adiciona M meses a data D1. Para subtrair meses usando o ADD_MONTHS, basta inserir um número negativo em M.

[et_pb_dmb_code_snippet _builder_version=”4.0.6″ code=”U0VMRUNUIFRPX0NIQVIoQUREX01PTlRIUyhTWVNEQVRFLCA1KSwgJ0REL01NL1lZWVkgSEgyNDpNSTpTUycpIAogIEZST00gRFVBTDs=” hover_enabled=”0″]U0VMRUNUIFRPX0NIQVIoQUREX01PTlRIUyhTWVNEQVRFLCA1KSwgJ0REL01NL1lZWVkgSEgyNDpNSTpTUycpIAogIEZST00gRFVBTDs=[/et_pb_dmb_code_snippet]

– MONTHS_BETWEEN(D1, D2)

Mostra o número de meses entre as datas D1, e D2. Ex:

[et_pb_dmb_code_snippet _builder_version=”4.0.6″ code=”U0VMRUNUIE1PTlRIU19CRVRXRUVOKFRPX0RBVEUoJzAxLzEyLzIwMTAnLCAnZGQvTU0vWVlZWScpLCAKICAgICAgICAgICAgICAgICAgICAgIFRPX0RBVEUoJzAxLzExLzIwMTAnLCAnREQvTU0vWVlZWScpKSAKICBGUk9NIERVQUwKCiAtLSBBIGZ1bmNhbyBuw6NvIGNoZWdhIGEgYXJyZWRvbmRhciBvcyB2YWxvcmVzLiBObyBleGVtcGxvIGFiYWl4bywKIC0tIGV4aXN0ZSBtZW5vcyBkZSAxIG3DqnMgZW50cmUgYXMgZGF0YXMuCgpTRUxFQ1QgTU9OVEhTX0JFVFdFRU4oVE9fREFURSgnMDEvMTIvMjAxMCcsICdkZC9NTS9ZWVlZJyksIAogICAgICAgICAgICAgICAgICAgICAgVE9fREFURSgnMDgvMTEvMjAxMCcsICdERC9NTS9ZWVlZJykpIAogIEZST00gRFVBTDs=” hover_enabled=”0″]U0VMRUNUIE1PTlRIU19CRVRXRUVOKFRPX0RBVEUoJzAxLzEyLzIwMTAnLCAnZGQvTU0vWVlZWScpLCAKICAgICAgICAgICAgICAgICAgICAgIFRPX0RBVEUoJzAxLzExLzIwMTAnLCAnREQvTU0vWVlZWScpKSAKICBGUk9NIERVQUwKCiAtLSBBIGZ1bmNhbyBuw6NvIGNoZWdhIGEgYXJyZWRvbmRhciBvcyB2YWxvcmVzLiBObyBleGVtcGxvIGFiYWl4bywKIC0tIGV4aXN0ZSBtZW5vcyBkZSAxIG3DqnMgZW50cmUgYXMgZGF0YXMuCgpTRUxFQ1QgTU9OVEhTX0JFVFdFRU4oVE9fREFURSgnMDEvMTIvMjAxMCcsICdkZC9NTS9ZWVlZJyksIAogICAgICAgICAgICAgICAgICAgICAgVE9fREFURSgnMDgvMTEvMjAxMCcsICdERC9NTS9ZWVlZJykpIAogIEZST00gRFVBTDs=[/et_pb_dmb_code_snippet]

– NUMTOYMINTERVAL(N1, UI)

Transforma o número N1 em um valor que represente a quantidade de tempo representada por UI. UI pode ter os seguintes valores: YEAR e MONTH. O valor retornado pela função é do tipo INTERVAL YEAR TO MONTH.

[et_pb_dmb_code_snippet _builder_version=”4.0.6″ code=”LS0zMCBtZXNlcyBlcXVpdmFsZXMgYSAyIGFub3MgZSA2IG1lc2VzCgpTRUxFQ1QgTlVNVE9ZTUlOVEVSVkFMKDMwLCAnTU9OVEgnKSBGUk9NIERVQUw7″ hover_enabled=”0″]LS0zMCBtZXNlcyBlcXVpdmFsZXMgYSAyIGFub3MgZSA2IG1lc2VzCgpTRUxFQ1QgTlVNVE9ZTUlOVEVSVkFMKDMwLCAnTU9OVEgnKSBGUk9NIERVQUw7[/et_pb_dmb_code_snippet]

– NUMTODSINTERVAL(N1, UI)

Transforma o número N1 em um valor que represente a quantidade de tempo representada por UI. UI pode ter os seguintes valores: DAY, HOUR, MINUTE e SECOND. O valor retornado pela função é do tipo INTERVAL DAY TO SECOND.

[et_pb_dmb_code_snippet _builder_version=”4.0.6″ code=”U0VMRUNUIE5VTVRPRFNJTlRFUlZBTCgxMCwgJ1NFQ09ORCcpIEZST00gRFVBTDsKU0VMRUNUIE5VTVRPRFNJTlRFUlZBTCgzMCwgJ0RBWScpIEZST00gRFVBTDs=” hover_enabled=”0″]U0VMRUNUIE5VTVRPRFNJTlRFUlZBTCgxMCwgJ1NFQ09ORCcpIEZST00gRFVBTDsKU0VMRUNUIE5VTVRPRFNJTlRFUlZBTCgzMCwgJ0RBWScpIEZST00gRFVBTDs=[/et_pb_dmb_code_snippet]

– CURRENT_DATE

Retorna a data atual no tipo DATE dentro do Timezone da sessão

– CURRENT_TIMESTAMP

Retorna a data atual no tipo TIMESTAMP WITH TIMEZONE, dentro do Timezone da sessão.

– LOCALTIMESTAMP(T)

Retorna a hora local da sessão do usuário, sem o TIMEZONE, no formato TIMESTAMP. Retorna com a precisão de T milisegundos (0 a 9)

– SYSTIMESTAMP

Retorna a data do sistema operacional cujo banco de dados está instalado. O retorno é do tipo TIMESTAMP WITH TIME ZONE.

– NEW_TIME(D1, T1, T2)

Converte a data D1 do Timezone T1 para o Timezone T2.

Então é isso. Até o próximo post.