locked
Date-Time Convertion Of DB2 data RRS feed

  • Question

  • Hi All,

     I need to pull out data from DB2 AS400 depending on  A perticular date and the present  SYS date . the data in the DB2 date column is like
    20091030 how can i convert this date and also compare it with the present sys date.
    I am using OLEDB source and SQl  command.
    Tuesday, November 3, 2009 2:25 AM

Answers

  • for converting into date create a new column using derived column (assuming date column is string): (dt_dbtimestamp) (substring(date,1,4) + "/" + substring(date,5,2) + "/" + substring(date,7,2)) Now use this column for comparison
    Nitesh Rai- Please mark the post as answered if it answers your question
    Tuesday, November 3, 2009 3:31 AM
  • Hi,
    here i want to change the "nitesh" expression; as you need to check only the date part so you can use following expression.

    DB2 Date:
    (DT_DBDATE)
    (substring(date,1,4) + "/" + substring(date,5,2) + "/" + substring(date,7,2))

    SQL Server Date
    (DT_DBDATE) GETDATE()

    Now you can use the compare function.

    SQL Server Date == DB2 Date ? 1:0

    Thanks-

    Let us TRY this | Don’t forget to mark the post(s) that answered your question
    Tuesday, November 3, 2009 4:33 AM

All replies

  • for converting into date create a new column using derived column (assuming date column is string): (dt_dbtimestamp) (substring(date,1,4) + "/" + substring(date,5,2) + "/" + substring(date,7,2)) Now use this column for comparison
    Nitesh Rai- Please mark the post as answered if it answers your question
    Tuesday, November 3, 2009 3:31 AM
  • Hi,
    here i want to change the "nitesh" expression; as you need to check only the date part so you can use following expression.

    DB2 Date:
    (DT_DBDATE)
    (substring(date,1,4) + "/" + substring(date,5,2) + "/" + substring(date,7,2))

    SQL Server Date
    (DT_DBDATE) GETDATE()

    Now you can use the compare function.

    SQL Server Date == DB2 Date ? 1:0

    Thanks-

    Let us TRY this | Don’t forget to mark the post(s) that answered your question
    Tuesday, November 3, 2009 4:33 AM
  • just convert the value to '2009/10/30' using the stuff function

    stuff(stuff('20091030',5,0,'/'),8,0,'/')

    create table test(ddate datetime)

    insert into test values('2009/10/30')


    insert into test select stuff(stuff('20091030',5,0,'/'),8,0,'/')

    select * from test


    Thanks,
    chandra sekhar pathivada
    www.calsql.com
    Tuesday, November 3, 2009 5:00 AM