convert DB2 datetimes to SQL Server datetimes

• bagofbirds-767347

  Ten Centuries

  Points: 1110

  More actions

  March 4, 2014 at 3:51 pm

  #148518

  Hi guys, It seems there would be a simple solution to this problem but i've looked all over the place and can't find the fix.

  I am trying to convert a column of DB2 datetimes (like: 2012-02-14-17.25.16.016164) to SQL Server datetimes. i know i could go through it with charindex/replace/left/right type functions but that is a bit messy.

  I'm hoping someone knows an easier way of doing this? or perhaps has written a function i can use?

  Much thanks in advance.

  edit: forgot to mention, all the data is loaded into a sql server (2005) table, so i am looking to use t-sql to do this.

• bagofbirds-767347

  Ten Centuries

  Points: 1110

  More actions

  March 6, 2014 at 1:27 pm

  #1694875

  so, if anyone else runs into this situation, i wrote a function to do this. it converts the db2 date, time, and datetimes into a sql server datetime. it does depend on the db2 datetime being exactly 26 chars long though.

  IF OBJECT_ID('fn_FixDateTime') IS NOT NULL

  DROP function fn_FixDateTime

  GO

  create function fn_FixDateTime (@datetime varchar(26))

  returns varchar(20)

  AS

  begin

  declare @dtdatetime varchar(20)

  set @datetime = replace(@datetime, '0001-', '1900-')

  -- is it a time only?

  if charindex('.', @datetime)>0 and charindex('-', @datetime)=0

  begin

  set @datetime = left(replace(@datetime, '.',':'),12)

  end

  -- is it a date only?

  if charindex('-', @datetime)>0 and charindex('.', @datetime)=0

  begin

  set @datetime = @datetime

  end

  -- is it a date and time?

  if charindex('.', @datetime)>0 and charindex('-', @datetime)>0

  begin

  set @datetime = replace(left(

  left(@datetime, 10) + ' ' + right(@datetime, 15)

  , len( left(@datetime, 10) + ' ' + right(@datetime, 15))-7)

  ,'.',':'

  )

  end

  set @dtdatetime = @datetime

  return @dtdatetime

  end

  go