MySQL /* Query 1. (Chapter 8, 10 points) Write a SELECT statement that returns f

Question

MySQL

/* Query 1. (Chapter 8, 10 points)
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 OrderDate
column with 2-digit hours, minutes, and seconds on a 24-hour clock. Use leading zeros
for all date/time components.

Hint: https://docs.microsoft.com/en-us/sql/t-sql/functions/cast-and-convert-transact-sql
*/

Object Explorer ConnectY& EE dbo·Addresses Columns o AddresslD (PK, int, not null) o CustomerlD (FK, int, null) Line! (varchar(60), not null) E Line2 (varchar(60), null) City (varchar(40), not null) State (varchar(2), not null) ZipCode (varchar(10), not null) Phone (varchar(12), not null) Disabled (int, not null) Keys Constraints Triggers Indexes " Statistics E dbo·Administrators Columns o AdminlD (PK, int, not null) E EmailAddress (varchar(255), not null) Password (varchar(255), not null) | FirstName (varchar(255), not null) E LastName (varchar(255), not null) Keys Constraints Triggers Indexes " Statistics E dbo·Categories Columns O CategorylD (PK, int, not null) CategoryName (varchar(255), not null) Keys Constraints Triggers Indexes Statistics dbo·Customers Columns CustomerlD (PK, int, not null) EmailAddress (varchar(255), not null) Password (varchar(60), not null) FirstName (varchar(60), not null) | E LastName (varchar(60), not null) ShippingAddressID (int, null) BillingAddressID (int, null)

Explanation / Answer

Below is the SQL Query that returns the four columns from the Order Table.

=======================================================================

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;

===================================================================================

DATE_FORMAT function is used to convert the date field in various formats.

Syntax : DATE_FORMAT(DateField, FormatPattern);

Format Pattern can have various format specifiers like :

%m - Display month in numeric format (0-12)

%d - Display day of the month in numeric format(00-31)

%Y - Displays year in numeric four digits

Example : Date_Format('1999-06-18','%m/%d/%Y') - Displayed as  06/18/1999

%k - Displays hours (0-23)

%i - Displays minutes in numeric (00-59)

%s - Displays seconds in numeric (00-59)

%r - Display Time, 12-hour (hh:mm:ss followed by AM or PM)

Example :

Date_Format('1999-06-18','%m/%d/%Y %r') - Displayed as  06/18/1999 12:00:00 AM

Date_Format('1999-06-18','%m/%d/%Y %k:%i:%s') - Displayed as  06/18/1999 0:00:00

Related Questions

Navigate

• Browse (All)
• Browse M
• Subjects

---

---

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