PostgreSql 日期插入格式参数设置

发布时间：2020-12-13 17:27:09 所属栏目：百科来源：网络整理

导读：I run the following SQL on my own laptop. Some issues are occured without expectation. INSERT INTO department (dname,dnumber,mgrssn,mgrstartdate) VALUES('Administration',1001,20915,'02/29/04');INSERT INTO department (dname,mgrstartdate) VA

I run the following SQL on my own laptop. Some issues are occured without expectation.

INSERT INTO department 
(dname,dnumber,mgrssn,mgrstartdate) VALUES
('Administration',1001,20915,'02/29/04');

INSERT INTO department 
(dname,mgrstartdate) VALUES
('Finance',1007,21287,'07/06/05');


INSERT INTO employee 
(fname,lname,ssn,bdate,address,sex,salary,superssn,dno) VALUES 
('Michio','Morishima',20118,'18/07/1923','79 Macpherson St,Turner','M',52107,21286,1000);
INSERT INTO employee 
(fname,dno) VALUES 
('John','Backus',20766,'03/12/1924','25 Burns St,Yarralumla',46789,1007);

Logon the Postgres,and run

i employeeCreate.sql

Many records can not be inserted because of the datetype(MDY). Such as
Errors as below when insert the date formate like '02/29/04'. It's 'MDY' formate.

	postgres=# insert into department values ('Administration','29/02/04');
	ERROR:  date/time field value out of range: "29/02/04"
	LINE 1: ... department values ('Administration','29/02/04'...

Exuecute show lc_time; to check the reason.

	postgres=# show lc_time;
	 lc_time 
	---------
	 C
	(1 row)

It's something I not expected. I check the lc_time paramter in my collage's computer,it show as 'en_AU'.
I set lc_time to be 'en_AU'.

	set lc_time to 'en_AU'


	stgres=# show lc_time;
	 lc_time 
	---------
	 en_AU
	(1 row)

Drop all of the tables,and execute 'i employeeCreate.sql'.
Errors as below when insert the date formate like '18/07/1923'. It's 'DMY' formate.

	INSERT INTO employee 
	(fname,dno) VALUES 
	('Michio',1000);

Set the DateStyle to be DMY formate:

SET DateStyle="ISO,DMY";

Execute the following SQL successfully.
Set back the DateStyle:

SE DateStyle = default;

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!

PostgreSql 日期插入格式 参数设置

PostgreSql 日期插入格式参数设置