Mascara de datas

[victorhugomuniz]

Tenho um tabela onde existem 2 campos que contem dados dos seguintes tipos

1 - CHARGE_MANUFACTURER NVARCHAR2(60)

05 Jan 2010 22:00:00:000
21/07/2009 15:00


2 - DATE_INTERVAL_SHOT DATE

8/5/2008 16:30:00
31/3/2005


Minha tarefa é alimentar o campo 2 (DATE_INTERVAL_SHOT do tipo DATE), com o valor do campo 1 (CHARGE_MANUFACTURER do tipo NVARCHAR2(60)).

Minha dúvida é qual mascara usar para converter tanto a string '05 Jan 2010 22:00:00:000' como '21/07/2009 15:00', para uma data válida.

Podem me ajudar com esse update??

[diegolenhardt]

Selecionar tudo

select to_date('05 Jan 2010 22:00:00', 'dd mon rrrr hh24:mi:ss') from dual;

select to_date('21/07/2009 15:00', 'dd/mm/rrrr hh24:mi') from dual

veja se dá certo

[victorhugomuniz]

diego

beleza cada um com seu formato

mas teria um jeito de fazer um update (uma mascara para ambos os formatos)

tem como em um update eu alterar todos os registros ou vou ter que fazer um proc com um if (ou coisa parecida) que veja qual mascara eu devo usar? e como seria essa proc ?

[diegolenhardt]

uhmm, você na verdade quer arrumar os registros que hoje estão gravados cada qual com um tipo de mascara então??
olha, não vejo outra solução, eu acabaria fazendo com procedure mesmo,

algo mais ou menos assim

Selecionar tudo

begin
  to_date(  ) - mascara1
exception
  when others then
    null;
end;

begin
  to_date(  ) - mascara2
exception
  when others then
    null;
end;

e assim por diante, em quantas tiver ai,

talvez alguém tenha outra ideia,

=)

[JOPA]

victorhugomuniz,

Trabalha essa idéia.

Selecionar tudo

begin
   for x in (select colunaPK, coluna1, coluna2 from tabela) loop
      begin
         update tabela set coluna2 = to_date(x.coluna2, 'dd mon rrrr hh24:mi:ss') where colunaPK = x.colunaPK;
	  exception
	     when others then
		    update tabela set coluna2 = to_date(x.coluna2, 'dd/mm/rrrr hh24:mi') where colunaPK = x.colunaPK;
	  end; 
   end loop;
exception
   when others then
      dbms_output.put_line('PARA TUDO: '||sqlerrm);  
end;

Ou seja, é realizada uma tentativa com um formato que trata um caso,
não tendo sucesso tenta-se com um outro formato.

Não dando certo em nenhum desses casos - PARA TUDO.

OBS: Commit e Rollback. Tal idéia veio por alto, estou meio no corre-corre mas, é só um pensamento pela olhadinha que dei aqui.

[victorhugomuniz]

diego

Selecionar tudo

select to_date('05 Jan 2010 22:00:00', 'dd mon rrrr hh24:mi:ss') from dual; 

o meu dado está assim '05 Jan 2010 22:00:00:000' e essa máscara ta sem os :000 do final

você saberia me dizer a máscara para esse :000 ou trato a string antes de converter para date
como você faria?

[victorhugomuniz]

Selecionar tudo

select substr('28 Feb 2007 22:30:00:000',1,instr('28 Feb 2007 22:30:00:000',':000',1)-1)
       --,to_date(substr('28 Feb 2007 22:30:00:000',1,instr('28 Feb 2007 22:30:00:000',':000',1)-1), 'dd mon rrrr hh24:mi:ss')
from dual

se você descomentar o segundo campo da

Selecionar tudo

ORA-01843: not a valid month

como fazer para tratar essa meleca de :000 ?

[diegolenhardt]

deixa eu dar uma pesquisada, parece ser SysTimeStamp isso, e não mais Date,

[diegolenhardt]

Cara, tenta ai preciso dar uma saida,

Formatting of the TIMESTAMP datatype

Formatting of the new TIMESTAMP datatype is the same as formatting the DATE datatype. Beware while the TO_CHAR function works with both datatypes, the TRUNC function will not work with a datatype of TIMESTAMP. This is a clear indication that the use of TIMESTAMP datatype should explicitly be used for date and times where a difference in time is of utmost importance, such that Oracle won't even let you compare like values. If you wanted to show the fractional seconds within a TIMESTAMP datatype, look at the 'FF3' to only showing 3 place holders for the fractional seconds.

Formatting of the TIMESTAMP datatype:

Selecionar tudo

    SELECT TO_CHAR(time1,'MM/DD/YYYY HH24:MI:SS') "Date"
      FROM date_table;

    Date
    -------------------
    12/17/1980 00:00:00

Formatting of the TIMESTAMP datatype with fractional seconds:

Selecionar tudo

    SELECT TO_CHAR(time1,'MM/DD/YYYY HH24:MI:SS:FF3') "Date"
      FROM date_table;

    Date
    -----------------------------
    12/17/1980 00:00:00:000[/quote]

fracao de segundo, só que isso ai acho que não vai entrar no seu campo date está

é TimeStamp mesmo.. =)

[diegolenhardt]

tenta assim

Selecionar tudo

select substr('28 Feb 2007 22:30:00:000',1,instr('28 Feb 2007 22:30:00:000',':000',1)-1)
       ,to_date(substr('28 Feb 2007 22:30:00:000',1,length('28 Feb 2007 22:30:00:000') -4), 'dd mon rrrr hh24:mi:ss')
from dual

[diegolenhardt]

do jeito que eu passei só com o to_date da impressao que cortou a hora,

mas é só a saida,

veja

Selecionar tudo

select substr('28 Feb 2007 22:30:00:000',1,instr('28 Feb 2007 22:30:00:000',':000',1)-1)
       ,
       to_char(to_date(substr('28 Feb 2007 22:30:00:000',1,length('28 Feb 2007 22:30:00:000') -4), 'dd mon rrrr hh24:mi:ss'), 'dd mon rrrr hh24:mi:ss')
from dual ,

está lá, certinho...
=)

[victorhugomuniz]

caraculis...

Selecionar tudo

--esse funciona
select substr('05 Jan 2010 22:00:00:000',1,length('28 Feb 2007 22:30:00:000') -4)
       ,to_date(substr('05 Jan 2010 22:00:00:000',1,length('28 Feb 2007 22:30:00:000') -4), 'dd mon rrrr hh24:mi:ss')
from dual

--esse da mês invalido
select substr('28 Feb 2007 22:30:00:000',1,length('28 Feb 2007 22:30:00:000') -4)
       ,to_date(substr('28 Feb 2007 22:30:00:000',1,length('28 Feb 2007 22:30:00:000') -4), 'dd mon rrrr hh24:mi:ss')
from dual

dorme com esse barulho.. chupa essa manga..

28 de fevereiro de 2007
porque não funciona isso??????

[diegolenhardt]

Cara, se eu te fala que aqui no meu banco rodou, =P

uiahuiahua

Selecionar tudo

SQL> select substr('28 Feb 2007 22:30:00:000',1,length('28 Feb 2007 22:30:00:000') -4)
       ,to_date(substr('28 Feb 2007 22:30:00:000',1,length('28 Feb 2007 22:30:00:000') -4), 'dd mon rrrr hh24:mi:ss')
from dual   2    3  ;

SUBSTR('28FEB200722: TO_DATE(SU
-------------------- ----------
28 Feb 2007 22:30:00 28/02/2007

SQL>

que beleza...

[diegolenhardt]

o meu ta american, o teu deve ta brazilian, aí é Fev e não Feb se não me engano, algo assim eu acho

[victorhugomuniz]

e isso mesmo.. so janeiro ta funcionando.. rsrsrs

January = Janeiro
Jan = Jan

[diegolenhardt]

tem um esquema no to_date que você passa NLS_LANGUAGE=AMERICAN, algo assim cara, acho que vai ter que fazer isso,

outra solucao mais gambi seria dar update com replace no banco nos meses passando pra portugues, mas não não é?
uiahauihauih

[victorhugomuniz]

Selecionar tudo

begin
  
  execute immediate 'alter session set nls_language = ''BRAZILIAN PORTUGUESE'''; 

   for x in (select PERF_INTERVAL_ID, CHARGE_MANUFACTURER, DATE_INTERVAL_SHOT from tabela) loop
      begin
         update tabela set DATE_INTERVAL_SHOT = to_date(x.CHARGE_MANUFACTURER, 'dd mon rrrr hh24:mi:ss') where PERF_INTERVAL_ID = x.PERF_INTERVAL_ID and DATE_INTERVAL_SHOT is null;
     exception
        when others then
          update tabela set DATE_INTERVAL_SHOT = to_date(x.CHARGE_MANUFACTURER, 'dd/mm/rrrr hh24:mi') where PERF_INTERVAL_ID = x.PERF_INTERVAL_ID and DATE_INTERVAL_SHOT is null;
     end;
   end loop;

   execute immediate 'alter session set nls_language = ''AMERICAN'' '; 
   
   commit;  
   
exception
   when others then
      dbms_output.put_line('Máscara não encontrada: '||sqlerrm); 
end;

não não.. update translate não.. rsrsrsrsrs

aproveitando a ideia do jopa eu fiz isso ai em cima..
concorda comigo no codigo?

so queria saber se ao encontrar um formato não contemplado ele ira parar a execução ou so vai executar o comando

Selecionar tudo

dbms_output.put_line('Máscara não encontrada: '||sqlerrm); 

e continuar ?
se for para como faço para ignorar um formato não contemplado e continuar varrendo a tabela ?? retiro o ultimo bloco exceptions ??

[diegolenhardt]

poem a exception dele dentro do loop, aí não vai parar, só vai ignorar esta mascara, porem não vai parar, tem muita linha nessa tabela??
caso tenha é interessante commitar isso ai de 10 em 10 mil,

se for muitooooooo grande pode acabar com o segmento de rollback, (undotbs)

Selecionar tudo

begin
  execute immediate 'alter session set nls_language = ''BRAZILIAN PORTUGUESE''';

   for x in (select PERF_INTERVAL_ID, CHARGE_MANUFACTURER, DATE_INTERVAL_SHOT from tabela) loop
     begin
      begin
         update tabela set DATE_INTERVAL_SHOT = to_date(x.CHARGE_MANUFACTURER, 'dd mon rrrr hh24:mi:ss') where PERF_INTERVAL_ID = x.PERF_INTERVAL_ID and DATE_INTERVAL_SHOT is null;
     exception
        when others then
          update tabela set DATE_INTERVAL_SHOT = to_date(x.CHARGE_MANUFACTURER, 'dd/mm/rrrr hh24:mi') where PERF_INTERVAL_ID = x.PERF_INTERVAL_ID and DATE_INTERVAL_SHOT is null;
     end;
     exception
       when others then
         dbms_output.put_line('Máscara não encontrada: '||sqlerrm||x.DATE_INTERVAL_SHOT);
     
   end loop;

   execute immediate 'alter session set nls_language = ''AMERICAN'' ';
   
   commit; 
end; 

concatena no dbms as datas também, pra saber qualquer ta vindo, senao não adianta, não vai saber q mascara é, ou então o rowid da linha, ou a pk, pra você ver depois...

[diegolenhardt]

faltou um end antes do end loop;

Selecionar tudo

  end;
end loop;

[fsitja]

Se tiver apenas textos de um idioma deve funcionar assim, se tiver de vários aí a gambiarra fica mais grossa.

Selecionar tudo

SQL> create table tab1 (CHARGE_MANUFACTURER, DATE_INTERVAL_SHOT) as
  2  select cast('05 Jan 2010 22:00:00:000' as NVARCHAR2(60)), cast(null as date) from dual
  3  union all
  4  select cast('21/07/2009 15:00' as NVARCHAR2(60)), cast(null as date) from dual;
 
Table created
 
SQL> 
SQL> SELECT to_date(dia || mês || ano || ' ' || hora || minuto || segundo, mascara, 'NLS_DATE_LANGUAGE=ENGLISH')
  2    FROM (SELECT t.charge_manufacturer,
  3                 regexp_substr(t.charge_manufacturer, '[[:alnum:]]+', 1, 1) dia,
  4                 regexp_substr(t.charge_manufacturer, '[[:alnum:]]+', 1, 2) mês,
  5                 regexp_substr(t.charge_manufacturer, '[[:alnum:]]+', 1, 3) ano,
  6                 regexp_substr(t.charge_manufacturer, '[[:alnum:]]+', 1, 4) hora,
  7                 regexp_substr(t.charge_manufacturer, '[[:alnum:]]+', 1, 5) minuto,
  8                 regexp_substr(t.charge_manufacturer, '[[:alnum:]]+', 1, 6) segundo,
  9                 CASE WHEN regexp_substr(t.charge_manufacturer, '[[:alpha:]]+') IS NOT NULL
 10                      THEN 'DDMONYYYY HH24MISS' ELSE 'DDMMYYYY HH24MISS' END mascara
 11            FROM tab1 t);
 
TO_DATE(DIA||mês||ANO||''||HOR
------------------------------
5/1/2010 22:00:00
21/7/2009 15:00:00
 
SQL> 

[victorhugomuniz]

Francisco

não entendi muito bem sua solução mas de qualquer forma muito obrigado..


mas executando a minha proc me parece que o execute immediate não rolou pois todas as datas que estavam no formato onde era necessario a mudança da linguagem não foram inseridas.. dai tentei alterar na mao para rodar a proc mas tambem parece que não consegui

e achei um outro formato tambem.. 16/07/2007 18:38 h ..seria um formato valido de data? qual seria sua mascara?

Selecionar tudo

SQL> alter session SET NLS_LANGUAGE='BRAZILIAN PORTUGUESE';
 
Session altered
 
SQL> show parameters nls;
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
nls_calendar                         string      
nls_comp                             string      
nls_currency                         string      
nls_date_format                      string      
nls_date_language                    string      
nls_dual_currency                    string      
nls_iso_currency                     string      
nls_language                         string      AMERICAN
nls_length_semantics                 string      BYTE
nls_nchar_conv_excp                  string      FALSE
nls_numeric_characters               string      
nls_sort                             string      
nls_territory                        string      AMERICA
nls_time_format                      string      
nls_timestamp_format                 string      
nls_timestamp_tz_format              string      
nls_time_tz_format                   string      
 
SQL> select * from NLS_SESSION_PARAMETERS;
 
PARAMETER                      VALUE
------------------------------ ----------------------------------------
NLS_LANGUAGE                   BRAZILIAN PORTUGUESE
NLS_TERRITORY                  BRAZIL
NLS_CURRENCY                   Cr$
NLS_ISO_CURRENCY               BRAZIL
NLS_NUMERIC_CHARACTERS         ,.
NLS_CALENDAR                   GREGORIAN
NLS_DATE_FORMAT                DD/MM/RR
NLS_DATE_LANGUAGE              BRAZILIAN PORTUGUESE
NLS_SORT                       WEST_EUROPEAN
NLS_TIME_FORMAT                HH24:MI:SSXFF
NLS_TIMESTAMP_FORMAT           DD/MM/RR HH24:MI:SSXFF
NLS_TIME_TZ_FORMAT             HH24:MI:SSXFF TZR
NLS_TIMESTAMP_TZ_FORMAT        DD/MM/RR HH24:MI:SSXFF TZR
NLS_DUAL_CURRENCY              Cr$
NLS_COMP                       BINARY
NLS_LENGTH_SEMANTICS           BYTE
NLS_NCHAR_CONV_EXCP            FALSE
 
17 rows selected

[fsitja]

Só separei o campo string em colunas individuais para dia, mês, ano, etc e depois converti, usando como máscara o resultado do case: se for mês em caracteres a máscara é 'MON', se for em números a máscara usa 'MM'.

[fsitja]

Segue um exemplo executável de update:

Selecionar tudo

SQL> create table tab1 (PERF_INTERVAL_ID, CHARGE_MANUFACTURER, DATE_INTERVAL_SHOT) as
  2  select cast(1 as number(10)), cast('05 Jan 2010 22:00:00:000' as NVARCHAR2(60)), cast(null as date) from dual
  3  union all select 2, cast('21/07/2009 15:00' as NVARCHAR2(60)), cast(null as date) from dual
  4  union all select 3, cast('28 Feb 2007 22:30:00:000' as NVARCHAR2(60)), cast(null as date) from dual;
 
Table created
 
SQL> 
SQL> UPDATE tab1 t
  2     SET (DATE_INTERVAL_SHOT) = (
  3  SELECT to_date(dia || mês || ano || ' ' || hora || minuto || segundo, mascara, 'NLS_DATE_LANGUAGE=ENGLISH')
  4    FROM (SELECT perf_interval_id,
  5                 charge_manufacturer,
  6                 regexp_substr(charge_manufacturer, '[[:alnum:]]+', 1, 1) dia,
  7                 regexp_substr(charge_manufacturer, '[[:alnum:]]+', 1, 2) mês,
  8                 regexp_substr(charge_manufacturer, '[[:alnum:]]+', 1, 3) ano,
  9                 regexp_substr(charge_manufacturer, '[[:alnum:]]+', 1, 4) hora,
 10                 regexp_substr(charge_manufacturer, '[[:alnum:]]+', 1, 5) minuto,
 11                 regexp_substr(charge_manufacturer, '[[:alnum:]]+', 1, 6) segundo,
 12                 CASE WHEN regexp_substr(charge_manufacturer, '[[:alpha:]]+') IS NOT NULL
 13                      THEN 'DDMONYYYY HH24MISS' ELSE 'DDMMYYYY HH24MISS' END mascara
 14            FROM tab1) t2  WHERE t2.perf_interval_id = t.perf_interval_id);
 
3 rows updated
 
SQL> select * from tab1;
 
PERF_INTERVAL_ID CHARGE_MANUFACTURER        DATE_INTERVAL_SHOT
---------------- -------------------------- ------------------
               1 05 Jan 2010 22:00:00:000   5/1/2010 22:00:00
               2 21/07/2009 15:00           21/7/2009 15:00:00
               3 28 Feb 2007 22:30:00:000   28/2/2007 22:30:00
 
SQL> 

[diegolenhardt]

Não é valido, teria que ter mais um h, assim hh para retornar a hora sempre em 12, como relogio de ponteiros, saca,

14 horas é 02 horas PM,

o que acontece quando você roda o alter session ?

que erro retorna, pode ser alguma permissão para o usuario, grant..

[victorhugomuniz]

então..

resolvi da seguinte forma

1- o alter session foi erro meu mesmo de logica.. mas passar ele no to_date funcinou bem tambem.. alias maneiro isso

Selecionar tudo

select substr('07 May 2007 18:00:00:000',1,length('07 May 2007 18:00:00:000') -4)
       ,to_date(substr('07 May 2007 18:00:00:000',1,length('07 May 2007 18:00:00:000') -4), 'dd mon rrrr hh24:mi:ss', 'NLS_DATE_LANGUAGE=AMERICAN')
from dual

2 - esse formato maluco e fiz igual o outro tratei a string retirando o h e dai fecho padrão

Selecionar tudo

begin
  execute immediate 'alter session set nls_language = ''AMERICAN''';

  for x in (select PERF_INTERVAL_ID, CHARGE_MANUFACTURER, DATE_INTERVAL_SHOT
              from tabela) loop
    begin
      begin
        update tabela
           set DATE_INTERVAL_SHOT = to_date(substr(x.CHARGE_MANUFACTURER,
                                                   1,
                                                   length(x.CHARGE_MANUFACTURER) - 4),
                                            'dd mon rrrr hh24:mi:ss')
         where PERF_INTERVAL_ID = x.PERF_INTERVAL_ID
           and DATE_INTERVAL_SHOT is null;
      exception
        when others then
          update tabela
             set DATE_INTERVAL_SHOT = to_date(x.CHARGE_MANUFACTURER,
                                              'dd/mm/rrrr hh24:mi')
           where PERF_INTERVAL_ID = x.PERF_INTERVAL_ID
             and DATE_INTERVAL_SHOT is null;
      end;
    exception
      when others then
        update tabela
           set DATE_INTERVAL_SHOT = to_date(substr(x.CHARGE_MANUFACTURER,
                                                   1,
                                                   length(x.CHARGE_MANUFACTURER) - 2),
                                            'dd/mm/rrrr hh24:mi')
         where PERF_INTERVAL_ID = x.PERF_INTERVAL_ID
           and DATE_INTERVAL_SHOT is null;
    end;
  end loop;
  exception
  when others then
        dbms_output.put_line('Máscara não encontrada: ' || sqlerrm ||
        x.DATE_INTERVAL_SHOT);

  execute immediate 'alter session set nls_language = ''BRAZILIAN PORTUGUESE'' ';

  commit;
end;

UFAAAAAAA.. q sofrimento por causa de data hein.. alias lixo de dados ne.. data em varchar, 3 formatos diferentes.. sinistro

[fsitja]

Ih foi mal, só vi que você tinha postado o 3o formato quand reli o post agora... bom, só para desencargo da consciência:

Selecionar tudo

SQL> create table CD_PERF_INTERVAL_T (PERF_INTERVAL_ID, CHARGE_MANUFACTURER, DATE_INTERVAL_SHOT) as
  2    select cast(1 as number(10)), cast('05 Jan 2010 22:00:00:000' as NVARCHAR2(60)), cast(null as date) from dual
  3    union all select 2, cast('21/07/2009 15:00' as NVARCHAR2(60)), cast(null as date) from dual
  4    union all select 3, cast('28 Feb 2007 22:30:00:000' as NVARCHAR2(60)), cast(null as date) from dual
  5    union all select 4, cast('16/07/2007 18:38 h' as NVARCHAR2(60)), cast(null as date) from dual;

Table created

SQL> 
SQL> UPDATE CD_PERF_INTERVAL_T t
  2      SET (DATE_INTERVAL_SHOT) = (
  3  SELECT to_date(dia || mês || ano || ' ' || hora || minuto || segundo, mascara || mascara_seg, 'NLS_DATE_LANGUAGE=ENGLISH')
  4     FROM (SELECT perf_interval_id,
  5                  charge_manufacturer,
  6                  regexp_substr(charge_manufacturer, '[[:digit:]]+', 1, 1) dia,
  7                  regexp_substr(charge_manufacturer, '[[:alnum:]]+', 1, 2) mês,
  8                  regexp_substr(charge_manufacturer, '[[:alnum:]]+', 1, 3) ano,
  9                  regexp_substr(charge_manufacturer, '[[:alnum:]]+', 1, 4) hora,
 10                  regexp_substr(charge_manufacturer, '[[:alnum:]]+', 1, 5) minuto,
 11                  regexp_replace(regexp_substr(charge_manufacturer, '[[:alnum:]]+', 1, 6), '[^0-9]') segundo,
 12                  CASE WHEN regexp_replace(regexp_substr(charge_manufacturer, '[[:alnum:]]+', 1, 2), '[^0-9]') IS NULL
 13                       THEN 'DDMONYYYY HH24MI' ELSE 'DDMMYYYY HH24MI' END mascara,
 14                  CASE WHEN regexp_substr(charge_manufacturer, '[[:alpha:]]$') IS NULL
 15                       THEN 'SS' END mascara_seg
 16             FROM CD_PERF_INTERVAL_T) t2  WHERE t2.perf_interval_id = t.perf_interval_id);

4 rows updated

SQL> select * from CD_PERF_INTERVAL_T;

PERF_INTERVAL_ID CHARGE_MANUFACTURER       DATE_INTERVAL_SHOT
---------------- ------------------------- ------------------
               1 05 Jan 2010 22:00:00:000  05/01/2010 22:00:0
               2 21/07/2009 15:00          21/07/2009 15:00:0
               3 28 Feb 2007 22:30:00:000  28/02/2007 22:30:0
               4 16/07/2007 18:38 h        16/07/2007 18:38:0

SQL> 

[victorhugomuniz]

alguém poderia me dizer qual a mascara para este formato..

Jun 28 2004 10:00PM

isso não ta funfando.. não sei se esse formato e valido pois o PM esta colado no ultimo zero

Selecionar tudo

select  to_date('Jun 28 2004 10:00PM','MON DD YYYY HH12:MI PM')
from dual

hora diz que falta A.M. P.M e hora diz que a string ta mal formada

[victorhugomuniz]

me afobei.. rsrsrsrs

Selecionar tudo

select  to_date('Jun 28 2004 10:00AM','MON DD YYYY HH12:MIPM', 'NLS_DATE_LANGUAGE=ENGLISH'),
        to_date('Jun 28 2004 10:00PM','MON DD YYYY HH12:MIPM', 'NLS_DATE_LANGUAGE=ENGLISH')
from dual

era o mesmo problemas do mês escrito por extenso em ingles e portugues