SQL 在sql中将日期格式更改为dd/mm/yyyy
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/20741750/
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
Change date format to dd/mm/yyyy in sql
提问by user3051827
I have a table called users and In sql the format of date is yyyy-mm-dd there fore when I try to enter data from my website in dd/mm/yyyy format it just enters 0000--00-00 How do I change the format it sql?
我有一个名为 users 的表，在 sql 中，日期格式为 yyyy-mm-dd，因此当我尝试以 dd/mm/yyyy 格式从我的网站输入数据时，它只输入 0000--00-00 我如何更改sql的格式呢？
回答by Useless_Wizard
SQL Server Example
SQL Server 示例
Link below has an easy explanation and there's an example if it helps
SELECT convert(varchar, getdate(), 103) – dd/mm/yyyy
Or try putting your column name in place of "datecolumn"
或者尝试将您的列名代替“datecolumn”
CONVERT(varchar(19), datecolumn, 103)
回答by Sunil Sandhu
If data is being saved but is showing up in your database as 0000-00-00 that'll usually be because you have the date field set to default at null.
如果数据正在保存但在您的数据库中显示为 0000-00-00，这通常是因为您将日期字段设置为默认值为空。
Same thing happened to me. Once you turn that off it'll save the data as normal.
同样的事情发生在我身上。一旦你关闭它，它就会像往常一样保存数据。
Worth also bearing in mind that you may need to ensure your code is being formatted properly when it reaches the database (if you happen to be posting data from a browser to the database. This will usually help to sort it:
还值得记住的是，您可能需要确保您的代码在到达数据库时格式正确（如果您碰巧将数据从浏览器发布到数据库。这通常有助于对其进行排序：
$dateForDB = DateTime::createFromFormat('d-m-Y', $_POST['whateverYourDateVariableIsCalled']); $dateForDB = $dateForDB->format('Y-m-d');
$dateForDB = DateTime::createFromFormat('dm-Y', $_POST['whateverYourDateVariableIsCalled']); $dateForDB = $dateForDB->format('Ym-d');
Hope that helps :)
希望有帮助:)
回答by supernova
No Idea what data getting entered as 0000-00-00 and where zeros coming from , but this may help: Basically , Data in a Date column in Oracle can be stored in any user defined format or kept as default. It all depends on NLS parameter.
不知道输入什么数据为 0000-00-00 以及零来自哪里，但这可能会有所帮助：基本上，Oracle 中日期列中的数据可以以任何用户定义的格式存储或保留为默认值。这一切都取决于 NLS 参数。
Current format can be seen by : SELECT SYSDATE FROM DUAL;
当前格式可以通过以下方式查看：SELECT SYSDATE FROM DUAL;
If you try to insert a record and insert statement is NOT in THIS format then it will give : ORA-01843 : not a valid month error. So first change the database date format before insert statements ( I am assuming you have bulk load of insert statements) and then execute insert script.
如果您尝试插入记录并且插入语句不是这种格式，那么它会给出：ORA-01843：无效月份错误。因此，首先在插入语句之前更改数据库日期格式（我假设您有大量插入语句），然后执行插入脚本。
Format can be changed by : ALTER SESSION SET nls_date_format = 'mm/dd/yyyy hh24:mi:ss';
可以通过以下方式更改格式：ALTER SESSION SET nls_date_format = 'mm/dd/yyyy hh24:mi:ss';
Also You can Change NLS settings from SQL Developer GUI , (Tools > preference> database > NLS)
您也可以从 SQL Developer GUI 更改 NLS 设置，（工具 > 首选项 > 数据库 > NLS）
参考：http: //oracle.ittoolbox.com/groups/technical-functional/oracle-sql-l/how-to-view-current-date-format-1992815
