September 11, 2008 at 1:45 pm
hi...
how can i convert a timestamp field to varchar?
thanks....
September 11, 2008 at 1:56 pm
September 11, 2008 at 2:17 pm
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
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- 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
September 11, 2008 at 2:18 pm
yes, i'm trying with convert
select convert(varchar, campo_timestamp)
September 11, 2008 at 2:30 pm
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
September 17, 2008 at 11:00 am
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
September 17, 2008 at 12:34 pm
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 datetimeNOT 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
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- 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
April 21, 2009 at 4:37 am
timestamp can be converted to binary[8]
no one else
April 21, 2009 at 4:44 am
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, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 22, 2009 at 1:44 pm
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
January 14, 2017 at 1:54 pm
Dear SQL Demigods,
Are there any concerns with using this CAST? If the value needs to be displayed in a manner a bit more legible. It hasn't caused me any problems.
A lowly underlying.
[Code]
CAST([RecordUpdateControl] as BigInt)
[/Code]
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply