TO_TIMESTAMP - Convert String to Datetime - Netezza to SQL Server Migration

In Netezza TO_TIMESTAMP function converts a string value to TIMESTAMP data type value using the specified format. In SQL Server you can use CONVERT or TRY_CONVERT function with an appropriate datetime style.

Note that TRY_CONVERT function is available since SQL Server 2012.

Netezza:

  -- Specify a datetime string and its exact format
  SELECT TO_TIMESTAMP('01/13/2023 11:37:13', 'MM/DD/YYYY HH24:MI:SS');

SQL Server:

  -- Specify a datetime string and style 101 (US format), raises an error if conversion fails
  SELECT CONVERT(DATETIME, '01/13/2023 11:37:13', 101);
 
  -- TRY_CONVERT returns NULL if conversion fails
  SELECT TRY_CONVERT(DATETIME, '01/13/2023 11:37:13', 101);

TO_TIMESTAMP Format Specifiers

Netezza TO_TIMESTAMP supports the following format specifiers (not full list):

Netezza TIMESTAMP Format Specifier
YYYY 4-digit year
YY 2-digit year
RRRR 4-digit or 2-digit year, 20th century used for years 00-49, otherwise 19th
MON Abbreviated month (Jan - Dec)
MONTH Month name (January - December)
MM Month (1 - 12)
DY Abbreviated day (Sun - Sat)
DD Day (1 - 31)
HH24 Hour (0 - 23)
HH or HH12 Hour (1 - 12)
MI Minutes (0 - 59)
SS Seconds (0 - 59)
MS Milliseconds
US Microseconds

Converting Netezza TO_TIMESTAMP to SQL Server

Unlike Netezza TO_TIMESTAMP function that allows you to build any format string using format specifiers (YYYY and MM i.e.), in SQL Server you have to use a datetime style that defines the format for the entire datetime string.

Fortunately, most applications use typical datetime formats in Netezza that can be easily mapped to a datetime format style in SQL Server.

CONVERT and TRY_CONVERT Recognize ANSI/ISO and US Formats by Default

Note that when converting a string to datetime, both CONVERT and TRY_CONVERT recognize ANSI/ISO datetime formats with various delimiters by default, so you do not need to specify a style for them.

An ANSI/ISO format is year, month, day, hour, minute, seconds, fractional seconds (YYYY-MM-DD HH24:MI:SS.FFF) where trailing parts can be omitted so you can specify YYYY-MM-DD, or YYYY-MM-DD HH24:MI etc.

SQL Server:

  -- ISO date formats with various delimiters recognized by default (year, month, day)
  SELECT CONVERT(DATETIME, '2012-06-30');
  SELECT CONVERT(DATETIME, '2012/06/30');
  SELECT CONVERT(DATETIME, '2012.06.30');
 
  SELECT CONVERT(DATETIME, '2012-06-30 11:10');
  SELECT CONVERT(DATETIME, '2012-06-30 11:10:09');
  SELECT CONVERT(DATETIME, '2012-06-30 11:10:09.333');
  SELECT CONVERT(DATETIME, '2012/06/30 11:10:09.333');
  SELECT CONVERT(DATETIME, '2012.06.30 11:10:09.333');
 
  -- ISO date without delimiters is also recognized
   SELECT CONVERT(DATETIME, '20120630');

SQL Server also recognizes United States datetime format (month, day, year and time) by default, so you do not need to specify style 101:

SQL Server:

  -- United States date formats with various delimiters recognized by default (month, day, year)
  SELECT CONVERT(DATETIME, '06-30-2012');
  SELECT CONVERT(DATETIME, '06/30/2012');
  SELECT CONVERT(DATETIME, '06.30.2012');
 
  SELECT CONVERT(DATETIME, '06-30-2012 11:10');
  SELECT CONVERT(DATETIME, '06/30/2012 11:10:09');
  SELECT CONVERT(DATETIME, '06.30.2012  11:10:09.333');

Also SQL Server recognizes the following formats by default:

SQL Server

  SELECT CONVERT(DATETIME, '17-FEB-2013');
  # 2013-02-17 00:00:00.000

Mapping Netezza TO_TIMESTAMP Formats to SQL Server CONVERT Style

You can map an Netezza TO_TIMESTAMP format to SQL Server CONVERT or TRY_CONVERT style as follows:

Netezza TO_TIMESTAMP Format SQL Server CONVERT and TRY_CONVERT Style
1 YYYY-MM-DD Default (no style specified), 101, 102, 110, 111, 20, 120, 21 and 121
2 YYYY/MM/DD Default, 101, 102, 110, 111, 20, 120, 21 and 121
3 DD/MM/YYYY 103
4 YYYY-MM-DD HH24:MI:SS Default, 101, 102, 110, 111, 20, 120, 21 and 121
5 MM/DD/YYYY HH24:MI:SS Default and 101
6 DD-MON-YYYY Default, 106 and 113

Conversion examples:

Netezza SQL Server
1 TO_TIMESTAMP('2012-07-18', 'YYYY-MM-DD') CONVERT(DATETIME, '2012-07-18')
2 TO_TIMESTAMP('2012/07/18', 'YYYY/MM/DD') CONVERT(DATETIME, '2012/07/18')
3 TO_TIMESTAMP('24/07/2022', 'DD/MM/YYYY') CONVERT(DATETIME, '24/07/2022', 103)
4 TO_TIMESTAMP('2012-07-18 13:27:18', 'YYYY-MM-DD HH24:MI:SS') CONVERT(DATETIME, '2012-07-18 13:27:18')
5 TO_TIMESTAMP('07/18/2012 13:27:18', 'MM/DD/YYYY HH24:MI:SS') CONVERT(DATETIME, '07/18/2012 13:27:18')
6 TO_TIMESTAMP('17-FEB-2013', 'DD-MON-YYYY') CONVERT(DATETIME, '17-FEB-2013')

For more information, see Netezza to SQL Server Migration