Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

convert DB2 datetimes to SQL Server datetimes Expand / Collapse
Author
Message
Posted Tuesday, March 4, 2014 3:51 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Yesterday @ 6:59 PM
Points: 62, Visits: 291
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.
Post #1547592
Posted Thursday, March 6, 2014 1:27 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Yesterday @ 6:59 PM
Points: 62, Visits: 291
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
Post #1548464
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse