AdventureWorks Database: Add two days to each value in the OrderDate column

Last update on December 20 2024 11:56:44 (UTC/GMT +8 hours)

113. From the following table write a query in SQL to add two days to each value in the OrderDate column, to derive a new column named PromisedShipDate. Return salesorderid, orderdate, and promisedshipdate column.

salesorderid|revisionnumber|orderdate              |duedate                |shipdate               |status|onlineorderflag|purchaseordernumber|accountnumber |customerid|salespersonid|territoryid|billtoaddressid|shiptoaddressid|shipmethodid|creditcardid|creditcardapprovalcode|currencyrateid|subtotal   |taxamt    |freight  |totaldue   |comment|rowguid                             |modifieddate           |
------------+--------------+-----------------------+-----------------------+-----------------------+------+---------------+-------------------+--------------+----------+-------------+-----------+---------------+---------------+------------+------------+----------------------+--------------+-----------+----------+---------+-----------+-------+------------------------------------+-----------------------+
       43659|             8|2011-05-31 00:00:00.000|2011-06-12 00:00:00.000|2011-06-07 00:00:00.000|     5|false          |PO522145787        |10-4020-000676|     29825|          279|          5|            985|            985|           5|       16281|105041Vi84182         |              | 20565.6206| 1971.5149| 616.0984| 23153.2339|       |79b65321-39ca-4115-9cba-8fe0903e12e6|2011-06-07 00:00:00.000|
       43660|             8|2011-05-31 00:00:00.000|2011-06-12 00:00:00.000|2011-06-07 00:00:00.000|     5|false          |PO18850127500      |10-4020-000117|     29672|          279|          5|            921|            921|           5|        5618|115213Vi29411         |              |  1294.2529|  124.2483|  38.8276|  1457.3288|       |738dc42d-d03b-48a1-9822-f95a67ea7389|2011-06-07 00:00:00.000|
       43661|             8|2011-05-31 00:00:00.000|2011-06-12 00:00:00.000|2011-06-07 00:00:00.000|     5|false          |PO18473189620      |10-4020-000442|     29734|          282|          6|            517|            517|           5|        1346|85274Vi6854           |             4| 32726.4786| 3153.7696|  985.553| 36865.8012|       |d91b9131-18a4-4a11-bc3a-90b6f53e9d74|2011-06-07 00:00:00.000|
       43662|             8|2011-05-31 00:00:00.000|2011-06-12 00:00:00.000|2011-06-07 00:00:00.000|     5|false          |PO18444174044      |10-4020-000227|     29994|          282|          6|            482|            482|           5|       10456|125295Vi53935         |             4| 28832.5289| 2775.1646| 867.2389| 32474.9324|       |4a1ecfc0-cc3a-4740-b028-1c50bb48711c|2011-06-07 00:00:00.000|
       43663|             8|2011-05-31 00:00:00.000|2011-06-12 00:00:00.000|2011-06-07 00:00:00.000|     5|false          |PO18009186470      |10-4020-000510|     29565|          276|          4|           1073|           1073|           5|        4322|45303Vi22691          |              |   419.4589|   40.2681|  12.5838|   472.3108|       |9b1e7a40-6ae0-4ad3-811c-a64951857c4b|2011-06-07 00:00:00.000|
       43664|             8|2011-05-31 00:00:00.000|2011-06-12 00:00:00.000|2011-06-07 00:00:00.000|     5|false          |PO16617121983      |10-4020-000397|     29898|          280|          1|            876|            876|           5|         806|95555Vi4081           |              | 24432.6088| 2344.9921|   732.81| 27510.4109|       |22a8a5da-8c22-42ad-9241-839489b6ef0d|2011-06-07 00:00:00.000|
       43665|             8|2011-05-31 00:00:00.000|2011-06-12 00:00:00.000|2011-06-07 00:00:00.000|     5|false          |PO16588191572      |10-4020-000146|     29580|          283|          1|            849|            849|           5|       15232|35568Vi78804          |              | 14352.7713| 1375.9427| 429.9821| 16158.6961|       |5602c304-853c-43d7-9e79-76e320d476cf|2011-06-07 00:00:00.000|
	   -- more --

Click to view Full table

Sample Solution:

-- Selecting specific columns from the SalesOrderHeader table
SELECT 
    -- Selecting the SalesOrderID column
    SalesOrderID,  

    -- Selecting the OrderDate column
    OrderDate,   

    -- Adding an interval of 2 days to the OrderDate and aliasing it as PromisedShipDate
    OrderDate + INTERVAL '2 day' AS PromisedShipDate
FROM 
    Sales.SalesOrderHeader;

Explanation:

• This SQL code operates on a table named Sales.SalesOrderHeader.
• It retrieves data related to sales orders.
• The retrieved data includes the SalesOrderID, OrderDate, and a calculated column named PromisedShipDate.
• In the PromisedShipDate column, 2 days are added to the OrderDate using the INTERVAL keyword and the value '2 day'. This syntax is specific to databases like PostgreSQL, where INTERVAL can be used to perform date arithmetic.
• The result set will contain the original OrderDate values along with the corresponding PromisedShipDate values, which are two days later.

Sample Output:

salesorderid|orderdate              |promisedshipdate       |
------------+-----------------------+-----------------------+
       43659|2011-05-31 00:00:00.000|2011-06-02 00:00:00.000|
       43660|2011-05-31 00:00:00.000|2011-06-02 00:00:00.000|
       43661|2011-05-31 00:00:00.000|2011-06-02 00:00:00.000|
       43662|2011-05-31 00:00:00.000|2011-06-02 00:00:00.000|
       43663|2011-05-31 00:00:00.000|2011-06-02 00:00:00.000|
       43664|2011-05-31 00:00:00.000|2011-06-02 00:00:00.000|
       43665|2011-05-31 00:00:00.000|2011-06-02 00:00:00.000|
       43666|2011-05-31 00:00:00.000|2011-06-02 00:00:00.000|
       43667|2011-05-31 00:00:00.000|2011-06-02 00:00:00.000|
       43668|2011-05-31 00:00:00.000|2011-06-02 00:00:00.000|
       43669|2011-05-31 00:00:00.000|2011-06-02 00:00:00.000|
...	

SQL AdventureWorks Editor:

Practice Online

Contribute your code and comments through Disqus.

Previous: Obtain the salary percentile of each employee for a department.
Next: Get a newdate by adding two days with current date.