Switch to full style
SQL articles written by forums members
Post a reply

Formatting dates in mysql

Sun Jul 22, 2012 1:38 pm

This is a SQL query that is written in MySQL syntax. The query selects a column named "EntryDate" from a table called "Department" and applies the "date_format()" function to it. The date_format function is used to format the date value in the EntryDate column to the specified format, '%m/%d/%Y %T', which represents month, day, year and time, respectively. The "as entryDate" is an alias to the formatted date, this means the column name in the result set will be entryDate instead of the original column name.

In summary, this query retrieves the "EntryDate" column from the "Department" table, formats it to 'mm/dd/yyyy hh:mm:ss' and renames it to "entryDate". Note: it is important to check if the column exists in the table "Department" and that the table exists in the database. Also the date format should match the date format of the column EntryDate, otherwise it will return null values or errors. Using date_format in mysql function you can change the date format for dates data selected from database. For example, imagine that you have a table “Department” and you want to change the date format for one of the selected field:

Code:

SELECT date_format 
( EntryDate, '%m/%d/%Y %T' ) as entryDate FROM Department




Here Department is the table name, and EntryDate is the field name. The formatting is like this 'm/%d/%Y %T’ is used. You can change the date formats to many ones. Here are some of them:
Code:
'%W %D %M %Y %T'   Wednesday 4th June 2011 11:46:51
'%a %b %e %Y %H:%i'   Wed June 4 2011 11:46
'%m/%d/%Y %T'   06/04/2011 11:46:51
'%d/%m/%Y'   04/06/2011




Post a reply
  Related Posts  to : Formatting dates in mysql
 DYNAMIC DATES IN MYSQL     -  
 formatting string in c++     -  
 date formatting in Java     -  
 Comparing Dates in Java     -  
 find the difference between dates in asp.net     -  
 Get Difference in days between two dates as a number     -  
 Formatting a Date with date()     -  
 need help in mysql     -  
 connect to MYSQL from ASP     -  
 need help in jdbc with mysql     -