SQL 仅从oracle sql中的给定时间戳中提取日期
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16535620/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
extract date only from given timestamp in oracle sql
提问by kumarprd
The following query:
以下查询：
select cdate from rprt where cdate <= TO_CHAR(sysdate, 'YYYY/MM/DD-HH24-MI-SS-SSSSS') and ryg='R' and cnum='C002';
return: 2013/04/27-10:06:26:794as stored in the table.
返回：2013/04/27-10:06:26:794存储在表中。
I want to get the date only as : 27-04-2013and get the number of days between the resul tdate and sysdate.
我只想获取日期：27-04-2013并获取结果日期和系统日期之间的天数。
回答by Fernando.
Use the function cast() to convert from timestamp to date
使用函数 cast() 将时间戳转换为日期
select to_char(cast(sysdate as date),'DD-MM-YYYY') from dual;
For more info of function cast oracle11g http://docs.oracle.com/cd/B28359_01/server.111/b28286/functions016.htm#SQLRF51256
有关函数转换 oracle11g 的更多信息http://docs.oracle.com/cd/B28359_01/server.111/b28286/functions016.htm#SQLRF51256
回答by mvp
This is exactly what TO_DATE()is for: to convert timestamp to date.
这正是TO_DATE()用于：将时间戳转换为日期。
Just use TO_DATE(sysdate)instead of TO_CHAR(sysdate, 'YYYY/MM/DD-HH24-MI-SS-SSSSS').
只需使用TO_DATE(sysdate)代替TO_CHAR(sysdate, 'YYYY/MM/DD-HH24-MI-SS-SSSSS').
UPDATE:
更新：
Per your update, your cdatecolumn is not real DATEor TIMESTAMPtype, but VARCHAR2. It is not very good idea to use string types to keep dates. It is very inconvenient and slowto search, compare and do all other kinds of math on dates.
根据您的更新，您的cdate列不是真实的DATE或TIMESTAMP类型的，而是VARCHAR2. 使用字符串类型来保存日期并不是一个好主意。在日期上搜索、比较和进行所有其他类型的数学运算非常不方便和缓慢。
You should convert your cdateVARCHAR2field into real TIMESTAMP. Assuming there are no other users for this field except for your code, you can convert cdateto timestamp as follows:
您应该将您的cdateVARCHAR2字段转换为 real TIMESTAMP。假设除了您的代码之外，此字段没有其他用户，您可以cdate按如下方式转换为时间戳：
BEGIN TRANSACTION;
-- add new temp field tdate:
ALTER TABLE mytable ADD tdate TIMESTAMP;
-- save cdate to tdate while converting it:
UPDATE mytable SET tdate = to_date(cdate, 'YYYY-MM-DD HH24:MI:SS');
-- you may want to check contents of tdate before next step!!!
-- drop old field
ALTER TABLE mytable DROP COLUMN cdate;
-- rename tdate to cdate:
ALTER TABLE mytable RENAME COLUMN tdate TO cdate;
COMMIT;
回答by Shailesh
try this type of format:
试试这种格式：
SELECT to_char(sysdate,'dd-mm-rrrr') FROM dual
回答by AB Sikarwar
Convert Timestamp to Date as mentioned below, it will work for sure -
如下所述将时间戳转换为日期，它肯定会起作用-
select TO_DATE(TO_CHAR(TO_TIMESTAMP ('2015-04-15 18:00:22.000', 'YYYY-MM-DD HH24:MI:SS.FF'),'MM/DD/YYYY HH24:MI:SS'),'MM/DD/YYYY HH24:MI:SS') dt from dual
回答by Tarun Verma
This format worked for me, for the mentioned date format i.e. MM/DD/YYYY
这种格式对我有用，对于提到的日期格式，即 MM/DD/YYYY
SELECT to_char(query_date,'MM/DD/YYYY') as query_date
FROM QMS_INVOICE_TABLE;
回答by Frank Staheli
If you want the value from your timestamp column to come back as a date datatype, use something like this:
如果您希望时间戳列中的值作为日期数据类型返回，请使用以下内容：
select trunc(my_timestamp_column,'dd') as my_date_column from my_table;
