I want to convert from netezza code to sql code

Question

This is the Netezza code:

TO_TIMESTAMP(STG_LDA_TEA2ARRAY."TIMESTAMP", 'YYYY-MM-DD"T"HH:MI:SS'::"NVARCHAR")

How can i write this code into MSSQL?

Answer 1

to_timestamp(text,template) Converts a character string to a timestamp. >Skips multiple blank spaces unless you specify the FX prefix as the first item >in the template. select to_timestamp('31 Dec 2015 08:38:40 pm', 'DD Mon YYYY >HH:MI:SS am'); Returns: 2015-12-31 20:38:40

Note that the meridian indicator (am or pm) of the template does not need to >match that of the input string. However, it must use the same format (with or >without periods).

I had the same problem once I used the below functions to resolve it. its works for me.

Introduction to CONVERT() and TRY_CONVERT() functions
SQL Server provides the CONVERT() function that converts a value of one type to another:

CONVERT(target_type, expression [, style])
Code language: SQL (Structured Query Language) (sql)
Besides the CONVERT() function, you can also use the TRY_CONVERT() function:

TRY_CONVERT(target_type, expression [, style])
Code language: SQL (Structured Query Language) (sql)
The main difference between CONVERT() and TRY_CONVERT() is that in case of conversion fails, the CONVERT() function raises an error while the TRY_CONVERT() function returns NULL.

This example uses the CONVERT() function to convert a string in ANSI date format to a datetime:

  SELECT 
    CONVERT(DATETIME, '2019-08-15', 102) result;
Code language: SQL (Structured Query Language) (SQL)
Here is the output:

result
-----------------------
2019-08-15 00:00:00.000

(1 row affected)
Code language: SQL (Structured Query Language) (sql)
If the conversion fails, the CONVERT() function will raise an error:

SELECT 
    CONVERT(DATETIME, '2019-18-15', 102) result;
Code language: SQL (Structured Query Language) (sql)
The following is the error message:

The conversion of a varchar data type to a DateTime data type resulted in an out-of-range value.
Code language: SQL (Structured Query Language) (SQL)
The TRY_CONVERT() function, on the other hand, returns NULL instead of raising an error if the conversion fails:

SELECT 
    TRY_CONVERT(DATETIME, '2019-18-15', 102) result;
Code language: SQL (Structured Query Language) (SQL)
The output is:

result
-----------------------
NULL

(1 row affected)
Code language: SQL (Structured Query Language) (SQL)
Converting a string in ANSI/ISO and US date format to a DateTime
Both CONVERT() and TRY_CONVERT() functions can recognize ANSI/ISO and US formats with various delimiters by default so you don’t have to add the style parameter.

This example shows how to use the CONVERT() function to convert strings in ISO date format to datetime values:

SELECT CONVERT(DATETIME, '2019-09-25');
SELECT CONVERT(DATETIME, '2019/09/25');
SELECT CONVERT(DATETIME, '2019.09.25');
SELECT CONVERT(DATETIME, '2019-09-25 12:11');
SELECT CONVERT(DATETIME, '2019-09-25 12:11:09');
SELECT CONVERT(DATETIME, '2019-09-25 12:11:09.555');
SELECT CONVERT(DATETIME, '2019/09/25 12:11:09.555');
SELECT CONVERT(DATETIME, '2019.09.25 12:11:09.555');
Code language: SQL (Structured Query Language) (sql)
Note that the CONVERT() function can also convert an ISO date string without delimiters to a date value as shown in the following example:

SELECT 
    CONVERT(DATETIME, '20190731') result;
Code language: SQL (Structured Query Language) (sql)
Here is the output:

result
-----------------------
2019-07-31 00:00:00.000

(1 row affected)
Code language: SQL (Structured Query Language) (sql)
The CONVERT() and TRY_CONVERT() functions can convert United States datetime format (month, day, year and time) by default, therefore, you don’t need to specify style 101:

SELECT TRY_CONVERT( DATETIME, '12-31-2019');
SELECT TRY_CONVERT( DATETIME, '12/31/2019');
SELECT TRY_CONVERT( DATETIME, '12.31.2019');
SELECT TRY_CONVERT( DATETIME, '12-31-2019 12:15');
SELECT TRY_CONVERT( DATETIME, '12/31/2019 12:15:10');
SELECT TRY_CONVERT( DATETIME, '12.31.2019 12:15:10.333');