TimeStamp question - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

TimeStamp question

Author

Topic

OldMySQLUser
Constraint Violating Yak Guru

301 Posts

Posted - 2009-07-23 : 08:20:19

I have a table with three columns, and am using

INSERT INTO DebtorCategories SELECT dtr.DebtorID, '0001'

FROM Debtor AS dtr
JOIN debt AS dt
ON dtr.DebtID = dt.DebtID
JOIN TraceOutputTemp AS tmp
ON dt.Code = tmp.Ref

where tmp.Ref = dt.code AND
tmp.DebtorNo = dtr.DebtorNo AND
@cnt = 0

to insert into the first to columns. How can I populate the third, timestamp, column please?

Non of the fields are identity fields otherwise I would use something like

IF @@ROWCOUNT > 0
BEGIN
SELECT @tstmp = [TimeStamp]
FROM DebtorCategories
WHERE <identityColumn> = scope_Identity()
END

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-07-23 : 08:24:40

So what datatype is it (datetime?) and which value do you want?

No, you're never too old to Yak'n'Roll if you're too young to die.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-23 : 08:29:15

like this ?


INSERT INTO DebtorCategories 
SELECT dtr.DebtorID, '0001', getdate()
FROM   Debtor AS dtr
JOIN   debt AS dt              ON dtr.DebtID = dt.DebtID
JOIN   TraceOutputTemp AS tmp  ON dt.Code    = tmp.Ref
where  tmp.Ref = dt.code 
AND    tmp.DebtorNo = dtr.DebtorNo 
AND    @cnt         = 0

KH
[spoiler]Time is always against us[/spoiler]

OldMySQLUser
Constraint Violating Yak Guru

301 Posts

Posted - 2009-07-23 : 08:29:39

DebtorCateogies table contains three columns:

int, varchar and timestamp

my insert into .. select .. already provides the frist two columns. It is the timestamp column that I need to provide (current timestamp).

OldMySQLUser
Constraint Violating Yak Guru

301 Posts

Posted - 2009-07-23 : 08:34:24

quote:

Originally posted by khtan

like this ?


INSERT INTO DebtorCategories 
SELECT dtr.DebtorID, '0001', getdate()
FROM   Debtor AS dtr
JOIN   debt AS dt              ON dtr.DebtID = dt.DebtID
JOIN   TraceOutputTemp AS tmp  ON dt.Code    = tmp.Ref
where  tmp.Ref = dt.code 
AND    tmp.DebtorNo = dtr.DebtorNo 
AND    @cnt         = 0

KH
[spoiler]Time is always against us[/spoiler]

this gave me:

*** error cannot insert an explicit value into a timestamp column

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-07-23 : 08:40:55

Your nickname says to me maybe that you are misunderstanding.
timestamp in MySQL is another thing than timestamp in SQL Server.

No, you're never too old to Yak'n'Roll if you're too young to die.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-23 : 09:10:03

The timestamp in SQL Server is rather misleading.
see http://msdn.microsoft.com/en-us/library/ms182776%28SQL.90%29.aspx

quote:
timestamp (Transact-SQL)

Is a data type that exposes automatically generated, unique binary numbers within a database. timestamp is generally used as a mechanism for version-stamping table rows. The storage size is 8 bytes. The timestamp data type is just an incrementing number and does not preserve a date or a time. To record a date or time, use a datetime data type.

I guess you need to store the date & time rather than the "timestamp". Use datetime data type for that

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources