Differences

This shows you the differences between two versions of the page.

db2-to-sql-server:char_for_datetime [August 22, 2017 2:33 am] (current)
sqlines created
Line 1: Line 1:
 +====== CHAR Function for Datetime - IBM DB2 to SQL Server Migration ======
 +In DB2, you can use the CHAR function to convert a datetime value to string using the specified format, for example:
 +
 +**DB2**:
 +
 +<code sql>
 +  CHAR(CURRENT_DATE, ISO)
 +  -- 2017-08-22
 +</code>
 +
 +In SQL Server, you can use the CONVERT function with the specified style:
 +
 +**SQL Server**:
 +
 +<code sql>
 +  CONVERT(VARCHAR, CONVERT(DATE, GETDATE()), 120)
 +  -- 2017-08-22  
 +</code>
 +
 +===== Mapping DB2 CHAR Formats to SQL Server CONVERT Styles =====
 +
 +To convert DB2 CHAR function with specified datetime format you have to map the format to the corresponding style values of CONVERT function in SQL Server:
 +
 +| | **DB2 CHAR Format** || **SQL Server CONVERT Style** | **Format** | **Example** |
 +| 1 | ISO | ISO Standard | 120 | YYYY-MM-DD | 2017-08-22 |
 +| 2 | USA | United States | 101 | MM/DD/YYYY | 08/22/2017 |
 +| 3 | EUR | Europe | 104 | DD.MM.YYYY | 22.08.2017 |
 +| 4 | JIS | Japan | 120 | YYYY-MM-DD | 2017-08-22 |
 +
 +Examples:
 +
 +| | **DB2** || **SQL Server** |
 +| 1 | CHAR(CURRENT_DATE, ISO) | Convert to YYYY-MM-DD | CONVERT(VARCHAR, CONVERT(DATE, GETDATE()), 120) |
 +| 2 | CHAR(CURRENT_DATE, USA) | Convert to MM/DD/YYYY | CONVERT(VARCHAR, CONVERT(DATE, GETDATE()), 101) |
 +| 3 | CHAR(CURRENT_DATE, EUR) | Convert DD.MM.YYYY | CONVERT(VARCHAR, CONVERT(DATE, GETDATE()), 104) |
 +| 4 | CHAR(CURRENT_DATE, JIS) | Convert to YYYY-MM-DD | CONVERT(VARCHAR, CONVERT(DATE, GETDATE()), 120) |
 +
 +===== Database and SQL Migration Tools =====
 +
 +  * [[/db2-to-sql-server|IBM DB2 to SQL Server Migration]]
 +  * [[http://www.sqlines.com/online|Online SQL Conversion Tool]]
 +
 +===== About SQLines =====
 +
 +SQLines offers services and open source tools to help you migrate databases and applications. For more information, please contact us at [[support@sqlines.com]].
 +
 +~~NOTOC~~
 +
 +