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...
>
>