ORACLE-DB2-SQLSERVER-MYSQL 数据库 时间转换 查询表结构
ORACLE：
--时间转字符串
TO_CHAR(SYSDATE,'YYYYMMDDHH24MISS')
--字符串转时间
select TO_DATE('20140701125751','YYYYMMDDHH24MISS') from dual -- 2014-07-01 12:57:51
--查询table
SELECT * FROM ALL_TABLES WHERE TABLE_NAME ='{0}'
--查询column
SELECT
aa.table_name AS tableename,
aa.column_name AS colname,
NVL (bb.comments, aa.column_name) AS coldesc
FROM
(
SELECT
table_name,
column_name,
comments
FROM
all_col_comments
WHERE
table_name = '{0}'
AND owner = '{1}') bb,
(
SELECT
table_name,
column_name,
column_id
FROM
all_tab_columns
WHERE
table_name = '{0}'
AND owner = '{1}') aa
WHERE
aa.column_name = bb.column_name
ORDER BY
aa.column_id ASC
DB2:
--时间转字符串
TO_CHAR (timestamp_expression,'YYYY-MM-DD HH24:MI:SS')
TO_CHAR (timestamp_expression,'YYYYMMDDHH24MISS')
示例：
SELECT to_char ('2023-12-11 11:38:43','YYYYMMDDHH24MISS') from sysibm.dual;
select TO_CHAR(CURRENT_TIMESTAMP, 'YYYYMMDDHH24MISS') from sysibm.dual;--系统时间
输出：
20231211113843
————————————————————————————————————————————
--字符串转时间
TO_DATE (string_expression, 'YYYY-MM-DD HH24:MI:SS')
--------示例
SELECT to_date('20231206140616', 'YYYY-MM-DD HH24:MI:SS') from sysibm.dual;
输出：
2023-12-06 14:06:16
--查询table
SELECT * FROM SYSCAT.TABLES WHERE tabname ='{0}'
--查询column
SELECT
tabname AS tableename,
colname,
NVL (remarks, colname) AS coldesc
FROM
SYSCAT.COLUMNS
WHERE
tabname = '{0}'
AND tabschema ='{1}'
ORDER BY
colno ASC
SQLSERVER:
--时间转字符串
REPLACE(REPLACE(replace((convert(varchar, getdate(), 120)),'-',''),':',''),' ','')
--字符串转时间
http://www.cnblogs.com/windphoenix/archive/2013/04/26/3044784.html
PS:--------------------------------------------------
select CONVERT(datetime,SUBSTRING ('20150713121314',1,4)
+ '-' +SUBSTRING ('20150713121314',5,2)
+ '-' + SUBSTRING ('20150713121314',7,2)
+ ' ' +SUBSTRING ('20150713121314',9,2)
+ ':' +SUBSTRING ('20150713121314',11,2)
+ ':' +SUBSTRING ('20150713121314',13,2)) '20150713121314';
输出：2015-07-13 12:13:14
--------------------------------------------------
--查询table
SELECT * from sysobjects where xtype='U' and name ='{0}'
--查询column
select dbo.syscolumns.name as Colname, CONVERT(Varchar(50), Isnull(sys.extended_properties.value ,dbo.syscolumns.name)) as ColDesc from dbo.sysobjects inner join dbo.syscolumns on (dbo.syscolumns.id = dbo.sysobjects.id ) left join sys.extended_properties on (dbo.syscolumns.id = sys.extended_properties.major_id and syscolumns.colid=sys.extended_properties.minor_id) where dbo.sysobjects.xtype='U' and dbo.sysobjects.name ='{0}'
MYSQL:
---时间转字符串
select date_format(now(),'%Y%m%d%H%i%S') --20150425102256
--字符串转时间
SELECT str_to_date('20150425102222', '%Y%m%d%h%i%s'); --2015-04-25 10:22:22
--查询table
select table_name as TABLENAME,TABLE_COMMENT as TABLEDESC from information_schema.tables where table_name not LIKE 'T' ORDER BY table_name；
--查询column
SELECT * FROM information_schema.columns WHERE table_name = 'T';
SQLITE:
--时间转字符串
strftime('%Y%m%d%H%M%S','now')
--字符串转时间
http://blog.csdn.net/xmphoenix/article/details/6438854
http://www.cnblogs.com/ungshow/archive/2010/10/10/1847081.html
PS：------------------------
select datetime(substr('20150710131022',1,4)
|| '-' ||substr('20150710131022',5,2)
|| '-' || substr('20150710131022',7,2)
|| ' ' ||substr('20150710131022',9,2)
|| ':' ||substr('20150710131022',11,2)
|| ':' ||substr('20150710131022',13,2))
将 20150710131022
输出：2015-07-10 13:10:22
-------------------------
--查询table
SELECT * from sqlite_master where type='table' and name ='{0}'
--查询column
PRAGMA table_info([T1])
-----------------------------------------------------
private const string _SQL_BM_INS_TRPBM01 = "INSERT INTO TRPBM01 VALUES('{0}',TO_CHAR(SYSDATE,'YYYYMMDDHH24MISS'),' ',' ',' ','{1}','{2}','{3}','{4}',{5},' ',' ')";
private const string _SQL_BM_INS_TRPBM01_ACCESS = "INSERT INTO TRPBM01 VALUES('{0}',FORMAT(NOW(),'YYYYMMDDHHNSS'),' ',' ',' ','{1}','{2}','{3}','{4}',{5},' ',' ')";
private const string _SQL_BM_INS_TRPBM01_SQLite = "INSERT INTO TRPBM01 VALUES('{0}',strftime('%Y%m%d%H%M%S','now'),' ',' ',' ','{1}','{2}','{3}','{4}',{5},' ',' ')";
private const string _SQL_BM_INS_TRPBM01_SqlServer = "INSERT INTO TRPBM01 VALUES('{0}',REPLACE(REPLACE(replace((convert(varchar, getdate(), 120)),'-',''),':',''),' ',''),' ',' ',' ','{1}','{2}','{3}','{4}',{5},' ',' ')";
private const string _SQL_BM_INS_TRPBM01_MySql = "INSERT INTO TRPBM01 VALUES('{0}',date_format(now(),'%Y%m%d%H%i%S'),' ',' ',' ','{1}','{2}','{3}','{4}',{5},' ',' ')";
