str_to_date formula not working please help
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