Solved

How to find\verfiy current Date format.

Posted on 2013-12-16
4
404 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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 110

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

MIM Survival Guide for Service Desk Managers

Major incidents can send mastered service desk processes into disorder. Systems and tools produce the data needed to resolve these incidents, but your challenge is getting that information to the right people fast. Check out the Survival Guide and begin bringing order to chaos.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
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…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

730 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