MySQL Forums
Forum List  »  Newbie

str_to_date formula not working please help
Posted by: Annette Bithell
Date: August 24, 2011 03:50AM

Hello,

I am trying to get a command sorted in a crystal report so that I can finally get this report sorted that has had all my time for about a month now!

Anyway back to the issue:-

I was getting an error in syntax on the command, I have split the command down and the statement I am getting the issue on is str_to_date and I don't know why.

Mysql version is protocol 10 and version 4.0.16.

The statement I am trying to use is:-

select str_to_date (closedate,'%d/%m/%Y %H:%i:%S') from opencall as date

This gets an error message:

[Err] 1064 - You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near '(closedate,'%d/%m/%Y %H:%i:%S') from opencall as date' at line

The field I am trying to convert to a date is a varchar(20) and is formatted as follows:
04/11/2009 07:15:41


The whole command is as follows:-

SELECT 'All',`opencall1`.`callref`, `opencall1`.`priority`, `opencall1`.`logdate`, `opencall1`.`closedate`, `mdis_status1`.`status_description`, `opencall1`.`cust_id`, `opencall1`.`suppgroup`, `updatedb1`.`updatetxt`
FROM (`swdata`.`opencall` `opencall1` INNER JOIN `swdata`.`updatedb` `updatedb1` ON `opencall1`.`callref`=`updatedb1`.`callref`) INNER JOIN `swdata`.`mdis_status` `mdis_status1` ON `opencall1`.`status`=`mdis_status1`.`status_code`
WHERE `opencall1`.`priority`<>'NL-MDIS SALES OFF SEND'
and `opencall1`.`closedate`='' or
str_to_date(`opencall1`.`closedate`) > dateadd('d',-8,CURDATE())
)
union all
SELECT 'All',`opencall1`.`callref`, `opencall1`.`priority`, `opencall1`.`logdate`, `opencall1`.`closedate`, `mdis_status1`.`status_description`, `opencall1`.`cust_id`, `opencall1`.`suppgroup`, `updatedb1`.`updatetxt`
FROM (`swdata`.`opencall` `opencall1` INNER JOIN `swdata`.`updatedb` `updatedb1` ON `opencall1`.`callref`=`updatedb1`.`callref`) INNER JOIN `swdata`.`mdis_status` `mdis_status1` ON `opencall1`.`status`=`mdis_status1`.`status_code`
WHERE `opencall1`.`priority`<>'NL-MDIS SALES OFF SEND'
and `opencall1`.`closedate`='' or
str_to_date(`opencall1`.`closedate`) > dateadd('d',-8,CURDATE())
)
ORDER BY `opencall`.`priority`


Any help would be highly welcomed!

Thanks in advance

Annette

Options: ReplyQuote




Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.