creating a mysql style timestamp col..? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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 2000 Forums

SQL Server Development (2000)

creating a mysql style timestamp col..?

Author

Topic

waskelton4
Starting Member

12 Posts

Posted - 2006-03-10 : 12:12:45

Hey group,
I'm in the process of converting a mysql db app over to mssql and I have a question re: timestamps.

I just noticed that the timestamp/rowversion datatype for mssql doesn't really have anything to do with a date or time.

My mysql version of this app uses timestamp to pull date and time info. Is there anyway for me to create a datetime col with a trigger or possibly just a default value of GetDate() to act like a mysql timestamp?

or.. are there any other potential solutions for this problem?

many thanks
ws

BAD SPELLERS OF THE WORLD UNTIE!!

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-03-10 : 12:23:07

Create a datetime col with a default value of GetDate()

CODO ERGO SUM

waskelton4
Starting Member

12 Posts

Posted - 2006-03-10 : 12:29:08

So after i finshed posting that message (i.e. thinking 'out loud') i wondered if that would update the col value when the row is updated or only add that value on INSERT.

how would it act?

ws

BAD SPELLERS OF THE WORLD UNTIE!!

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-03-10 : 14:44:10

waskelton4,

U can do a small experiment and find it (As I did)
If u update it won't get changed (since the default value - means the value in case no value is specified)

If u want to change in Updating, u can do it by the update statement or by a trigger

Kristen
Test

22859 Posts

Posted - 2006-03-11 : 11:46:15

"update the col value when the row is updated or only add that value on INSERT"

A DEFAULT will only apply a value on INSERT (and then only if the application doesn't provide a value/NULL for that column!)

You might like to consider having columns in your table for CREATE DATE and UPDATE DATE, and then enforce the Update Date either in your application or using a trigger (if you use a trigger it becomes hard to import data and retain the update date it comes with; if you do it in the application you have to remember to do it everywhere!)

Kristen

aiken
Aged Yak Warrior

525 Posts

Posted - 2006-03-11 : 18:22:05

If you use stored procedures to update data, you can easily do something like

create procedure UpdateData (@iID int,@iVal int,@dUpdateDate datetime=NULL) AS
set nocount on
update table set 
  datacolumn=@iVal,
  @dUpdateDate=IsNull(@dUpdateDate,getdate())
where id=@iID

...That way you can still import data, and you're not always passing in the current data from the app to the DB (which seems kind of silly).

-b

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources