How to Convert a DB2 'Date' Datatype to a SQL Server 'DateTime' Dataype?

Mr.Bean · #1 (**permalink**) 12-14-11, 14:01

Hi All,

I need to convert a column in DB2 Linked Server of Datatype 'DATE' to a column in SQL Server 2000 of Datatype 'DATATIME'.

I have tried both the Function and CastConvert Functions and neither the Function nor the Cast & Convert helped me out.

The Queries I've used are:

SELECT * FROM OPENQUERY(DB2Server, 'select {fn convert(DB2DateColumn , SQL_DATE)} from DB2Schema.Table')

SELECT * FROM OPENQUERY(DB2Server, 'select Cast(DB2DateColumn As DateTime ) from DB2Schema.Table')

SELECT * FROM OPENQUERY(DB2Server, 'select Convert(DateTime, DB2DateColumn)from DB2Schema.Table')

Can somebody help me out.

Thanks

ARWinner · #2 (**permalink**) 12-14-11, 14:49

You should post this on the SQL Server forum.

Andy

Mr.Bean · #3 (**permalink**) 12-14-11, 14:56

Quote:

Originally Posted by ARWinner

You should post this on the SQL Server forum.

Andy

Since it is related to DB2 too, I have posted it here.

stolze · #4 (**permalink**) 12-14-11, 18:42

Right. But this is a DB2-specific forum and you want to have an answer for MS SQL Server. It may be a good idea to ask the people who know that product best.

Mr.Bean · #5 (**permalink**) 12-15-11, 12:24

Quote:

Originally Posted by stolze

Right. But this is a DB2-specific forum and you want to have an answer for MS SQL Server. It may be a good idea to ask the people who know that product best.

Ok. I've asked there

Peter.Vanroose · #6 (**permalink**) 12-16-11, 09:57

Quote:

Originally Posted by Mr.Bean

SELECT * FROM OPENQUERY(DB2Server, 'select Cast(DB2DateColumn As DateTime ) from DB2Schema.Table')

What about

Code:

SELECT * FROM
  OPENQUERY(DB2Server,
            'select CHAR(DB2DateColumn, ISO)||'' 00:00:00''
             from DB2Schema.Table')

(Sorry, not tried on SQL Server, just checked the DB2 part of the story.)

Mr.Bean · #7 (**permalink**) 12-16-11, 17:37

Quote:

Originally Posted by Peter.Vanroose

What about

Code:

SELECT * FROM
  OPENQUERY(DB2Server,
            'select CHAR(DB2DateColumn, ISO)||'' 00:00:00''
             from DB2Schema.Table')

(Sorry, not tried on SQL Server, just checked the DB2 part of the story.)

Thanks but it has thrown me error. What exactly is this query tyring to do?

manuavaran · #8 (**permalink**) 12-17-11, 01:17

these are some useful queries for checking existence of
1. Function
SELECT * FROM SYSCAT.FUNCTIONS WHERE FUNCNAME = 'your_func_name' AND FUNCSCHEMA='schema_name' AND PARM_COUNT= parametercount
2. Procudure
SELECT * FROM SYSIBM.SYSPROCEDURES WHERE PROCSCHEMA='schema_name' AND PROCNAME=' Proc_name' AND PARM_COUNT= Param_Cnt
3. View
SELECT * FROM SYSIBM.SYSVIEWS WHERE CREATOR='schema_name' AND NAME='view_name'
4. Procedure Parameter
SELECT * FROM SYSIBM.SYSROUTINEPARMS WHERE ROUTINENAME=' Proc_Name ' and ROUTINESCHEMA='schema_name' AND TYPENAME=' Param_Type'AND PARMNAME= ' Param_Name '
5. Trigger
SELECT * FROM SYSIBM.SYSTRIGGERS WHERE TBCREATOR='schema_name' AND NAME=' Trig_name '
6. Index
SELECT * FROM SYSIBM.SYSINDEXES WHERE CREATOR='schema_name' AND NAME= Index_name
7. Column
SELECT * FROM SYSCAT.COLUMNS WHERE TABSCHEMA = 'schema_name' AND TABNAME='" & Tab_Name & "' AND COLNAME='" & Col_Name & "' AND TYPENAME='" & Col_Type & "' AND LENGTH=" & Col_Size
8. Primary key
SELECT * FROM SYSIBM.SYSCOLUMNS WHERE TBCREATOR = schema_name' AND TBNAME = '" & Tab_Name & "' AND KEYSEQ > 0

Peter.Vanroose · #9 (**permalink**) 12-17-11, 07:11

Code:

SELECT * FROM
  OPENQUERY(DB2Server,
            'select CHAR(DB2DateColumn, ISO)||'' 00:00:00''
             from DB2Schema.Table')

Quote:

Originally Posted by Mr.Bean

Thanks but it has thrown me error. What exactly is this query trying to do?

"SELECT DB2DateColumn FROM DB2Schema.Table"
returns one of the following three formats, depending on your system setting:
2011-12-17
17.12.2011
12/17/2011
Hence, to be sure to have the first form, I used
"SELECT CHAR(DB2DateColumn, ISO) FROM DB2Schema.Table"

Next, since I (apparently wrongly) guessed SQL Server expects a text fragment of the form
"2011-12-17 00:00:00"
as input for a "datetime" entry, I let DB2 append (concat) the last 9 characters.

So adapt where necessary to match your SQL Server locale setting; e.g., if it expects something of the form
"12/17/2011 00:00:00"
use

Code:

SELECT * FROM OPENQUERY(DB2Server,
'select CHAR(DB2DateColumn, USA)||'' 00:00:00'' from DB2Schema.Table')

dr_te_z · #10 (**permalink**) 12-19-11, 06:23

Dump db2 to CSV and import that into mssql:

Code:

select char (     substr(digits(smallint((year   (current_timestamp)))),2,4)
               || substr(digits(smallint((month  (current_timestamp)))),4,2)
               || substr(digits(smallint((day    (current_timestamp)))),4,2) || space(1)
               || substr(digits(smallint((hour   (current_timestamp)))),4,2) || ':'
               || substr(digits(smallint((minute (current_timestamp)))),4,2) || ':'
               || substr(digits(smallint((second (current_timestamp)))),4,2) || ':000'
            )  as mssql_datetime_format
from sysibm.sysdummy1
;

Mr.Bean · #11 (**permalink**) 12-27-11, 11:20

Quote:

Originally Posted by manuavaran

these are some useful queries for checking existence of
1. Function
SELECT * FROM SYSCAT.FUNCTIONS WHERE FUNCNAME = 'your_func_name' AND FUNCSCHEMA='schema_name' AND PARM_COUNT= parametercount
2. Procudure
SELECT * FROM SYSIBM.SYSPROCEDURES WHERE PROCSCHEMA='schema_name' AND PROCNAME=' Proc_name' AND PARM_COUNT= Param_Cnt
3. View
SELECT * FROM SYSIBM.SYSVIEWS WHERE CREATOR='schema_name' AND NAME='view_name'
4. Procedure Parameter
SELECT * FROM SYSIBM.SYSROUTINEPARMS WHERE ROUTINENAME=' Proc_Name ' and ROUTINESCHEMA='schema_name' AND TYPENAME=' Param_Type'AND PARMNAME= ' Param_Name '
5. Trigger
SELECT * FROM SYSIBM.SYSTRIGGERS WHERE TBCREATOR='schema_name' AND NAME=' Trig_name '
6. Index
SELECT * FROM SYSIBM.SYSINDEXES WHERE CREATOR='schema_name' AND NAME= Index_name
7. Column
SELECT * FROM SYSCAT.COLUMNS WHERE TABSCHEMA = 'schema_name' AND TABNAME='" & Tab_Name & "' AND COLNAME='" & Col_Name & "' AND TYPENAME='" & Col_Type & "' AND LENGTH=" & Col_Size
8. Primary key
SELECT * FROM SYSIBM.SYSCOLUMNS WHERE TBCREATOR = schema_name' AND TBNAME = '" & Tab_Name & "' AND KEYSEQ > 0

Hey Manuavaran,

It says invalid object names for all the above queries.

Mr.Bean · #12 (**permalink**) 12-27-11, 11:39

Quote:

Originally Posted by Peter.Vanroose

Code:

SELECT * FROM
  OPENQUERY(DB2Server,
            'select CHAR(DB2DateColumn, ISO)||'' 00:00:00''
             from DB2Schema.Table')

"SELECT DB2DateColumn FROM DB2Schema.Table"
returns one of the following three formats, depending on your system setting:
2011-12-17
17.12.2011
12/17/2011
Hence, to be sure to have the first form, I used
"SELECT CHAR(DB2DateColumn, ISO) FROM DB2Schema.Table"

Next, since I (apparently wrongly) guessed SQL Server expects a text fragment of the form
"2011-12-17 00:00:00"
as input for a "datetime" entry, I let DB2 append (concat) the last 9 characters.

So adapt where necessary to match your SQL Server locale setting; e.g., if it expects something of the form
"12/17/2011 00:00:00"
use

Code:

SELECT * FROM OPENQUERY(DB2Server,
'select CHAR(DB2DateColumn, USA)||'' 00:00:00'' from DB2Schema.Table')

Well this query seems to run fine when I run it individually. However, my query looks like this:

Code:

SELECT CustomerID, Char(DB2DateColumn, ISO) || '' 00:00:00'', CustAddress
FROM OPENQUERY(DB2Server, 'SELECT CustomerID, DB2DateColumn, CustAddress FROM DB2Schema.Table')

The above query throws me an error:
Server: Msg 174, Level 15, State 1, Line 3
The char function requires 1 arguments.

Please Help me.

Mr.Bean · #13 (**permalink**) 12-27-11, 11:41

Quote:

Originally Posted by dr_te_z

Dump db2 to CSV and import that into mssql:

Code:

select char (     substr(digits(smallint((year   (current_timestamp)))),2,4)
               || substr(digits(smallint((month  (current_timestamp)))),4,2)
               || substr(digits(smallint((day    (current_timestamp)))),4,2) || space(1)
               || substr(digits(smallint((hour   (current_timestamp)))),4,2) || ':'
               || substr(digits(smallint((minute (current_timestamp)))),4,2) || ':'
               || substr(digits(smallint((second (current_timestamp)))),4,2) || ':000'
            )  as mssql_datetime_format
from sysibm.sysdummy1
;

I've been already loading the data into the staging tables first and then loading into Reporting tables. So, loading them again to CSV should be a lengthy process I guess !

Peter.Vanroose · #14 (**permalink**) 12-28-11, 16:13

Quote:

Originally Posted by Mr.Bean

SELECT CustomerID, Char(DB2DateColumn, ISO) || '' 00:00:00'', CustAddress
FROM OPENQUERY(DB2Server, 'SELECT CustomerID, DB2DateColumn, CustAddress FROM DB2Schema.Table')

That will never work!
The whole "Char(...,ISO)" stuff has to go inside the OPENQUERY, since it's DB2 syntax. Outside you need something which is valid SQL Server syntax.

Why not use

Code:

SELECT * FROM OPENQUERY(DB2Server,
'select CustomerID, CHAR(DB2DateColumn, ISO)||'' 00:00:00'', CustAddress
 from DB2Schema.Table')

If you need a nice column name for the second column, add it just before the last comma, e.g.

Code:

SELECT * FROM OPENQUERY(DB2Server,
'select CustomerID,
        CHAR(DB2DateColumn, ISO)||'' 00:00:00'' AS DB2DateColumn,
        CustAddress
 from DB2Schema.Table')