w3resource

AdventureWorks Database: Convert order date in the 'America/Denver' and 'America/Chicago' time zone

SQL Query - AdventureWorks: Exercise-160 with Solution

160. From the following table write a query in SQL to convert order date in the 'America/Denver' time zone and also convert from 'America/Denver' time zone to 'America/Chicago' time zone.

Sample table: Sales.SalesOrderHeader 
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 SalesOrderID, OrderDate, and converting OrderDate to the timezone 'America/Denver' and 'America/Chicago'
SELECT 
    -- Selecting the SalesOrderID column from the SalesOrderHeader table
    SalesOrderID, 
    -- Selecting the OrderDate column from the SalesOrderHeader table
    OrderDate,
    -- Converting OrderDate to the timezone 'America/Denver' and renaming the result column as OrderDate_TimeZoneAMDEN
    OrderDate ::timestamp AT TIME ZONE 'America/Denver' AS OrderDate_TimeZoneAMDEN,
    -- Converting OrderDate to the timezone 'America/Denver' and then converting to the timezone 'America/Chicago', and renaming the result column as OrderDate_TimeZoneAMCHI
    OrderDate ::timestamp AT TIME ZONE 'America/Denver' AT TIME ZONE 'America/Chicago' AS OrderDate_TimeZoneAMCHI
-- From the SalesOrderHeader table
FROM 
    Sales.SalesOrderHeader;

Explanation:

  • This SQL code retrieves the SalesOrderID and OrderDate from the SalesOrderHeader table and converts the OrderDate to the timezones 'America/Denver' and 'America/Chicago'.
  • The SELECT statement specifies the columns to be included in the result set.
  • The FROM clause indicates the table from which data is being retrieved, which is the SalesOrderHeader table.
  • The OrderDate ::timestamp AT TIME ZONE 'America/Denver' expression converts the OrderDate column to the timezone 'America/Denver'.
  • The OrderDate ::timestamp AT TIME ZONE 'America/Denver' AT TIME ZONE 'America/Chicago' expression first converts the OrderDate to the timezone 'America/Denver' and then converts it to the timezone 'America/Chicago'.
  • The result set will contain the SalesOrderID, original OrderDate, OrderDate converted to the timezone 'America/Denver', and OrderDate converted to the timezone 'America/Chicago', labeled accordingly.

Sample Output:

salesorderid|orderdate              |orderdate_timezoneamden      |orderdate_timezoneamchi|
------------+-----------------------+-----------------------------+-----------------------+
       43659|2011-05-31 00:00:00.000|2011-05-31 11:30:00.000 +0530|2011-05-31 01:00:00.000|
       43660|2011-05-31 00:00:00.000|2011-05-31 11:30:00.000 +0530|2011-05-31 01:00:00.000|
       43661|2011-05-31 00:00:00.000|2011-05-31 11:30:00.000 +0530|2011-05-31 01:00:00.000|
       43662|2011-05-31 00:00:00.000|2011-05-31 11:30:00.000 +0530|2011-05-31 01:00:00.000|
       43663|2011-05-31 00:00:00.000|2011-05-31 11:30:00.000 +0530|2011-05-31 01:00:00.000|
       43664|2011-05-31 00:00:00.000|2011-05-31 11:30:00.000 +0530|2011-05-31 01:00:00.000|
       43665|2011-05-31 00:00:00.000|2011-05-31 11:30:00.000 +0530|2011-05-31 01:00:00.000|
       43666|2011-05-31 00:00:00.000|2011-05-31 11:30:00.000 +0530|2011-05-31 01:00:00.000|
       43667|2011-05-31 00:00:00.000|2011-05-31 11:30:00.000 +0530|2011-05-31 01:00:00.000|
       43668|2011-05-31 00:00:00.000|2011-05-31 11:30:00.000 +0530|2011-05-31 01:00:00.000|
...

SQL AdventureWorks Editor:

Practice Online


Contribute your code and comments through Disqus.

Previous: Convert order date in the 'America/Denver' time zone.
Next: Find the rows with green_ in the LargePhotoFileName column.


What is the difficulty level of this exercise?

Test your Programming skills with w3resource's quiz.



Become a Patron!

Follow us on Facebook and Twitter for latest update.

It will be nice if you may share this link in any developer community or anywhere else, from where other developers may find this content. Thanks.

https://w3resource.com/sql-exercises/adventureworks/sql-adventureworks-exercise-160.php