StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POMYSQL STR_TO_DATE not returning desired results
primarykey
Id
18977272
data
AcceptedAnswerId
0
AnswerCount
1
ClosedDate
CommentCount
0
CommunityOwnedDate
CreationDate
2013-09-24T09:08:45.800
FavoriteCount
0
LastActivityDate
2013-09-24T10:00:05.617
LastEditDate
2013-09-24T09:14:32.050
LastEditorUserId
1451836
OwnerUserId
1451836
ParentId
0
PostTypeId
1
Score
1
ViewCount
161
LastEditorDisplayName
text
Body
<p>I am trying to run a function to return the difference of days between two dates. For this reason, I fetch two dates based on a date parameter (this is working fine). Next, I use the parameter date <code>pdate</code> to construct dates if they are not found in a table. This construction of dates is not working as desired. Following is the code:</p> <pre><code>`DELIMITER $$ USE payrolldb001 $$ DROP FUNCTION IF EXISTS NoOfWorkingDays $$ CREATE DEFINER=`root`@`localhost` FUNCTION NoOfWorkingDays(P_EmployeeID INT,pdate DATE) RETURNS INT(10) READS SQL DATA BEGIN DECLARE v_FromDate DATE; DECLARE todate DATE; DECLARE workingdays INT; DECLARE shiftoff INT; DECLARE gazzetted INT; SET v_FromDate=(SELECT cp.FromDate FROM current_payroll cp INNER JOIN employees e ON e.employeeid=P_EmployeeID AND e.CompanyCode=cp.CompanyCode AND e.SubOfficeCode=cp.SubOfficeCode WHERE pdate BETWEEN cp.FromDate AND cp.ToDate); IF (v_FromDate IS NOT NULL) THEN SET todate=(SELECT cp.ToDate FROM current_payroll cp INNER JOIN employees e ON e.employeeid=P_EmployeeID AND e.CompanyCode=cp.CompanyCode AND e.SubOfficeCode=cp.SubOfficeCode WHERE pdate BETWEEN cp.FromDate AND cp.ToDate); ELSE SET v_FromDate=STR_TO_DATE(CONCAT(YEAR(pdate),'',MONTH(pdate)-1,'-21'),"%d%m%Y"); SET todate=STR_TO_DATE(CONCAT(YEAR(pdate),'-',MONTH(pdate),'-20'),"%d%m%Y"); END IF; SET workingdays = DATEDIFF(todate,v_FromDate); WHILE v_FromDate<=todate DO SET shiftoff= `isShiftOff`(P_EmployeeID,v_FromDate); IF (shiftoff=0) THEN SET gazzetted = `GazettedHoliday`(v_FromDate,P_Employeeid); SET workingdays = workingdays-gazzetted; ELSE SET workingdays=workingdays-shiftoff; END IF; -- RESET QUERY CACHE; -- FLUSH QUERY CACHE; SET v_FromDate = ADDDATE(v_FromDate,1); END WHILE; RETURN workingdays ; END$$ DELIMITER ;` </code></pre> <p>The error I am getting is as follows: <code>Incorrect datetime value: '2013-8-21' for function str_to_date</code> which is in the conversion for <code>v_FromDate</code>.</p>
Tags
<mysql><date>
Title
MYSQL STR_TO_DATE not returning desired results
singulars
PostAcceptedAnswerId
1. This table or related slice is empty.
PostParentId
1. This table or related slice is empty.
PostTypePostTypeId
1. PTQuestion
UserLastEditorUserId
1. USSalik
UserOwnerUserId
1. USSalik
plurals
PostLinksPostIdRelatedPostId
1. This table or related slice is empty.
PostLinksRelatedPostIdPostId
1. This table or related slice is empty.
PostsAcceptedAnswerId
1. This table or related slice is empty.
PostsParentIdCreationDate
1. PO
  singulars
  PostTypePostTypeId
  PTAnswer
VotesPostIdCreationDate
1. VO
  singulars
  PostPostId
  POMYSQL STR_TO_DATE not returning desired results
  UserUserId
  This table or related slice is empty.
  VoteTypeVoteTypeId
  VTUpMod
CommentsPostId
1. This table or related slice is empty.

Querying!

Guidance

A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.