Change date format to dd/mm/yyyy in sql

Question

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?

Answer 1

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.

Current format can be seen by : 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.

Format can be changed by : 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)

Ref: http://oracle.ittoolbox.com/groups/technical-functional/oracle-sql-l/how-to-view-current-date-format-1992815

Answer 2

SQL Server Example

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"

CONVERT(varchar(19), datecolumn, 103) 

Answer 3

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.

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');

Hope that helps :)

Answer 4

in SQL Server, according to the article here

SET DATEFORMAT YMD;