Edit a datetime field

Question

Edit a datetime field

eyejay

Mr or Mrs. 500

Points: 542
More actions
December 26, 2010 at 4:01 pm

#242682

I maintain a database in our hospital of data collected from different pieces of equipment. I have noticed that one piece of equipement has caused the wrong data to be entered into the datetime field of a table on a number of occassions and I need to change the date component of that field only, eg: the date is currently stored as 2081-10-03 09.30.10 when it should read 2008-10-04 09.30.10. This error is repeated in many records with the time changing as new records are stored in the table during the course of the operation.
I can manipulate this field to get at just the date or just the time but I'm lost as to how to edit just the date component of this field for all the records with this error and would value your help.
Many thanks, Ian.

Viewing 7 posts - 1 through 6 (of 6 total)

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

Ron McCullough SSC Guru Points: 63877 More actions · Answer 1

It seems to this person, that effort should be expended to learn why that particular piece of equipment is sending incorrect data for incorporation into the database, and then to correct that condition, not to be manipulating the data to "make it correct", for how long do you think the manipulation effort will last.

What it does do, is to make questionable every other entry in that table and for that matter that database, who can tell what was "corrected" or what was "manipulated" for other reasons. And being medical data a most sensitive area to say the least.

Fix the equipment that is generating the "incorrect" data, not the existing data. Remember if you are a DBA your prime objective is to protect the data ... not "invent" new data.

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

Core6430 Old Hand Points: 363 More actions · Answer 2

Couldn't you add a trigger to the Insert to check if the Date being inserted is the same as today's date, and if it isn't then change the date but leave the time alone?

Couldn't you do something like:

SELECT @CurrentDate = CurrentDate

FROM Inserted Inner Join

MachineThingyTable ON Inserted.id= MachineThingyTable.Id

If (DATEDIFF (dd, @CurrentDate, GetDate() ) > 0) BEGIN

UPDATE MachineThingyTable

SET CurrentDate = DateAdd(dd, +1, @CurrentDate)

WHERE id = id

END

Jeff Moden SSC Guru Points: 1004318 More actions · Answer 3

I agree with BitBucket on this one. If the machine is not producing the correct dates, it needs to go to the repair/calibration lab NOW! There's no telling what else is wrong with it. Modifying the data to be supposedly "correct" will just delay the proper fix to the problem.

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

eyejay Mr or Mrs. 500 Points: 542 More actions · Answer 4

Jeeeez,

What is it with you lot??????????

You know nothing about exactly what this database holds or how it is managed or what happens to the data............

I'm simply a newbie asking for SQL advice not a ......... lecture!!

Forget I ever asked the question, I'll figure it out on my own.

Jeff Moden SSC Guru Points: 1004318 More actions · Answer 5

eyejay (12/27/2010)
Jeeeez,
What is it with you lot??????????
You know nothing about exactly what this database holds or how it is managed or what happens to the data............
I'm simply a newbie asking for SQL advice not a ......... lecture!!
Forget I ever asked the question, I'll figure it out on my own.

Heh... and what is it with you people that mask problems with equipment? Yes... I'll forget you ever asked the question. Just tell me your real name and the hospital you work for so I never make the mistake of hiring you or going to the hospital you "serve". 😉

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

spaghettidba SSC Guru Points: 105732 More actions · Answer 6

eyejay (12/26/2010)
I can manipulate this field to get at just the date or just the time but I'm lost as to how to edit just the date component of this field for all the records with this error and would value your help.

DATEPART and DATEADD should help you with your data.

Jeeeez,
What is it with you lot??????????
You know nothing about exactly what this database holds or how it is managed or what happens to the data............
I'm simply a newbie asking for SQL advice not a ......... lecture!!
Forget I ever asked the question, I'll figure it out on my own.

Some spanks should help with your attitude.

-- Gianluca Sartori