SQL Server 2000

Question

Post reply

SQL Server 2000

shally.asm

Grasshopper

Points: 16
More actions
March 4, 2008 at 11:47 pm

#184724

Hi All,
I have a field in my table named LastLogindate which is of timestamp data type.When I run select query on this table,it is retuning some binary values for the LastLogindate column.How can I convert the values for this column from timestamp to datetime data type inSQL server 2000 as i have to show the last login date as the value for the abovesaid column?
please reply ASAP
Regards,
Shally

Viewing 15 posts - 1 through 15 (of 23 total)

You must be logged in to reply to this topic. Login to reply

TraderSam SSCrazy Points: 2931 More actions · Answer 1

Try this...

... convert(datetime, [columnname])

See SQL Server BooksOnline (BOL) for more info on the 'convert' function.

If it was easy, everybody would be doing it!;)

Jeff Moden SSC Guru Points: 1003889 More actions · Answer 2

No, no... the TIMESTAMP datatype has absolutely NOTHING to do with a date or a time. Despite what Sam offered up, the best you'll get is some date and time from the 1900's or maybe the 2000's but it won't be anything close to what the login date actually was.

Please see Books Online for what the TIMESTAMP data type actually is and why what I've said is true. Here's a part of it...

timestamp

timestamp is a data type that exposes automatically generated binary numbers, which are guaranteed to be unique within a database. timestamp is used typically as a mechanism for version-stamping table rows. The storage size is 8 bytes.

Remarks

The Transact-SQL timestamp data type is not the same as the timestamp data type defined in the SQL-92 standard. The SQL-92 timestamp data type is equivalent to the Transact-SQL datetime data type.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

TraderSam SSCrazy Points: 2931 More actions · Answer 3

Thanks for setting that straight...sounds like they need to change the datatype on the LastLoginDate to datetime.

If it was easy, everybody would be doing it!;)

Jeff Moden SSC Guru Points: 1003889 More actions · Answer 4

You got that right...

By the way... thanks for taking that the right way. 🙂

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

TraderSam SSCrazy Points: 2931 More actions · Answer 5

I have no problem admitting I blew that one...:hehe:

The biggest reason I read this forum is to learn new things...I learn a lot everyday thanks to everyone who contributes. A lot of discussion would never occur if it weren't for people posting things that are incorrect or misunderstood. Better to make a mistake here, than in some Production code somewhere!

If it was easy, everybody would be doing it!;)

shally.asm Grasshopper Points: 16 More actions · Answer 6

Thanks a lot to both of you for your responses.Even i had read the same about the timestamp data type.so,i just wanted to check out if there might be some other way.i guess we will now change the data type only.thanks a lot again:)

regards,

Shally

Jeff Moden SSC Guru Points: 1003889 More actions · Answer 7

Thanks for the feedback, Shally.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

OP_HELP SSCrazy Points: 2618 More actions · Answer 8

So will adding a datetime type column to the table automatically insert the "date/time" when the table is last updated?? I'm sorry, I guess I don't understand the conclusion.

The timestamp datatype was ruled out. datetime is implemented. But how do you implement the last time the table was updated? (for example, I need to expose the date/time when a table was updated on a asp.net control (on a label).

Any suggestion?

Garadin One Orange Chip Points: 29613 More actions · Answer 9

You can handle it in many ways.

A few are:

Passing in the date to the table along with the values being updated from ASP, and updating it at the same time.(probably the best method)

Adding a default constraint to the table DEFAULT (GETDATE()), (Note that this will only affect inserts, and won't update every time a change is made... but if they only insert data, it would be fine. I'd use this in conjunction with the above method)

You can create a FOR UPDATE trigger on the table to update the field whenever the table is updated.

Seth Phelabaum

Consistency is only a virtue if you're not a screwup. 😉

Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

Greg Snidow SSCoach Points: 16106 More actions · Answer 10

Here is one method that I use, mentioned by Garadin. It seems to be working for my situation, but you should try to "break" it. Maybe I have missed some scenarios where it won't work.

CREATE TRIGGER tr_your_trigger_name

ON [YourTable]

FOR INSERT,UPDATE

AS

IF @@ROWCOUNT = 0 RETURN

IF UPDATE(ColumnYouWantToAudit)

BEGIN

UPDATE [YourTable]

SET YourDateColumn = GETDATE()

FROM [YourTable] t,

inserted i

WHERE t.id = i.id

END

Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.

Jeff Moden SSC Guru Points: 1003889 More actions · Answer 11

Nicely done, Greg. And, if you want the date/time to change no matter which column is updated, just remove the IF...

CREATE TRIGGER tr_your_trigger_name

ON [YourTable]

FOR INSERT,UPDATE

AS

IF @@ROWCOUNT = 0 RETURN

UPDATE [YourTable]

SET YourDateColumn = GETDATE()

FROM [YourTable] t,

inserted i

WHERE t.id = i.id

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Greg Snidow SSCoach Points: 16106 More actions · Answer 12

DOH!...I didn't even think that was what the OP actually wanted.

Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.

Jeff Moden SSC Guru Points: 1003889 More actions · Answer 13

Dunno if it is... 🙂

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 14

Jeff Moden (10/20/2008)
Nicely done, Greg. And, if you want the date/time to change no matter which column is updated, just remove the IF...
CREATE TRIGGER tr_your_trigger_name
ON [YourTable]
FOR INSERT,UPDATE
AS
IF @@ROWCOUNT = 0 RETURN
UPDATE [YourTable]
SET YourDateColumn = GETDATE()
FROM [YourTable] t,
inserted i
WHERE t.id = i.id

Just be sure to disable recursive triggers...:)

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?