Timestamp to Datetime

Question

Timestamp to Datetime

Viewing 4 posts - 16 through 18 (of 18 total)

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

Jeff Moden SSC Guru Points: 1003869 More actions · Answer 1

andrewd.smith (4/8/2009)
Obviously, the actual value of the resulting datetime column is meaningless.

At the risk of rattling the rubble, that statement is 100% spot on. The original developer made a horrible mistake in trying to convert the TimeStamp data type, which has absolutely nothing to do with any form of actual date or time, to a DateTime. Instead of perpetuating that same error, the developer who now has the ball needs to make management keenly aware of the problem and get the time allocated to fix this. The best temporary fix would be to remove the field from the display altogether. The long term fix would be to add the correct datetime column either to the original table or to a sister table.

Sorry to sound so angry about this, but this is just wrong. Someone tell me how to find the original developer on this... I wanna take him/her out for a full blown 9 course pork chop dinner.

--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)

leo4Ever Right there with Babe Points: 735 More actions · Answer 2

Yes, I agree with you Jeff. That's the reason I had mentioned this as a workaround rather than the best possible fix.

The final datetime value obtained from timestamp being meaningless is not important in my current case. There is another column in the table which is of type datetime, that handles the last updated time and value.

The timestamp column is just to prevent users from overwriting each other and more over the column is hidden from the end user and there is no way they could change it through UI or any other possible way.

andrewd.smith SSCrazy Eights Points: 8444 More actions · Answer 3

Agree entirely with Jeff.

My previous post was wrong on a couple of points and wouldn't even have fixed up the OPs problem as a temporary workaround. I've corrected my post, and incidently learnt something new about the internal binary storage of the datetime data type in the process.

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

leo4Ever (4/8/2009)
Yes, I agree with you Jeff. That's the reason I had mentioned this as a workaround rather than the best possible fix.
The final datetime value obtained from timestamp being meaningless is not important in my current case. There is another column in the table which is of type datetime, that handles the last updated time and value.
The timestamp column is just to prevent users from overwriting each other and more over the column is hidden from the end user and there is no way they could change it through UI or any other possible way.

I understand and appreciate all of that except for this being a workaround and, heh, I'm pretty sure that I understand what a TimeStamp column is supposed to be used for. The best short term workaround would be to not display the field as a date. I'm just trying to protect the IT department where you work because the users have just got to think what they see is insane especially if they know what date they made a change on.

Heh... I'd still like to get my hands on the original developer, some day. 😉

--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)