Create Function SQLDateToDB2Date(@TargetDate datetime)
return replace(replace(convert(varchar(30), @TargetDate, 121),':', '.'), ' ', '.')+'000'
DROP FUNCTION SQLDateToDB2Date
But how how handy blindman....definetly will come in handy..
However, and here's the rub, I bet Karolyn is importing as varchar the DB2 date as is (which is always 26 chars)
So we need to truncate it with something like LEFT(col1,23) then add the +'000'..
But the Korolyn, if you have a synch up process you'd be SOL...
I don't know DB2, so I just formatted the result as was demonstrated in Karolyn's other post.
"However, and here's the rub, I bet Karolyn is importing as varchar the DB2 date as is (which is always 26 chars)"
I think this is the opposite of her other post. I think she is trying to go from SQL Server to DB2 in this instance, while in her other open thread she is trying to convert DB2 to SQL Server format.
I see your point about adding the '000'.. I wondered about that too, but not being a DB2 person, I don't know how picky it would be about such things.
Mostly I wanted to show her the concept of a general conversion function, versus her attempt at returning only the current datetime.
The program imports/exports dates from and to DB2.
So with all you're advices, I'll :
- declare my tables with Char(26) instead of Datetime or VarChar to be sure that I have fixed columns
- even maybe create a user define type named DB2_DateTime with rules on data to be stored in those columns
- use the BlindMan function "CurrentTimestamp" to replace my "Current timestamp" in the old SQL Anywhere queries to store the Now date in the DB2 format
- use bulk insert to import data from a DB2 file (DB2 dates)
- maybe use bcp to export data into a file at DB2 destination
Getting there !
And they say that USA does'nt like France !