Write a SELECT statement that returns four colums from the Orders table: OrderDa

Question

Write a SELECT statement that returns four colums from the Orders table:

OrderDate: which is the original OrderDate

OrderDateConverted: which is a column that uses the CONVERT function to return the OrderDate column in this format: MM/DD/YYYY. In other words, use 2-digit months and days and a 4-digit year, and separate each date component with slashes.

AMPM: which is a column that uses the CONVERT function to return the OrderDate column with the date, and the hours and minutes on a 12-hour clock with an am/pm indicator.

OrderTime: which is a column that uses the CONVERT function to return the OrderDatecolumn with 2-digit hours, minutes, and seconds on a 24-hour clock. Use leading zeros for all date/time components.

Explanation / Answer

The query is given below:

select
orderDate,
date_format(orderDate,'%m/%d/%Y') as orderDateConverted,
date_format(orderDate,'%m/%d/%Y %r') as AMPM,
date_format(orderDate,'%m/%d/%Y %k:%i:%s') as orderTime
from Orders;

Explanation: DATE_FORMAT () function formats dates according to given mask;

Syntax: DATE_FORMAT(date, format_mask)

The used format specifiers are explained below:

%m -> Month name as a numeric value (00 to 12)

%d -> Day of the month as a numeric value (01 to 31)

%Y -> Year as a numeric, 4-digit value

%r - > Time in 12 hour AM or PM format (hh:mm:ss AM/PM)

%k -> Hour (0 to 23)

%i - > Minutes (00 to 59)

%s - > Seconds (00 to 59)

/*Hope this helps. Thanks.*/

Related Questions

Navigate

• Browse (All)
• Browse W
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.