# Miscellaneous > Ask an Expert >  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

:Embarrassment:  . Thanks for defining me as a Leet.

Anything for you pal  :Big Grin:  

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  :Smilie:  and become Mak Dean  :Stick Out Tongue:  

LS

----------


## MAK

I am not into politics.  :Mad: 

Thanks for the suggestion tho. :Wink:

----------

