Converting DB2 Timestamp Data to SQL Server 2005 - Problems with Unique Index

I am attempting to move a timestamp data column from DB2 to SQL Server 2005. Normally not a big deal but the column is part of unique index.

The DB2 timestamp has seconds of ss.ssssss but SQL Server only has ss.sss.

Most all the times entered into this column are a from an automated process so they are really close together timewise.

Here is what I have come up with so far:

1. Fast Load OLEDB with a batch of 10,000 records at a time

2. On the fail of the batch redirect rows to a regular table load OLEDB insert task

3. On the fail of the single insert redirect rows to script that ups the seconds one tick.

4. Attempt one last insert of the modified rows

5. If fail, then store the record off to a delimited text file

I am hoping to get the number of records that wind up in the delimited text file to be a very small number and not in the 1,000+.

Any help would be appreciated.

Redirecting errors from the FastLoad to a regular OLEDB destination is a technique that I've used before to capture only the row that caused the error - that should work fine.

I have another question, though. If you can change this timestamp value (as you are planning in step 3), it doesn't seem to be all that significant. Why not drop just add an identity column to the unique index?

|||

Adding an Identity column to the table and then adding into the index sounds like a good idea to get around the problem. Especially since the package is going to be scheduled to run every 15 to 30 minutes to sync the DB2 table to the SQL Server table during the migration time frame.

Thanks for the suggestion. I'll let you know what we have decided to do.

|||

Upon further inspection of this process and doing a small test this method is not an option for me.

The addition of an indentity column to the unqiue index would now in essence make the index non-unique. The identity column is a unique value and would then allow for the business rule of this date time column to be broken.

I am attempting to try and see if I can capture the data column as a string for manipulation, but right now the OleDB connection is pulling the catalog info from DB2 and knows that the column is supposed to be a date time. Any attempt to use the column in the SSIS package as a character I get errors.

Still looking for suggestions before starting down the path of writing an application or off the shelf tool to handle the sync between these tables.

Thanks.

|||

You should be able to use a derived column to convert the value to a string - that is probably your best bet to avoid violating the business rule.

Code Snippet

(DT_WSTR, 10) [ColumnName]

Converting DateTime

Hi,
When I open a tabe in the SQL enterprise manager I see the Timestamp Field
in this Format :
24/01/2005 16:45:00

However when I'm using the Query analyzer or other SQL Client I see the
Timestamp Field in this Format :
2005-01-24 16:44:59.997

Does Anybody know what to do in order to display the Timestamp Field in
first Format (24/01/2005 16:45:00) ?

Please Advise,
Yariv

--
Message posted via http://www.sqlmonster.comLook up "Date Time String Transformation" in SQL Server BOL

"Yariv via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:732a9f6af71c48d6ab1ff539f0fe3351@.SQLMonster.c om...
> Hi,
> When I open a tabe in the SQL enterprise manager I see the Timestamp Field
> in this Format :
> 24/01/2005 16:45:00
> However when I'm using the Query analyzer or other SQL Client I see the
> Timestamp Field in this Format :
> 2005-01-24 16:44:59.997
> Does Anybody know what to do in order to display the Timestamp Field in
> first Format (24/01/2005 16:45:00) ?
> Please Advise,
> Yariv
> --
> Message posted via http://www.sqlmonster.com|||"Yariv via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:732a9f6af71c48d6ab1ff539f0fe3351@.SQLMonster.c om...
> Hi,
> When I open a tabe in the SQL enterprise manager I see the Timestamp Field
> in this Format :
> 24/01/2005 16:45:00
> However when I'm using the Query analyzer or other SQL Client I see the
> Timestamp Field in this Format :
> 2005-01-24 16:44:59.997
> Does Anybody know what to do in order to display the Timestamp Field in
> first Format (24/01/2005 16:45:00) ?
> Please Advise,
> Yariv
> --
> Message posted via http://www.sqlmonster.com

Check out CONVERT() in Books Online. But you need to remember that MSSQL
stores datetime values in an internal format, and each client (including EM
and QA) decides how to display them. See here for more details:

http://www.karaszi.com/sqlserver/info_datetime.asp

Simon|||Yariv via SQLMonster.com (forum@.SQLMonster.com) writes:
> When I open a tabe in the SQL enterprise manager I see the Timestamp Field
> in this Format :
> 24/01/2005 16:45:00
> However when I'm using the Query analyzer or other SQL Client I see the
> Timestamp Field in this Format :
> 2005-01-24 16:44:59.997
> Does Anybody know what to do in order to display the Timestamp Field in
> first Format (24/01/2005 16:45:00) ?

Under Tool->Options->Connections, check the third checkbox, "Use
regional settings...".

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Converting a type (e.g. Decimal) BEFORE writing to ResultSet ?

As well known a DATE or TIMESTAMP type can be converted (to VARCAHR) after s
election but BEFORE
writing to the ResultSet by using the CONVERT function e.g.
SELECT CONVERT(char(10), MYTIMESTAMP, 101)) FROM ... WHERE ...
Is there something similar if the original field is a DECIMAL/NUMERIC field?
E.g.
SELECT DEC_CONVERT(char(30), MYDECIMAL, '###########0.00') FROM .... WHERE
...
GeorgeGeorge Dainis wrote:
> As well known a DATE or TIMESTAMP type can be converted (to VARCAHR)
> after selection but BEFORE writing to the ResultSet by using the
> CONVERT function e.g.
> SELECT CONVERT(char(10), MYTIMESTAMP, 101)) FROM ... WHERE ...
> Is there something similar if the original field is a DECIMAL/NUMERIC
> field?
> E.g.
> SELECT DEC_CONVERT(char(30), MYDECIMAL, '###########0.00') FROM ....
> WHERE ...
> George
Just use CONVERT or CAST:
Select CONVERT(char(30), MyDecimal) From ...
Select CAST(MyDecimal as char(30)) From ...
David Gugick
Imceda Software
www.imceda.com|||You can also use function STR.
Example:
declare @.d decimal(8, 2)
set @.d = 50.25 / 2.00
select @.d, str(@.d, 8, 2)
go
AMB
"George Dainis" wrote:

> As well known a DATE or TIMESTAMP type can be converted (to VARCAHR) after
selection but BEFORE
> writing to the ResultSet by using the CONVERT function e.g.
> SELECT CONVERT(char(10), MYTIMESTAMP, 101)) FROM ... WHERE ...
> Is there something similar if the original field is a DECIMAL/NUMERIC fiel
d?
> E.g.
> SELECT DEC_CONVERT(char(30), MYDECIMAL, '###########0.00') FROM .... WHER
E ...
> George
>|||>From the documentation ... "CONVERT converts a character string from
one character set to another. The datatype of the returned value is
VARCHAR2." So what you are seeing is an implicit conversion to varchar2
because you are using a function that accepts a char as input.
Look at the functions TO_CHAR(), TO_DATE(), TO_NUMBER() and CAST() for
type conversion ...
http://download-west.oracle.com/doc.../b10759/toc.htm|||Oh that was strange ... I accessed the question through
comp.databases.oracle.misc but google tells me that the answer will get
posted to a sqlserver group? hmmm.|||On 16 Feb 2005 04:51:03 -0800, David Aldridge wrote:

>Oh that was strange ... I accessed the question through
>comp.databases.oracle.misc but google tells me that the answer will get
>posted to a sqlserver group? hmmm.
Hi David,
The orinal question was crossposted to a total of three groups:
* comp.databases.oracle.misc
* microsoft.public.sqlserver.programming
* comp.databases.oracle
The followup-to was set to only the SQL Server group. The use of CONVERT
in the original question suggests that this is indeed a SQL Server related
question. I have no idea why the original poster has included two Oracle
groups in his crossposting.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||"George Dainis" <george.dainis@.bluecorner.com> wrote in message
news:cuu58t$irh$04$1@.news.t-online.com...
> As well known a DATE or TIMESTAMP type can be converted (to VARCAHR) after
> selection but BEFORE
> writing to the ResultSet by using the CONVERT function e.g.
> SELECT CONVERT(char(10), MYTIMESTAMP, 101)) FROM ... WHERE ...
> Is there something similar if the original field is a DECIMAL/NUMERIC
> field?
> E.g.
> SELECT DEC_CONVERT(char(30), MYDECIMAL, '###########0.00') FROM ....
> WHERE ...
> George
>
Also well known are the TO_CHAR, ROUND, and TRUNC functions -- and don't
forget the CAST operator ;-)
++ mcs

convert unix timestamp to UTC

Hi

Is there a way in T-SQL (Server 2005) to convert unix timestamp to UTC

So want to do this:

1189481763.61 -> Tue, 11 Sep 2007 03:36:03 UTC(or any UTC format)

Thanks

You can use the DATEADD function to add the unix date time value to Jan 1 1970 12:00:00 AM.

Code Snippet

DECLARE @.unixDate FLOAT,

@.tsqlDate DATETIME

SET @.unixDate = 1189481763.61

SET @.tsqlDate = '01/01/1970 00:00:00 AM'

SET @.tsqlDate = DATEADD(ss, @.unixDate, @.tsqlDate)

PRINT @.tsqlDate

|||

Hi

i was planning on doing that but but the problem with that is :

number

Is the value used to increment datepart. If you specify a value that is not an integer, the fractional part of the value is discarded. For example, if you specify day for datepart and1.75 for number, date is incremented by 1.

So it will truncate 1189481763.61 to 1189481763

I was looking for a work around

Thanks

|||

Code Snippet

DECLARE @.unixDate float,
@.tsqlDate DATETIME
SET @.unixDate = 1189481763.61

SET @.tsqlDate = '01/01/1970 00:00:00 AM'
SET @.tsqlDate = DATEADD(ss, @.unixDate, @.tsqlDate)
PRINT convert(varchar(15),@.tsqlDate,114)
-- Now convert the fractional part into millseconds and add to the date
SET @.tsqlDate = DATEADD(ms, (@.UnixDate - cast(@.unixDate as int)) *1000, @.tsqlDate)
PRINT convert(varchar(15),@.tsqlDate,114)

convert timestamp to datetime

Dear all,

Could anyone advise how to convert timestemp to Datetime value using T-SQL?

for exmple of timestamp 0x00083D9C95BBF180 and I want to have it in readable datetime.

Many thanks

LG

you have to use convert

ex.

declare @.x timestamp

select @.x = convert(timestamp,getdate())

select @.x, convert(datetime,@.x) as readabledatetime, convert(smalldatetime,convert(datetime,@.x)) as readablesmalldatetime|||You couldn't convert timestamp to datetime. Timestamp is a row version not datetime of row change. When you add or update row timestamp increase
|||

AS Konstantin indicates, the timestamp datatype has nothing to do with date and time values. It is a sequential binary number that increments upon any change to the database.

It is useful for managing sequence, or determining if data has been altered. But you can't get datetime from it...

|||arnie and konstantin are correct, we'll just have to ask LG how he implemented the timestamp field or variable that he had to come up with a requirement of converting timestamp to a datetime|||

I'm use this statement.

CONVERT(CHAR(19), GETDATE(),20)

convert hexadecimal datetime to normal datetime

Hi,
I have a field of timestamp datatype. The data is hexadecimal.
I would like to create a function or query the field so that I can see it
as normal 00:00:00 format?
It would also be nice to be able to query the field by entering a 00:00:00
value but it searches the field in the hexadecimal format and then returns
the results again in the 00:00:00 format.
thanksChris wrote:
> Hi,
> I have a field of timestamp datatype. The data is hexadecimal.
> I would like to create a function or query the field so that I can see it
> as normal 00:00:00 format?
> It would also be nice to be able to query the field by entering a 00:00:00
> value but it searches the field in the hexadecimal format and then returns
> the results again in the 00:00:00 format.
> thanks
Use the undocumented extended sproc xp_varbintohexstr
CREATE FUNCTION dbo.TStoString
(
@.ts binary(8)
)
RETURNS varchar(20)
AS
BEGIN
declare @.s varchar(20)
EXEC master.dbo.xp_varbintohexstr @.ts, @.s out
RETURN @.s
END|||Hi Chris
What do you mean by the normal 00:00:00 format?
A timestamp value has absolutely nothing to do with time. It is an internal
counter. Timestamps are also not meant to be queried. SQL Server compares
them internally to determine if a row has been updated.
If you want a datetime column for your own querying, you can add one to the
table.
HTH
Kalen Delaney, SQL Server MVP
"Chris" <Chris@.discussions.microsoft.com> wrote in message
news:8E6CBAB6-246F-445B-B56E-60B71C494053@.microsoft.com...
> Hi,
> I have a field of timestamp datatype. The data is hexadecimal.
> I would like to create a function or query the field so that I can see it
> as normal 00:00:00 format?
> It would also be nice to be able to query the field by entering a 00:00:00
> value but it searches the field in the hexadecimal format and then returns
> the results again in the 00:00:00 format.
> thanks|||Chris,
I think your by the name of the data type. SQL Server has datetime
and timestamp data types, but the second one has nothing to do with datetime
.
Example:
use northwind
go
create table dbo.t1 (
c1 int not null identity(1, 1) unique,
c2 datetime,
c3 timestamp
)
insert into dbo.t1 default values
insert into dbo.t1 default values
select * from t1
update
t1
set
c2 = getdate()
where
c1 = 1
select * from dbo.t1
drop table dbo.t1
go
See datetime and timestamp in BOL for more info.
AMB
"Chris" wrote:

> Hi,
> I have a field of timestamp datatype. The data is hexadecimal.
> I would like to create a function or query the field so that I can see it
> as normal 00:00:00 format?
> It would also be nice to be able to query the field by entering a 00:00:00
> value but it searches the field in the hexadecimal format and then returns
> the results again in the 00:00:00 format.
> thanks|||Thanks Kalen,
I learned in the books online that is useless to me as an actual way to
determine at what time a record was updated.
"Kalen Delaney" wrote:

> Hi Chris
> What do you mean by the normal 00:00:00 format?
> A timestamp value has absolutely nothing to do with time. It is an interna
l
> counter. Timestamps are also not meant to be queried. SQL Server compares
> them internally to determine if a row has been updated.
> If you want a datetime column for your own querying, you can add one to th
e
> table.
> --
> HTH
> Kalen Delaney, SQL Server MVP
>
> "Chris" <Chris@.discussions.microsoft.com> wrote in message
> news:8E6CBAB6-246F-445B-B56E-60B71C494053@.microsoft.com...
>
>

convert db2 timestamp to sql server datetime

can someone please supply some information to help with this??

I am moving data from db2 8.1 for windows. the dates in db2 are defined as timestamp. i want to convert these to sql server datetime format in sql server 2000 using dts and sql.

does anyone have examples or something??

any help would be greatly appreciated.Look here

http://weblogs.sqlteam.com/brettk/archive/2005/06/02/5528.aspx