Solved

How to find\verfiy current Date format.

Posted on 2013-12-16
4
400 Views
Last Modified: 2014-04-11
Hello,
      
I am looking to simply find out what is the current Date format that I have in m MYSql Database. This should be easy but I am not finding the command or function to do this. I simply want to query for date format and have it return what it is currently set at, basically; DD:MM:YY or YYYY:MM:DD or whatever the format currently is set at for a particular DB.

Thank You
0
Comment
Question by:Charlie_Melega
4 Comments
 
LVL 11

Expert Comment

by:John_Vidmar
ID: 39721463
This command will show what you want, and more:
SHOW VARIABLES LIKE '%format%'

Open in new window

I received the following output:
VARIABLE_NAME			VALUE
-------------			-----
binlog_format			STATEMENT 
date_format			%Y-%m-%d 
datetime_format			%Y-%m-%d %H:%i:%s 
default_week_format		0 
innodb_file_format		Antelope 
innodb_file_format_check	ON 
innodb_file_format_max		Antelope 
time_format			%H:%i:%s 

Open in new window

0
 
LVL 33

Expert Comment

by:snoyes_jw
ID: 39721795
MySQL only uses the YYYY-MM-DD date format. There is no way to set the format for a date field to anything else. You can store dates in other formats in a string type field, but then it is just a string and not a date type.

The date_format and datetime_format system variables are deprecated as of 5.6.7 and will be removed in a later version.

http://dev.mysql.com/doc/refman/5.6/en/server-system-variables.html#sysvar_date_format
0
 
LVL 5

Expert Comment

by:rk_india1
ID: 39722461
The Query given below is used to return  the current date as Date(current_timestamp).

mysql> SELECT DATE(CURRENT_TIMESTAMP);

Output of the program

+-------------------------+
 | DATE(CURRENT_TIMESTAMP) |
 +-------------------------+
 | 2008-12-18 |
 +-------------------------+
 1 row in set (0.00 sec)

Query:-

The  Query CURRENT DATE ( ) is used to return  the current date

mysql> SELECT CURRENT_DATE();

Output of the program

+----------------+
 | CURRENT_DATE() |
 +----------------+
 | 2008-12-18 |
 +----------------+
 1 row in set (0.00 sec)

Query:-

The Query below returns the current database system timestamp as a date time value without any change in timezone.

mysql> SELECT CURRENT_TIMESTAMP;

Output of the program

+---------------------+
 | CURRENT_TIMESTAMP |
 +---------------------+
 | 2008-12-18 12:50:54 |
 +---------------------+
 1 row in set (0.00 sec)

Query:-

The Query below returns  you the the current date and time in the session time zone in a value of datatype .

mysql> SELECT LOCALTIMESTAMP;

Output of the program

+---------------------+
 | LOCALTIMESTAMP |
 +---------------------+
 | 2008-12-18 12:51:53 |
 +---------------------+
 1 row in set (0.00 sec)

Query :

The Query below return the current string format into a date format.

mysql> SELECT STR_TO_DATE('12/03/2008','%m/%d/%Y');

Output of the program

+--------------------------------------+
 | STR_TO_DATE('12/03/2008','%m/%d/%Y') |
 +--------------------------------------+
 | 2008-12-03 |
 +--------------------------------------+
 1 row in set (0.00 sec)
0
 
LVL 108

Accepted Solution

by:
Ray Paseur earned 500 total points
ID: 39737127
Have you stored datetimes in non-datetime formats, such as might be mm/dd/yy in VARCHAR columns?  If so you might want to PHP strtotime() and date() to correct this.  It's more from a PHP than an SQL perspective, but this article might be helpful.
http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/A_201-Handling-date-and-time-in-PHP-and-MySQL.html
0

Featured Post

Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

Join & Write a Comment

Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL (http://www.experts-exchange.com/articles/201/Handling-Date-and-Time-in-PHP-and-MySQL.html) several years ago, it seemed like now was a good time to updat…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

27 Experts available now in Live!

Get 1:1 Help Now