Re: Why is STR_TO_DATE failing?
I knew you were going to ask that.
It just tracks a lot of stuff from a lot of different parts of the app.
Questions sometimes have sub parts (columns & rows of sub questions).
There are 23 different kinds of questions.
Thus: CXCol, CXRow, MXInquiry, MXAnswer . . .
I think you're right and I really appreciate your help and candor.
The new Answer fields have been added now.
CREATE TABLE `answers` (
`RecordID` bigint unsigned NOT NULL AUTO_INCREMENT,
`QuestionaireID` smallint unsigned DEFAULT NULL,
`QuestionBankID` smallint unsigned DEFAULT NULL,
`QBSequenceRlID` smallint unsigned DEFAULT NULL,
`AttributionID` int unsigned DEFAULT NULL,
`AnswerLocationID` smallint DEFAULT NULL,
`AnswerEventID` smallint DEFAULT NULL,
`TouchPointID` smallint DEFAULT NULL,
`SurveyID` bigint unsigned DEFAULT NULL,
`SurveyorID` bigint unsigned DEFAULT NULL,
`SurveyorOrgID` int DEFAULT NULL,
`QTypeID` smallint unsigned DEFAULT NULL,
`QuestionID` int unsigned DEFAULT NULL,
`PredefinedAnswerID` int unsigned DEFAULT NULL,
`TDTRowID` int unsigned DEFAULT NULL,
`CXColumnID` int unsigned DEFAULT NULL,
`CXRowID` int unsigned DEFAULT NULL,
`LSTIdx` smallint unsigned DEFAULT NULL,
`MXInquiry` smallint DEFAULT NULL,
`MXAnswer` smallint DEFAULT NULL,
`ScaleID` smallint unsigned DEFAULT NULL,
`DMLevel` smallint unsigned DEFAULT NULL,
`DMItemIdx` smallint unsigned DEFAULT NULL,
`AnswerText` text CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci,
`AnswerNum` decimal(20,6) DEFAULT NULL,
`AnswerDate` date DEFAULT NULL,
`AnswerTime` time DEFAULT NULL,
`AnswerDateTime` datetime DEFAULT NULL,
`Score` float(10,2) unsigned DEFAULT NULL,
`ScoreSentiment` decimal(3,0) DEFAULT NULL COMMENT 'The calculated sentiment represented by the score',
`SentimentScore` decimal(3,2) DEFAULT NULL COMMENT 'The comment sentiment determined by Google.',
`SentimentMagnitude` decimal(3,2) DEFAULT NULL,
`CreatedByID` bigint DEFAULT NULL,
`DateCreated` datetime DEFAULT NULL,
PRIMARY KEY (`RecordID`),
KEY `QuestionID` (`QuestionID`),
KEY `QuestionaireID` (`QuestionaireID`),
KEY `QTypeID` (`QTypeID`),
KEY `SurveyID` (`SurveyID`),
KEY `QuestionBankID` (`QuestionBankID`),
KEY `QBSequenceRlID` (`QBSequenceRlID`),
KEY `AttributionID` (`AttributionID`),
KEY `SurveyorID` (`SurveyorID`),
KEY `PredefinedAnswerID` (`PredefinedAnswerID`),
CONSTRAINT `answers_ibfk_1` FOREIGN KEY (`QuestionaireID`) REFERENCES `questionaires` (`RecordID`) ON DELETE SET NULL,
CONSTRAINT `answers_ibfk_2` FOREIGN KEY (`QuestionBankID`) REFERENCES `question_banks` (`RecordID`) ON DELETE SET NULL,
CONSTRAINT `answers_ibfk_3` FOREIGN KEY (`QuestionID`) REFERENCES `questions` (`RecordID`),
CONSTRAINT `answers_ibfk_4` FOREIGN KEY (`QBSequenceRlID`) REFERENCES `qb_sequences_rl` (`RecordID`) ON DELETE SET NULL,
CONSTRAINT `answers_ibfk_5` FOREIGN KEY (`AttributionID`) REFERENCES `attributions` (`RecordID`) ON DELETE SET NULL,
CONSTRAINT `answers_ibfk_6` FOREIGN KEY (`SurveyID`) REFERENCES `surveys` (`RecordID`),
CONSTRAINT `answers_ibfk_7` FOREIGN KEY (`SurveyorID`) REFERENCES `person` (`RecordID`) ON DELETE SET NULL,
CONSTRAINT `answers_ibfk_8` FOREIGN KEY (`PredefinedAnswerID`) REFERENCES `q_predefined_answers` (`RecordID`)
) ENGINE=InnoDB AUTO_INCREMENT=5748 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci