DataStage - Microsoft SQL Server

To Convert a Microsoft SQL Server datetime to DataStage type Date.

To convert a microsoft SQL server datetime to a DataStage type Timestamp

The ODBC Connector column type must be Timestamp and have a scale of 3.

DataStage requires the format 'YYYY-MM-DD HH:MM:SS' for date.

Use the convert function with format code 120 to get YYYY-MM-DD:' 00:00:00'

select CONVERT(VarChar(50), GETDATE(), 120) as '120';

Other MSSQL Convert formats of interest

select CONVERT(varchar,GETDATE(),108) 'hh:mm:ss';

select CONVERT(varchar,GETDATE(),114) 'hh:mm:ss:mmm';

select CONVERT(varchar,GETDATE(),113) 'dd Mmm yyyy hh:mm:ss:mmm';

select CONVERT(VarChar(50), GETDATE(), 104);

select CONVERT(VarChar(50), GETDATE(), 103);

select CONVERT(VarChar(50), GETDATE(), 100);

select CONVERT(VarChar(50), GETDATE(), 101);

select CONVERT(VarChar(50), GETDATE(), 102);