SQL 也许您需要不同的“日期样式”设置
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/34760758/
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
Perhaps you need a different "datestyle" setting
提问by YAM
I have a table which have a column order_dateof date type.
我有一个表，其中包含 日期类型的order_date 列。
query:
询问：
INSERT INTO uni_data_temp(sale_order_item_code,
order_date, sale_order_item_status, tracking_number, dispatch_date,
user_id) VALUES ('1000932515', cast('16/05/2015' as date), 'DISPATCHED', 'UNIPAYP1958141', '2015/05/20', '4')
when I am running this query it gives error:
当我运行这个查询时，它给出了错误：
ERROR: date/time field value out of range: "16/05/2015" SQL state: 22008 Hint: Perhaps you need a different "datestyle" setting. Character: 380
ERROR: date/time field value out of range: "16/05/2015" SQL state: 22008 Hint: Perhaps you need a different "datestyle" setting. Character: 380
then I changed the query
然后我改变了查询
INSERT INTO uni_data_temp(sale_order_item_code,
order_date, sale_order_item_status, tracking_number, dispatch_date,
user_id) VALUES ('1000932515', cast('2015/05/16' as date), 'DISPATCHED', 'UNIPAYP1958141', '2015/05/20', '4')
It works fine.
它工作正常。
but my problem is my date may be in any style (yyyy/mm/dd or dd/mm/yyyy) how i can cast it according databse ?
但我的问题是我的日期可能是任何样式（yyyy/mm/dd 或 dd/mm/yyyy）我如何根据数据库进行转换？
Any kind of date format convert into system database.
任何类型的日期格式转换为系统数据库。
Thank You
谢谢你
采纳答案by Thorsten Kettner
You are using strings for the dates and are relying on session settings to interprete the strings correctly. Use supported date literals instead so as to be independent from settings.
您使用字符串作为日期，并依赖会话设置来正确解释字符串。改用受支持的日期文字，以便独立于设置。
In PostgreSQL (and the SQL standard for that matter) DATE 'YYYY-MM-DD' is considered a date literal and is the format I would recommend. So use
在 PostgreSQL（以及与此相关的 SQL 标准）中，DATE 'YYYY-MM-DD' 被认为是日期文字并且是我推荐的格式。所以用
INSERT INTO uni_data_temp
( sale_order_item_code
, order_date
, sale_order_item_status
, tracking_number
, dispatch_date
, user_id
)
VALUES
( '1000932515'
, DATE '2015-05-16'
, 'DISPATCHED'
, 'UNIPAYP1958141'
, DATE '2015-05-20'
, 4
);
(Thanks to a_horse_with_no_name for pointing me to the correct date literal syntax in PostgreSQL.)
（感谢 a_horse_with_no_name 将我指向 PostgreSQL 中正确的日期文字语法。）
If, however, you get the dates as strings from somewhere, you need to apply the according format:
但是，如果您从某处以字符串形式获取日期，则需要应用相应的格式：
TO_DATE('06/05/2015', 'DD/MM/YYYY')
TO_DATE('05/06/2015', 'MM/DD/YYYY')
