Import DB2 timestamp into SQL - out-of-range datetime value error msg

[Lava]

I have to import DB2's timestamp data to sql server table. Time stamp data look like below
--2000-03-24-00.00.00.000000
--I am getting the following errors. Please guide me.

select convert(datetime,'2003-12-24-00.00.00.000000')

Server: Msg 242, Level 16, State 3, Line 1
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

select convert(datetime,'2003-12-24-00.00.00.000000')

Server: Msg 242, Level 16, State 3, Line 1
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

Thanks;
LS

[MAK]

declare @x varchar(26)
set @x='2003-12-24-17.55.34.002000'
select convert(datetime,left(@x,10) + ' ' +replace(substring(@x,12,12),'.',':'))

[Lava]

Thank you very much MAK, it worked.

[anam]

Hi Mak;

Sorry but I had to change my user-id because I had problems with my other id.

Anyhow, Now, here is another thing which I don't know how to make it to work:

When I type:

select * from openquery(db2c1,'select {fn convert(Col1,SQL_TIMESTAMP)} from DMA.EVH')

It returns the data in the correct format: 2004-01-09-13.57.08.703829


But when I type:
select Col1 into table1 from openquery(db2c1,'select {fn convert(Col1,SQL_TIMESTAMP)} from DMA.EVH')
it won’t work, it gives invalid column name error message.

Any idea?

LS

[MAK]

1. This is not the right SQL format

"2004-01-09-13.57.08.703829"

This is the right sql format

"2004-01-09 13:57:08:703"


This is not right SQl Statement
select Col1 into table1 from openquery(db2c1,'select {fn convert(Col1,SQL_TIMESTAMP)} from DMA.EVH')


This is right SQL Statement
select convert(datetime,left(COl1,10) + ' ' +replace(substring(COl1,12,12),'.',':')) as COl1
into table1 from openquery(db2c1,'select {fn convert(Col1,SQL_TIMESTAMP)} as COl1 from DMA.EVH')

[anam]

Mak;

That DB2 table gets updated every day by the user. There is a date/time stamp for every single incident they enter. How you transfer the data into SQL table on regular bases? User prefers using stored procedure, so how you do so?

[MAK]

create procedure usp_dailyimportfromdb2
as
declare @query varchar(2000)
declare @mydate varchar(10)
set @mydate=convert(.....db2 timestamp format of today's date)

set @query= 'insert into sqltable select col1,col2,col3,col4, timestampcolumn
from openquery(DB2server," Select col1,col2,col3,col4, timestampcolumn
from db2table where timestamp column>=
+@mydate+'")'
print @query
exec (@query)
go
--schedule this procedure to run daily
--remember format the @mydate similar to DB2 timestamp

[MAK]

please read
set @mydate=convert(.....db2 timestamp format of today's date)

as

set @mydate=convert(.....db2 timestamp format of yesterday's date)

or
use between 12:00Am to 11:59PM

[MAK]

or

create procedure usp_dailyimportfromdb2
as
declare @query varchar(2000)
declare @mydate datetime
declare @mydate2 datetime

set @mydate=(select max(timestamp) from sqltable)
set @mydate2=convert(.....db2 timestamp format of @mydate)

set @query= 'insert into sqltable select col1,col2,col3,col4, timestampcolumn
from openquery(DB2server," Select col1,col2,col3,col4, timestampcolumn
from db2table where timestamp column>=
+@mydate2+'")'
print @query
exec (@query)
go
--schedule this procedure to run daily
--remember format the @mydate2 similar to DB2 timestamp

[pixiesskater]

MAK i've been reading some of your replies to peoples posts and you know alot about what sql and all and i know the littlest i'm taking a Oracle 1 class in highschool and i was wondering if you had like AIM or MSN messenger and i could talk to you to learn some more if you willing to teach



THanks

MAK = 1337

[MAK]

. Thanks for defining me as a Leet.

Anything for you pal

Kidding. Please post any questions in databasejournal, so that many people can get Benefited and also when I am not available many other 1337 can answer the questions.

[anam]

Mak, see now u can run for SQL Presidency and become Mak Dean

LS

[MAK]

I am not into politics.

Thanks for the suggestion tho.