Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

convert timestamp to varchar Expand / Collapse
Author
Message
Posted Thursday, September 11, 2008 1:45 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, July 14, 2011 3:48 PM
Points: 51, Visits: 222
hi...
how can i convert a timestamp field to varchar?
thanks....
Post #568092
Posted Thursday, September 11, 2008 1:56 PM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Monday, December 8, 2014 1:04 PM
Points: 3,840, Visits: 3,854
Have you tried CAST or CONVERT?





John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
Post #568100
Posted Thursday, September 11, 2008 2:17 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 8:56 AM
Points: 6,748, Visits: 8,545
you just don't !

Check BOL for timestamp datatype and you'll figure out it is a type that sqlserver maintains itself ! (with every modification to the row !)

So you can only use it (dataset), but not convert it.


Johan


Don't drive faster than your guardian angel can fly ...
but keeping both feet on the ground won't get you anywhere

- How to post Performance Problems
- How to post data/code to get the best help


- How to prevent a sore throat after hours of presenting ppt ?


"press F1 for solution", "press shift+F1 for urgent solution"


Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me
Post #568113
Posted Thursday, September 11, 2008 2:18 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, July 14, 2011 3:48 PM
Points: 51, Visits: 222
yes, i'm trying with convert
select convert(varchar, campo_timestamp)
Post #568114
Posted Thursday, September 11, 2008 2:30 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Thursday, December 18, 2014 9:58 AM
Points: 13,872, Visits: 9,600
Is the data type actually "timestamp", or is it "datetime"?

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Post #568119
Posted Wednesday, September 17, 2008 11:00 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, November 24, 2014 8:50 PM
Points: 174, Visits: 427
you can convert the timestamp to binary and then convert the binary to varchar
however my code below involves an undocumented function... not sure if there is a better way to do this
it's important to realize that the timestamp column will keep changing every time the table gets modified but the converted value will retain whatever value it had at the time of the conversion


use tempdb

create table test (
ID int identity(1, 1) primary key
, sometext varchar(30) NOT NULL
, ts timestamp NOT NULL
, ts_converted_to_bin binary(8)
, bin_converted_to_varchar nvarchar(100)

)

insert into test (sometext)
select name
from sysobjects

update test set ts_converted_to_bin = convert(binary(8), ts)

update test set bin_converted_to_varchar = master.dbo.fn_varbintohexstr(ts_converted_to_bin)


select * from test

drop table test
Post #571196
Posted Wednesday, September 17, 2008 12:34 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 8:56 AM
Points: 6,748, Visits: 8,545
here's another test with the timestamp datatype:
/****** test script timestamp ******/
set nocount on
go
/*
@@DBTS returns the last-used timestamp value of the current database.
A new timestamp value is generated when a row with a timestamp column is inserted or updated.
*/
select @@DBTS
go
drop table dbo.t_test
go

print 'tabel dropped'
go

CREATE TABLE dbo.T_Test (
Sleutel int IDENTITY (1, 1) NOT NULL ,
Ms_Ts timestamp NOT NULL ,
Ms_Datetime datetime NOT NULL ,
Ms_Datetime_Last_Used datetime NOT NULL ,
Ms_Char char (10) NOT NULL
)
GO
print 'Table reated'
go
ALTER TABLE dbo.T_Test WITH NOCHECK ADD
CONSTRAINT DF_T_Test_Ms_Datetime DEFAULT (getdate()) FOR Ms_Datetime,
CONSTRAINT DF_T_Test_Ms_Datetime_Last_Used DEFAULT (getdate()) FOR Ms_Datetime_Last_Used,
CONSTRAINT PK_T_Test PRIMARY KEY NONCLUSTERED
(
Sleutel
)
GO
print 'Constraints added'
go

--drop trigger TrU_Ms_Datetime_Last_Used
--go

CREATE TRIGGER TrU_Ms_Datetime_Last_Used ON T_Test
FOR UPDATE

AS

if not UPDATE(Ms_Datetime_Last_Used)
begin

update T_Test set Ms_Datetime_Last_Used = CURRENT_TIMESTAMP where sleutel = ( select sleutel from deleted )

end
go
print 'Trigger added'



insert into t_test (Ms_Char) values('a')
go

insert into t_test (Ms_Char) values('b')
go
Print 'Rows inserted'
go


select * from t_test
go

Print 'Update starts here...'
go


update t_test set ms_char = 'c' where sleutel = 1

go

Print 'Sleutel 1 updated ...'
go

select * from t_test
go



select sleutel, ms_ts, cast(ms_ts as datetime) as Cast_ms_ts_datetime
, CONVERT (datetime, ms_ts) as Convert_ms_ts_datetime
from t_test
order by sleutel

go


update t_test set ms_char = 'D' where sleutel = 2

go

Print 'Sleutel 2 updated ...'
go

select * from t_test
go

select sleutel, ms_ts, cast(ms_ts as datetime) as Cast_ms_ts_datetime
, CONVERT (datetime, ms_ts) as Convert_ms_ts_datetime
from t_test
order by sleutel
go



Johan


Don't drive faster than your guardian angel can fly ...
but keeping both feet on the ground won't get you anywhere

- How to post Performance Problems
- How to post data/code to get the best help


- How to prevent a sore throat after hours of presenting ppt ?


"press F1 for solution", "press shift+F1 for urgent solution"


Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me
Post #571243
Posted Tuesday, April 21, 2009 4:37 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, April 21, 2009 4:36 AM
Points: 1, Visits: 0
timestamp can be converted to binary[8]
no one else
Post #701288
Posted Tuesday, April 21, 2009 4:44 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 9:22 AM
Points: 40,632, Visits: 37,094
Helical Johan (9/11/2008)
hi...
how can i convert a timestamp field to varchar?
thanks....


Why do you need the timestamp column as a varchar? Despite it's name, timestamp has absolutely nothing to do with dates or times.



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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

Post #701290
Posted Tuesday, September 22, 2009 1:44 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, December 14, 2011 12:41 AM
Points: 17, Visits: 23
Hi,

You can use it as shown below:
SELECT [master].[dbo].fn_sqlvarbasetostr(@@DBTS)

There is a predefined scalar function in Master database to do this..

Regards,
Navamohan K
Post #792153
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse