从Select查询中提取完整的时间戳(包括日期)；甲骨文 [英] Extract a full timestamp (date included) from a Select query; Oracle

问题描述

因此，我尝试将多个数据行从一个表插入到另一个表.我已经做到了；但是，我的某些列(特别是日期列)有问题.当查询返回数据时，它缺少通常存在的日期时间部分.如果希望这样做没有意义，那么请按照以下说明使事情变得有意义.

So I am trying to insert multiples rows of data from one table to another. I have done this; however, I am having an issue with some of my columns, specifically my date columns. When the query returns data it is missing the time component of the date which is normally present. If this doesn't make sense hopefully the follow helps things to make sense.

我的原始查询

SELECT 'insert into dante2 (subcar, batch_id, silicon, temperature, sulphur, manganese, phosphorus, start_pour, end_pour, sched_cast_date) values(',    SUBCAR,',',BBP.BATCH_ID   ,',',   SILICON                 ,',',TEMPERATURE                   ,',',   SULPHUR                 ,',',   MANGANESE                  ,',',   pHOSPHORUS                  ,',''',START_POUR      ,''',''',    END_POUR,''',''',SCHED_CAST_DATE              ,''');'
FROM  bof_chem_sample bcs, bof_batch_pour bbp, bof_celox_sample bofcs 
WHERE bcs.SAMPLE_CODE= to_char('D1')
and bofcs.sample_code=bcs.sample_code
and bofcs.batch_id=bcs.batch_id
and bcs.batch_id = bbp.batch_id
and bofcs.temperature>0
AND bbp.START_POUR>=to_date('01-JAN-11')
order by bbp.start_pour

查询结果:

insert into dante2 (subcar, batch_id, silicon, temperature, sulphur, manganese, phosphorus, start_pour, end_pour, sched_cast_date) 
  values( 101,65277 ,0.6631,2525 ,0.0551,0.3366,0.043,'01-JAN-11','01-JAN-11','31-DEC-10'); 
insert into dante2 (subcar, batch_id, silicon, temperature, sulphur, manganese, phosphorus, start_pour, end_pour, sched_cast_date) 
  values( 123,65277 ,0.6631,2525 ,0.0551,0.3366,0.043,'01-JAN-11','01-JAN-11','31-DEC-10'); 
insert into dante2 (subcar, batch_id, silicon, temperature, sulphur, manganese, phosphorus, start_pour, end_pour, sched_cast_date) 
  values( 123,65278 ,0.7116,2470 ,0.0598,0.333,0.0423,'01-JAN-11','01-JAN-11','31-DEC-10'); 
insert into dante2 (subcar, batch_id, silicon, temperature, sulphur, manganese, phosphorus, start_pour, end_pour, sched_cast_date) 
  values( 116,65278 ,0.7116,2470 ,0.0598,0.333,0.0423,'01-JAN-11','01-JAN-11','31-DEC-10'); 

但是，我希望日期看起来像dd-mon-yy hh24:mi. 有人知道如何解决这个问题吗?

However, I want the dates to look like dd-mon-yy hh24:mi. Does anyone know how to fix this?

推荐答案

两个选项:

1)如果您具有alter session特权，请在运行SELECT语句之前更改NLS_DATE_FORMAT，如下所示:

1) If you have alter session privilege, change the NLS_DATE_FORMAT before running the SELECT statement as given below:

ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-RR HH24:MI';

2)如果您没有ALTER会话权限，则对每个日期字段应用转换，如下所示(下面的stmnt将其显示为START_POUR)

2) If you don't have ALTER session privilege then apply the transformation for each date field as given below (the stmnt below shows it for START_POUR)

'TO_DATE(''' || TO_CHAR(START_POUR, 'DD-MON-RR HH24:MI') || ''', ''DD-MON-RR HH24:MI'')'

这篇关于从Select查询中提取完整的时间戳(包括日期)；甲骨文的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！