Connor Bishop Connor Bishop - 4 months ago 16
SQL Question

Working with date formats

Ok this is really bugging me.

I am developing a web app and I need to work with dates. When a date is displayed in a view, or whenever a date is entered into a form I need the format to be dd/mm/yyyy.

What data type do I choose for my SQL database columns which contain dates. 'Date' doesn't seem to work, do I use varchar?

But If I use varchar how do I use java script to perform arithmetic with dates.

Do I do some conversions server-side?

Please advise the best practices.

Also Im using laravel if theres any useful stuff already built in.

Answer

Best practice to save the date in MySQL table as date field only. Which saves the date string in YYYY-MM-DD HH:mm:ss format.

You need to make sure the following things.

  1. Before inserting date into MySQL change the format of date string to YYYY-MM-DD HH:mm:ss.

  2. When you retrieve the date from database convert the date string from YYYY-MM-DD HH:mm:ss to your desired format.

You can use SimpleDateFormat class in Java to convert the dates format. Use format() function to format the date in desired and parse() function to get the Java date object from string.

Reference: https://docs.oracle.com/javase/7/docs/api/java/text/SimpleDateFormat.html