Problem closing patient file!

Jassim Rahma

Hi, I have visits table for my patients database. I have created_date field which is timestamp with CURRENT_TIMESTAMP and closed_date with datetime datatypes. I have no problem with the created_date but when I close the file using this command:

UPDATE visits SET total_amount = param_total_amount, balance_amount = param_balance_amount, visit_is_open = FALSE, closed_date = NOW(), closed_user = param_created_user WHERE visit_id = param_visit_id;

The closed_date will have the same value of the created_date. All my closed_date now equal created_date

Technology News @ www.JassimRahma.com

Joezer BH

try GETDATE()

Cheees, Edo

Sandeep Mewara

Not sure how created_date is equal to closed_date. Make sure, that when you create a patient, at that time populate the created_date field (not closed_date - keep it blank) At the time of closing the patient, just update the closed_date for that patient, something like:

UPDATE visits SET closed_date = GETDATE() WHERE visit_id = param_visit_id;

Both are separate queries and both target separate fields. Refer: http://msdn.microsoft.com/en-us/library/ms188383.aspx[^]

Sandeep Mewara Microsoft ASP.NET MVP 2012 & 2013 [My Blog]: Sandeep Mewara's Tech Journal! [My Latest Article]: HTML5 Quick Start Web Application

Jassim Rahma

sorry Edo, forgot to mention it's MySQL spo there is no GETDATE()

Technology News @ www.JassimRahma.com

Jassim Rahma

H Snadeep, closed_date is Blank... here is the table structure:

CREATE TABLE `visits` (
`visit_id` int(11) NOT NULL AUTO_INCREMENT,
`file_no` int(11) DEFAULT NULL,
`visit_is_open` bit(1) DEFAULT b'1',
`clinic_id` int(11) DEFAULT NULL,
`doctor_id` int(11) DEFAULT NULL,
`account_category` int(11) DEFAULT NULL,
`account_number` bigint(11) DEFAULT NULL,
`price_list_id` int(11) DEFAULT NULL,
`vital_signs_taken` bit(1) DEFAULT b'0',
`total_amount` double(11,3) DEFAULT '0.000',
`balance_amount` double(11,3) DEFAULT '0.000',
`created_date` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`created_user` int(11) DEFAULT NULL,
`closed_date` datetime DEFAULT NULL,
`closed_user` int(11) DEFAULT NULL,
PRIMARY KEY (`visit_id`),
KEY `idx_visits_visit_id` (`visit_id`),
KEY `idx_visits_file_no` (`file_no`),
KEY `idx_visits_visit_is_open` (`visit_is_open`),
KEY `idx_visits_clinic_id` (`clinic_id`),
KEY `idx_visits_doctor_id` (`doctor_id`),
KEY `idx_visits_account_category` (`account_category`),
KEY `idx_visits_account_number` (`account_number`),
KEY `idx_visits_price_list_id` (`price_list_id`),
KEY `idx_visits_vital_signs_taken` (`vital_signs_taken`),
KEY `idx_visits_total_amount` (`total_amount`),
KEY `idx_visits_balance_amount` (`balance_amount`),
KEY `idx_visits_created_date` (`created_date`),
KEY `idx_visits_created_user` (`created_user`),
KEY `idx_visits_closed_date` (`closed_date`),
KEY `idx_visits_closed_user` (`closed_user`)
) ENGINE=MyISAM AUTO_INCREMENT=631 DEFAULT CHARSET=utf8;

Technology News @ www.JassimRahma.com

Joezer BH

Then I agree it's a strange phenomenon :) All other fields get updates correctly or the whole row does not get updated?

Cheees, Edo

Sandeep Mewara

Jassim Rahma wrote:

`created_date` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

This is the issue. You have set the field 'create_date' to be updated to current timestamp on any update - Wrong and not needed in your case. Just keep it:

-- If you are not updating this field on record creation
`created_date` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON INSERT CURRENT_TIMESTAMP,

-- If you are updating the field on record creation
`created_date` timestamp DEFAULT NULL,

Sandeep Mewara Microsoft ASP.NET MVP 2012 & 2013 [My Blog]: Sandeep Mewara's Tech Journal! [My Latest Article]: HTML5 Quick Start Web Application

Sandeep Mewara

Jassim Rahma wrote:

`created_date` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

This is the issue. You have set the field 'create_date' to be updated to current timestamp on any update - Wrong and not needed in your case. Just keep it:

-- If you are not updating this field on record creation
`created_date` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON INSERT CURRENT_TIMESTAMP,

-- If you are updating the field on record creation
`created_date` timestamp DEFAULT NULL,

Pick that you need and apply. As per other comment of MySQL, you can use NOW()

Sandeep Mewara Microsoft ASP.NET MVP 2012 & 2013 [My Blog]: Sandeep Mewara's Tech Journal! [My Latest Article]: HTML5 Quick Start Web Application

gvprabu

Hi, Use DEFAULT Constraint.... ALTER TABLE visits MODIFY closed_date DATETIME NOT NULL DEFAULT NOW(); Check the following link MySQL ALTER Command[^]