Solved

How to find\verfiy current Date format.

Posted on 2013-12-16
4
403 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 109

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

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

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

Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo‚Ķ
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

860 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