Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

How to find\verfiy current Date format.

Posted on 2013-12-16
4
Medium Priority
?
408 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 111

Accepted Solution

by:
Ray Paseur earned 2000 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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Backups and Disaster RecoveryIn this post, we’ll look at strategies for backups and disaster recovery.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

636 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