postgresql 字符串转date 如下 201610101010 12位varchar 转换为时间类型

第三十一章 SQL函数 CONVERT

将给定表达式转换为指定数据类型的函数。

CONVERT(datatype,expression[,format-code])

{fn CONVERT(expression,datatype)}

参数

expression - 要转换的表达式。

datatype - 要将表达式转换为的数据类型。

format - 可选-指定日期和时间格式的整数代码，用于在日期/时间/时间戳数据类型和字符数据类型之间进行转换。 此参数仅用于通用标量语法形式。

描述

这里描述了CONVERT函数的两种不同实现。 两者都将一种数据类型中的表达式转换为另一种数据类型中的相应值。 两者都执行日期和时间转换。

注意:这两个CONVERT实现中的参数以不同的顺序表示。 第一个是与MS SQL Server兼容的通用 IRIS标量函数，它接受三个参数。 第二个是带有两个参数的 ODBC标量函数。 下面的文本将分别处理这两种形式的CONVERT。

CONVERT(datatype,expression)支持流数据的转换。 例如，可以将字符流字段的内容转换为数据类型为VARCHAR的字符串。

{fn CONVERT(expression,datatype)}不支持流数据的转换; 指定要表达的流字段将导致SQLCODE -37错误。

为两个版本的CONVERT指定一个无效值将导致SQLCODE -141。

如果表达式没有定义的数据类型(例如ObjectScript提供的主机变量)，则其数据类型默认为字符串数据类型。

CONVERT(datatype,expression,format-code)

可以通过执行VARCHAR-to-VARCHAR转换来截断字符串，指定输出字符串长度小于表达式字符串长度。

在使用CONVERT(或CAST)时，如果字符数据类型(如CHAR或VARCHAR)没有指定长度，则默认的最大长度为30个字符。 如果二进制数据类型(如binary或VARBINARY)没有指定长度，则默认的最大长度为30个字符。 否则，这些没有指定长度的数据类型将被映射到一个1个字符的MAXLEN，如data types表所示。

可以执行BIT数据类型转换。 允许的值为1、0或NULL。 如果指定任何其他值，IRIS将发出SQLCODE -141错误。 在下面的嵌入式SQL示例中，两者都是一个NULL的BIT转换:

ClassMethod Convert()

{

s a=""

&sql(

SELECT CONVERT(BIT,:a),

CONVERT(BIT,NULL)

INTO :x,:y)

w !,"SQLCODE=",SQLCODE

w !,"the host variable is:",x

w !,"the NULL keyword is:",y

}

可选的format-code参数指定日期、datetime或时间格式。 该格式既可用于定义从日期/时间/时间戳数据类型转换为字符串时的输出，也可用于定义从字符串转换为日期/时间/时间戳数据类型时的输入。 支持以下格式代码; 输出两位数年份的格式代码列在第一列; 输出四位数年或不输出年的格式列在第二列:

Two-digit year codes Four-digit year codes Format

以下是日期和时间转换的特性:

取值范围:允许的日期范围为0001-01-01 ~ 9999-12-31。

默认值:

将时间值转换为TIMESTAMP、POSIXTIME、DATETIME或SMALLDATETIME时，日期默认为1900-01-01。 注意，对于{fn CONVERT()}，日期默认为1841-01-01。

将日期值转换为TIMESTAMP、POSIXTIME、DATETIME或SMALLDATETIME时，时间默认为00:00:00。

Default Format:如果没有指定Format -code, CONVERT将尝试从指定的值确定格式。 如果不能，则默认为格式代码100。

两位数年份:从00到49的两位数年份转换为21世纪的日期(2000到2049); 从50到99的两位数年份转换为20世纪的日期(1950到1999)。

分数秒:分数秒前可以加句号()或冒号(:)。 这些符号有不同的含义:

句点是默认值，可用于所有格式代码。 句号表示标准分数; 因此，12:00:004表示十分之四秒，而12:00:00004表示千分之四秒。 分数精度的位数没有限制。

冒号只能用于以下格式代码值:9/109、13/113、14/114、130和131。 冒号表示后面的数字是千分之一秒; 因此12:00:00:4表示四万分之一秒(12:00:00004)。 冒号后面的数字限制为3位。

当指定表达式的格式无效或格式与格式代码不匹配时，将产生SQLCODE -141错误。 指定一个不存在的格式代码将返回1900-01-01 00:00:00。

{fn CONVERT(expression,datatype)}

这是ODBC标量函数。 它支持以下ODBC显式数据类型转换。 必须使用“SQL_”关键字指定这种形式的CONVERT的数据类型转换。 在下表中，有两组转换数据类型，第一组转换数据值和数据类型，第二组转换数据类型，但不转换数据值:

Source Conversion

SQL_VARCHAR是标准的ODBC表示。 在转换为SQL_VARCHAR时，日期和时间被转换为相应的ODBC表示; 数字数据类型值转换为字符串表示。 从SQL_VARCHAR转换时，该值必须是有效的ODBC Time、Timestamp或Date表示。

当将时间值转换为SQL_TIMESTAMP或SQL_POSIXTIME时，未指定的日期默认为1841-01-01。 注意，对于CONVERT()，日期默认为1900-01-01。

将date值转换为SQL_TIMESTAMP或SQL_POSIXTIME时，时间默认为00:00:00。

在这种语法形式中，小数秒前面可以加句号()或冒号(:)。 这些符号有不同的含义。 句号表示标准分数; 因此，12:00:004表示十分之四秒，而12:00:00004表示千分之四秒。 冒号表示接下来的是千分之一秒; 因此12:00:00:4表示千分之四秒。 冒号后面的数字限制为3位。

在转换为整数数据类型或SQL_DOUBLE数据类型时，数据值(包括日期和时间)将转换为数字表示。 对于SQL_DATE，这是自1841年1月1日以来的天数。 对于SQL_TIME，这是自午夜以来的秒数。 当遇到非数字字符时，输入字符串将被截断。 整数数据类型还截断十进制数字，返回数字的整数部分。

{fn CONVERT(expression,datatype)}不支持流数据的转换; 指定要表达的流字段将导致SQLCODE -37错误。

转换成任何数据类型的NULL仍然是NULL。

空字符串(")，或任何非数字字符串值转换如下:

SQL_VARCHAR和SQL_TIMESTAMP返回提供的值。

数字数据类型转换为0(零)。

SQL_DATE和SQL_TIME转换为NULL。

CONVERT 类方法

还可以使用CONVERT()方法调用执行数据类型转换，使用" SQL_"关键字指定数据类型:

$SYSTEMSQLFunctionsCONVERT(expression,convert-to-type,convert-from-type)

如下示例所示:

WRITE $SYSTEMSQLCONVERT(60945,"SQL_VARCHAR","SQL_DATE")

2007-11-11

示例

CONVERT() 示例

下面的示例使用标量语法形式的CONVERT。

下面的示例比较了使用DECIMAL和DOUBLE数据类型对小数的转换:

SELECT CONVERT(DECIMAL,-1234567890000123456789) AS DecimalVal,

CONVERT(DOUBLE,-1234567890000123456789) AS DoubleVal

下面的示例将字符流字段转换为VARCHAR文本字符串。 它还使用CHAR_LENGTH显示字符流字段的长度:

SELECT Notes,CONVERT(VARCHAR(80),Notes) AS NoteText,CHAR_LENGTH(Notes) AS TextLen

FROM SampleEmployee WHERE Notes IS NOT NULL

下面的例子展示了几种将出生日期字段(DOB)转换为格式化字符串的方法:

SELECT DOB,

CONVERT(VARCHAR(20),DOB) AS DOBDefault,

CONVERT(VARCHAR(20),DOB,100) AS DOB100,

CONVERT(VARCHAR(20),DOB,107) AS DOB107,

CONVERT(VARCHAR(20),DOB,114) AS DOB114,

CONVERT(VARCHAR(20),DOB,126) AS DOB126

FROM SamplePerson

默认格式和代码100格式是相同的。 因为DOB字段不包含时间值，所以显示时间的格式(这里包括默认值100、114和126)提供一个零值，它表示12:00AM(午夜)。 代码126格式提供了一个不包含空格的日期和时间字符串。

{fn CONVERT()} 示例

下面的示例使用了ODBC语法形式的CONVERT。

下面的嵌入式SQL示例将混合字符串转换为整数。 IRIS在第一个非数字字符处截断字符串，然后将结果数字转换为规范形式:

ClassMethod Convert1()

{

s a="007 James Bond"

&sql(SELECT {fn CONVERT(:a, SQL_INTEGER)} INTO :x)

w !,"SQLCODE=",SQLCODE

w !,"the host variable is:",x

}

DHC-APP>d ##class(PHATESTSQLCommand)Convert1()

SQLCODE=0

the host variable is:7

下面的示例将“DOB”(出生日期)列中的日期转换为SQL_TIMESTAMP数据类型。

SELECT DOB,{fn CONVERT(DOB,SQL_TIMESTAMP)} AS DOBtoTstamp

FROM SamplePerson

生成的时间戳格式为“yyyy-mm-dd hh:mm:ss”。

下面的示例将“DOB”(出生日期)列中的日期转换为SQL_INTEGER数据类型。

SELECT DOB,{fn CONVERT(DOB,SQL_INTEGER)} AS DOBtoInt

FROM SamplePerson

下面的示例将“DOB”(出生日期)列中的日期转换为SQL_VARCHAR数据类型。

SELECT DOB,{fn CONVERT(DOB,SQL_VARCHAR)} AS DOBtoVChar

FROM SamplePerson

生成的字符串格式为:yyyy-mm-dd。

单行函数：

[sql] view plaincopy

--大小写控制函数

select lower('Hello World') 转小写, upper('Hello World') 转大写 from dual;

--initcap: 首字母大写

select initcap('hello world') 首字符大写 from dual;

--字符控制函数

-- concat: 字符连接函数, 等同于 ||

select concat('Hello',' World') from dual;

--substr:求母串中的某个子串

select substr('Hello World',3) from dual;

select substr('Hello World',3,4) from dual;

--length和lengthb: 字符数和字节数

select length('China') 字符数, lengthb('China') 字节数 from dual;

--instr:在母串中，查找子串的位置

select instr('Hello World','ll') from dual;

--lpad,rpad: 左右填充,将abcd用填充到10位

select lpad('abcd',10,'') 左填充, rpad('abcd',10,'') 右填充 from dual;

--trim: 去掉字符串前后指定的字符

select trim('H' from 'Hello WorldH') from dual;

--replace:字符串替换函数

select replace('Hello Wordl','l','') from dual;

--数字函数

select round(45926,2) 四舍五入, trunc(45926,2) 截断 ,mod(1600,300) 求于 from dual;

--ROUND函数

select round(45923,0) 整数位, round(45923,-1) 十位,round(45923,-2) 百位 from dual;

--日期函数

--显示当前日期

select sysdate from dual;

--显示时间部分

select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;

--显示昨天，今天和明天，加减数字仍未日期

select sysdate-1 昨天, sysdate 今天, sysdate+1 明天 from dual;

--两个日期相减，结果为相差的天数，查询员工信息，显示员工工龄。两个日期不能相加

select empno,ename, sysdate-hiredate 天 from emp;

--查询员工信息，显示员工工龄,分别按照天，星期，月显示

select empno,ename,sysdate-hiredate 天,(sysdate-hiredate)/7 星期, (sysdate-hiredate)/30 月 from emp;

--months_between:两个日期相差的月数

select (sysdate-hiredate)/30 方式一, months_between(sysdate,hiredate) 方式二 from emp;

--add_months:在指定日期上加上若干个月

select add_months(sysdate,1) 下个月, add_months(sysdate,123) "123个月后" from dual

--last_day: 某个日期当月的最后一天

select last_day(sysdate) from dual;

--next_day:下周六

select next_day(sysdate,'星期五') from dual;

--对日期进行四舍五入

select round(sysdate,'MONTH') 月,round(sysdate,'YEAR') from dual;

--对日期进行截断

select trunc(sysdate,'MONTH') 月,trunc(sysdate,'YEAR') from dual;

--日期格式

select from emp where hiredate=to_date('1982-01-23','yyyy-mm-dd');

-- 查询当前日期：显示: 2011-09-17 15:12:15今天是星期六

select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss"今天是"day') from dual;

--查询员工信息，显示员工的编号，姓名，月薪,要求有货币代码(L)，千位符(,)，小数点(),

select empno,ename,to_char(sal,'L9,99999') from emp;

[sql] view plaincopy

--通用函数

--nvl(exp1,exp2):当exp1为空时，返回exp2

--nvl2(exp1,exp2,exp3):当exp1为空时，返回exp3；否则返回exp2

select ename,sal12+nvl2(comm,comm,0) 年收入 from emp;

--NULLIF (expr1, expr2),如果expr1=expr2,返回null;否则,返回expr1

select nullif('abc','abc') from dual;

select nullif('abc','abcaa') from dual;

--COALESCE :找到参数列表中，第一个不为空的值

select ename,comm,sal,COALESCE(comm,sal) from emp;

--给员工涨工资,根据职位涨，总裁涨1000，经理涨600 其他人员涨400

select ename,job,sal 涨前工资, case job when 'PRESIDENT' then sal+1000

when 'MANAGER' then sal+600

else sal+400

end 涨后工资

from emp;

select ename,job,sal 涨前工资, decode(job,'PRESIDENT',sal+1000,

'MANAGER',sal+600,

sal+400) 涨后工资

from emp;

(五)转换函数(Conversion Functions)

转换函数将指定字符从一种类型转换为另一种，通常这类函数遵循如下惯例：函数名称后面跟着待转换类型以及输出类型。

1、TO_CHAR() 本函数又可以分三小类，分别是

 转换字符->字符TO_CHAR(c)：将nchar,nvarchar2,clob,nclob类型转换为char类型；

例如：SELECT TO_CHAR('AABBCC') FROM DUAL;

 转换时间->字符TO_CHAR(d[,fmt])：将指定的时间(data,timestamp,timestamp with time zone)按照指定格式转换为varchar2类型；

例如：SELECT TO_CHAR(sysdate,'yyyy-mm-dd hh24:mi:ss') FROM DUAL;

 转换数值->字符TO_CHAR(n[,fmt])：将指定数值n按照指定格式fmt转换为varchar2类型并返回；

例如：SELECT TO_CHAR(-100, 'L99G999D99MI') FROM DUAL;

2、TO_DATE(c[,fmt[,nls]]) 将char,nchar,varchar2,nvarchar2转换为日期类型，如果fmt参数不为空，则按照fmt中指定格式进行转换。注意这里的fmt参数。如果ftm为'J'则表示按照公元制(Julian day)转换，c则必须为大于0并小于5373484的正整数。

例如：

SELECT TO_DATE(2454336, 'J') FROM DUAL;

SELECT TO_DATE('2007-8-23 23:25:00', 'yyyy-mm-dd hh24:mi:ss') FROM DUAL;

为什么公元制的话，c的值必须不大于5373484呢？因为Oracle的DATE类型的取值范围是公元前4712年1月1日至公元9999年12月31日。看看下面这个语句：

SELECT TO_CHAR(TO_DATE('9999-12-31','yyyy-mm-dd'),'j') FROM DUAL;

3、TO_NUMBER(c[,fmt[,nls]]) 将char,nchar,varchar2,nvarchar2型字串按照fmt中指定格式转换为数值类型并返回。

例如：SELECT TO_NUMBER('-10000', '9G999D99') FROM DUAL;

(六)其它辅助函数(Miscellaneous Single-Row Functions)

1、DECODE(exp,s1,r1,s2,r2s,r[,def]) 可以把它理解成一个增强型的if else，只不过它并不通过多行语句，而是在一个函数内实现if else的功能。

exp做为初始参数。s做为对比值，相同则返回r，如果s有多个，则持续遍历所有s，直到某个条件为真为止，否则返回默认值def(如果指定了的话)，如果没有默认值，并且前面的对比也都没有为真，则返回空。

毫无疑问，decode是个非常重要的函数，在实现行转列等功能时都会用到，需要牢记和熟练使用。

例如：select decode('a2','a1','true1','a2','true2','default') from dual;

2、GREATEST(n1,n2,n) 返回序列中的最大值

例如：SELECT GREATEST(15,5,75,8) "Greatest" FROM DUAL;

3、LEAST(n1,n2n) 返回序列中的最小值

例如：SELECT LEAST(15,5,75,8) LEAST FROM DUAL;

4、NULLIF(c1,c2)

Nullif也是个很有意思的函数。逻辑等价于：CASE WHEN c1 = c2 THEN NULL ELSE c1 END

例如：SELECT NULLIF('a','b'),NULLIF('a','a') FROM DUAL;

5、NVL(c1,c2) 逻辑等价于IF c1 is null THEN c2 ELSE c1 END。c1,c2可以是任何类型。如果两者类型不同，则oracle会自动将c2转换为c1的类型。

例如：SELECT NVL(null, '12') FROM DUAL;

6、NVL2(c1,c2,c3) 大家可能都用到nvl，但你用过nvl2吗？如果c1非空则返回c2,如果c1为空则返回c3

例如：select nvl2('a', 'b', 'c') isNull,nvl2(null, 'b', 'c') isNotNull from dual;

7、SYS_CONNECT_BY_PATH(col,c) 该函数只能应用于树状查询。返回通过c1连接的从根到节点的路径。该函数必须与connect by 子句共同使用。

多行函数

和单行函数相比，oracle提供了丰富的基于组的，多行的函数。这些函数能在select或select的having子句中使用，当用于select子串时常常都和GROUP BY一起使用。多行函数分为接收多个输入，返回一个输出。

组函数：

[sql] view plaincopy

--求员工的工资总和

select sum(sal) from emp;

--求个数

select count() from emp;

--求平均工资

select sum(sal)/count() 方式一, avg(sal) 方式二 from emp;

--关于空值：组函数会自动滤空

select count(), count(comm) from emp;

--max和min：求最高工资和最低工资

select max(sal) 最高工资,min(sal) 最低工资 from emp;

--分组数据：求各个部门的平均工资

select deptno,avg(sal) from emp group by deptno;

--group by作用于多列： 按部门，不同的工种，统计平均工资

--group by作用于多列：先按照第一列分组；如果相同，再按照第二列分组

select deptno,job,avg(sal) from emp group by deptno,job;

--：求部门的平均工资大于2000的部门

select deptno,avg(sal) from emp group by deptno having avg(sal)>2000;

--group by的增强

select deptno,job,sum(sal) from emp group by rollup(deptno,job);

--不同的deptno空两行/取消设置

break on deptno skip 2/break on null

应该用timestamp，建表SQL改动如下：

    id INT(11) PRIMARY KEY AUTO_INCREMENT,

    `name` VARCHAR(100),

    addrip VARCHAR(50),

    savetime TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,

    other VARCHAR(50)

);

SQL中关键字尽量大写，如果你用SQLyog它会自动把关键字转换为大写；

name是保留字，虽然没有影响，但是不能保证以后没有影响，如果使用关键字、保留字做字段，最好加上``符号包裹，以免出错。所有自己命名的地方，都可以使用``符号包裹；

另外，每个字段最好使用 COMMENT '内容'，给每个字段加上注释，这样以后看到的时候不会想不起来字段的含义。

以上就是关于postgresql 字符串转date 如下 201610101010 12位varchar 转换为时间类型全部的内容，包括:postgresql 字符串转date 如下 201610101010 12位varchar 转换为时间类型、oracle的数据导入到mysql中，遇到一个时间转换问题、数据库中 日期的几种表示方法等相关内容解答，如果想了解更多相关内容，可以关注我们，你们的支持是我们更新的动力！