CODEWITHSUNDEEP
mysql
sean
sean

Reputation: 35

MySQL - Date Format

A couple of questions.

In SQL Developer I could type alter session set nls_date_format='DD-MON-YY HH:24:MI:SS'; to include the time and all queries on that tab would then include this format. I could then type alter session set nls_date_format='DD-MON-YY'; to remove the time. Any easy equivalent in MySQL that could flip my date formats like this?

If it's not that simple, let me ask this: Can the format be changed on a single query line. So if i have this

select name, start_date from Users;

If this normally shows the date and time, how can i alter it to only have the results show just the date?

Upvotes: 1

Views: 2293

Answers (2)

Rei
Rei

Reputation: 6363

Any easy equivalent in MySQL that could flip my date formats like this?

Yes, there is. What you're looking for is the DATE_FORMAT() function.

Example:

select DATE_FORMAT(start_date,'%d-%m-%Y') from Users

Adjust the specifiers as needed. Common specifiers:

%d   day of month
%m   month
%Y   year
%H   hour (00..23)
%h   hour (01..12)
%p   AM or PM
%i   minutes
%s   seconds

For other specifiers, see DATE_FORMAT().

Upvotes: 1

Mureinik
Mureinik

Reputation: 311326

start_date is presumably a datetime column. You can truncate the time by converting it to a date:

SELECT name, DATE(start_date)
FROM   users

Upvotes: 0

Related Questions