I want to insert datetime to database by converting the format to 'DD-MM-YYYY'

Question

I'm trying to insert a date to mysql database by converting date format to 'DD-MM-YYYY'

ALTER TABLE tblleave
  ADD COLUMN DATEPOSTED DATE NOT NULL FORMAT 'DD-MM-YYYY';

it shows a syntax error

Answer 1

This does not work. You can not format the value at INSERT or while defining your table.

You can either parse your custom format before inserting, or apply a formatting when you read back the value. This is what the FORMAT clause actually does.

You could do

SELECT DATEPOSTED FROM tblleave FORMAT 'DD-MM-YYYY';