convert varchar to timestamp

Question

Post reply

convert varchar to timestamp

mysticslayer

Old Hand

Points: 319
More actions
July 12, 2009 at 5:02 am

#134249

Hi all,
I'm having a problem to convert a varchar to timestamp. Someone in our business wants to use an export and when all data is changed in the export the business wants to import it back into the database, but only the records that aren't changed yet.
So I'm having the issue that I get an string back from the import and placed into a stored procedure, but I can't convert a varchar(255) '' back to a timestamp.
Anyone have an idea how to solve this issue?

Viewing 15 posts - 1 through 15 (of 15 total)

You must be logged in to reply to this topic. Login to reply

ps. One Orange Chip Points: 29423 More actions · Answer 1

can you pls post an example of the format of source data that u want to be converted to datetime?

Pradeep Singh

mysticslayer Old Hand Points: 319 More actions · Answer 2

It's not a datetime format, but a timestamp format:

this is an example how it works when I get the data for my sp.

exec sp_example '9929129', '0x0000000001E5CFE7'

where the hex is my timestamp, it's also known as rowversion.

example data in stored procedure

ALTER PROCEDURE [dbo].[sp_example]

(

@Schip_Id varchar(10),

@TimeStamp varchar(20)

)

AS

BEGIN

-- the declare statement is used for unusing of quotes from the @TimeStamp...

DECLARE @convertedTimeStamp varchar(20)

SET @convertedTimeStamp = @timestamp

print @convertedTimeStamp

select * from

WHERE schip_id = Schip_Id

and ([timestamp] = Convert(varbinary(8), @convertedTimeStamp))

END

ps. One Orange Chip Points: 29423 More actions · Answer 3

I changed the datatype of @TimeStamp parameter of the sp to TimeStamp and created some test data and i could select exact values

-- create a test table

create table #t

(

col1 int,

timestamp timestamp

)

insert into #t(col1) values(1)

go 5

select * from #t

OUTPUT

----------

col1 timestamp

----------- ------------------

1 0x00000000000007D8

1 0x00000000000007D9

1 0x00000000000007DA

1 0x00000000000007DB

1 0x00000000000007DC

1 0x00000000000007DD

1 0x00000000000007DE

1 0x00000000000007DF

1 0x00000000000007E0

1 0x00000000000007E1

-- create the proc

alter PROCEDURE [dbo].[sp_example]

(

@Schip_Id varchar(10),

@TimeStamp timestamp

)

AS

BEGIN

-- the declare statement is used for unusing of quotes from the @TimeStamp...

DECLARE @convertedTimeStamp varchar(20)

SET @convertedTimeStamp = @timestamp

print @convertedTimeStamp

select * from #t

WHERE col1 = @Schip_Id

and [timestamp] = @TimeStamp

--Convert(varbinary(8), @convertedTimeStamp))

END

-- run the sp

exec sp_example 1,0x00000000000007DC

OUTPUT-

---------

col1 timestamp

----------- ------------------

1 0x00000000000007DC

(1 row(s) affected)

Is this what you need?

Pradeep Singh

mysticslayer Old Hand Points: 319 More actions · Answer 4

The issue here is that I get an parameter filled with quotes. This isn't accepted by SQL Server when executing the stored procedure. So I can't validate on the timestamp in the where clause.

bovell_bernard Valued Member Points: 69 More actions · Answer 5

Just to verify, do you have quotes in your timestamp? .. what data type in timestamp

ps. One Orange Chip Points: 29423 More actions · Answer 6

mysticslayer (7/12/2009)
The issue here is that I get an parameter filled with quotes. This isn't accepted by SQL Server when executing the stored procedure. So I can't validate on the timestamp in the where clause.

I dont think it's possible to convert a varchar to timestamp..

may be you can modify the application that exports data to exclude the quotes in this field.

Pradeep Singh

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 7

Try making the parameter a varbinary instead of a varchar.

ALTER PROCEDURE [dbo].[sp_example]

(

@Schip_Id varchar(10),

@TimeStamp varbinary(8)

)

AS

BEGIN

-- the declare statement is used for unusing of quotes from the @TimeStamp...

print @convertedTimeStamp

select * from

WHERE schip_id = Schip_Id

and ([timestamp] = @convertedTimeStamp)

END

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 8

mysticslayer (7/12/2009)
The issue here is that I get an parameter filled with quotes.

Not quite sure what you mean here. Could you give more detail?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

ps. One Orange Chip Points: 29423 More actions · Answer 9

GilaMonster (7/13/2009)
mysticslayer (7/12/2009)
The issue here is that I get an parameter filled with quotes.
Not quite sure what you mean here. Could you give more detail?

Perhaps he's getting data as '0x00000000000007D6' as a string instead of plain 0x00000000000007D6.

Pradeep Singh

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 10

ps (7/13/2009)
GilaMonster (7/13/2009)
mysticslayer (7/12/2009)
The issue here is that I get an parameter filled with quotes.
Not quite sure what you mean here. Could you give more detail?
Perhaps he's getting data as '0x00000000000007D6' as a string instead of plain 0x00000000000007D6.

That's something that has to be fixed in whatever is calling the proc.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

mysticslayer Old Hand Points: 319 More actions · Answer 11

Well, because SSIS sees it as a varchar.

But I found a solution for me as well for SQL2k0, SQL2k5 and SQL2k8.

So this topic can be closed 🙂

ps. One Orange Chip Points: 29423 More actions · Answer 12

It'd be great for everyone if you share the solution here 🙂

Pradeep Singh

Jeff Moden SSC Guru Points: 1004642 More actions · Answer 13

mysticslayer (7/13/2009)
Well, because SSIS sees it as a varchar.
But I found a solution for me as well for SQL2k0, SQL2k5 and SQL2k8.
So this topic can be closed 🙂

Two way street here... 😉 What IS the solution? Don't get labeled as a "checkvalve" on only your 4th visit. :hehe:

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004642 More actions · Answer 14

Heh... seriously...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)