Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    Dec 2011
    Posts
    29

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

    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

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    You should post this on the SQL Server forum.

    Andy

  3. #3
    Join Date
    Dec 2011
    Posts
    29
    Quote Originally Posted by ARWinner View Post
    You should post this on the SQL Server forum.

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

  4. #4
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    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.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  5. #5
    Join Date
    Dec 2011
    Posts
    29

    Smile

    Quote Originally Posted by stolze View Post
    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

  6. #6
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Quote Originally Posted by Mr.Bean View Post
    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.)
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  7. #7
    Join Date
    Dec 2011
    Posts
    29
    Quote Originally Posted by Peter.Vanroose View Post
    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?

  8. #8
    Join Date
    Dec 2011
    Posts
    27
    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

  9. #9
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Code:
    SELECT * FROM
      OPENQUERY(DB2Server,
                'select CHAR(DB2DateColumn, ISO)||'' 00:00:00''
                 from DB2Schema.Table')
    Quote Originally Posted by Mr.Bean View Post
    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')
    Last edited by Peter.Vanroose; 12-17-11 at 08:15.
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  10. #10
    Join Date
    Jan 2009
    Location
    Zoetermeer, Holland
    Posts
    746
    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
    ;

  11. #11
    Join Date
    Dec 2011
    Posts
    29
    Quote Originally Posted by manuavaran View Post
    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.

  12. #12
    Join Date
    Dec 2011
    Posts
    29
    Quote Originally Posted by Peter.Vanroose View Post
    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.

  13. #13
    Join Date
    Dec 2011
    Posts
    29
    Quote Originally Posted by dr_te_z View Post
    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 !

  14. #14
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Quote Originally Posted by Mr.Bean View Post
    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')
    Last edited by Peter.Vanroose; 12-28-11 at 17:16.
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  15. #15
    Join Date
    Dec 2011
    Posts
    27
    Quote Originally Posted by Mr.Bean View Post
    Hey Manuavaran,

    It says invalid object names for all the above queries.

    i checked again, but i could not find any problem, could you please more specific, am new to db2

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •