• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 410
  • Last Modified:

How to find\verfiy current Date format.

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
Charlie_Melega
Asked:
Charlie_Melega
1 Solution
 
John_VidmarCommented:
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
 
snoyes_jwCommented:
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
 
rk_india1Commented:
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
 
Ray PaseurCommented:
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

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now