Need help with a date function in mysql

AnnetteB1305 · Aug 22, 2011

Hello,

I am trying to create a command from mysql database to get a report completed and am having issues changing a string field to a date. I have tried str_to_date and date and cdate and various others and nothing is working. There is an error in the sql syntax everytime. Can anyone help please.

Thanks,

Annette

feherke · Aug 22, 2011

Hi

Annette said:
There is an error in the sql syntax everytime.

Show those failed attempts together with the corresponding error messages and we will suggest corrections to them.

Feherke.

http://free.rootshell.be/~feherke/

AnnetteB1305 · Aug 22, 2011

This was the original attempt

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 (isnull(`opencall1`.`closedate`) or
cdate(`opencall1`.`closedate`) > dateadd('d',-8,{fn 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 (isnull(`opencall1`.`closedate`) or
cdate(`opencall1`.`closedate`) > dateadd('d',-8,{fn CURDATE()})
)
ORDER BY 1

and the error I got was:-

http://www.mediafire.com/?7vm6xhhu2k4nbez

AnnetteB1305 · Aug 22, 2011

In fact I'm getting that error message with all my attempts.

str_to_date

date

is_date

isdate

to_date

and a whole lot more

feherke · Aug 22, 2011

Hi

Are you sure all that is MySQL ? To me seems that you are mixing some VisualBasic code into the MySQL query.

Feherke.

http://free.rootshell.be/~feherke/

AnnetteB1305 · Aug 22, 2011

To be honest no, I am more used to sql however I am trying to get this command so I can get this report finally sorted....it has had me pulling my hair out for weeks now and just when I got it finally sorted something else is required. So if you can let me know where I am going wrong then I'd be grateful. I have looked at a number of sites to get the date function sorted but nothing I am trying is working.

feherke · Aug 22, 2011

Hi

What is opencall1.closedate field's type ? What is that cdate() function supposed to do ?

In meantime I would try to

Code:

[gray]-- change this[/gray]
[s]cdate(`opencall1`.`closedate`) > dateadd('d',-8,{fn CURDATE()})[/s]

[gray]-- into this[/gray]
opencall1.closedate > date_add(curdate(),interval -8 day)

Feherke.

http://free.rootshell.be/~feherke/

AnnetteB1305 · Aug 22, 2011

the opencall.closedate is a string so I'm trying to change it to a date to then only include rows with a closedate which is greater than 8 days before the current date. Hence the reason I can't just do opencall1.closedate > date_add(curdate(),interval -8 day)

vacunita · Aug 22, 2011

The MYSQL online manual is your firend.

http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_str-to-date

To convert a string into a date use str_to_date.

It takes 2 parameters.

the string, and the format its in.

So technically your attempt would look like:

Code:

str_to_date(opencall1,"%d/%m/%Y") > closedate

Assuming your date is in the format of 2digitday/2digitmonth/4digityear

----------------------------------
Phil AKA Vacunita
----------------------------------
Ignorance is not necessarily Bliss, case in point:
Unknown has caused an Unknown Error on Unknown and must be shutdown to prevent damage to Unknown.

Behind the Web, Tips and Tricks for Web Development.

http://behindtheweb.blogspot.com/

AnnetteB1305 · Aug 22, 2011

tried this but getting an error message 42000 error in SQL syntax but my field is opencall1.closedate so shouldn't it be str_to_date(opencall1.closedate,"%d/%m/%Y") But have tried this too and am getting same error message?????

feherke · Aug 23, 2011

Hi

Annette said:
the opencall.closedate is a string

And how are those date strings formatted ?

Feherke.

http://free.rootshell.be/~feherke/

AnnetteB1305 · Aug 23, 2011

They are date time but are strings and formatted in dd/mm/yyyy and then time. They are always formatted like this.

feherke · Aug 23, 2011

Hi

And how are those time strings formatted ?

Just add more format specifiers according to your time format, for example

Code:

[gray]-- 23/08/2011 20:39[/gray]
str_to_date(opencall1.closedate,'%d/%m/%Y %H:%i') > date_add(curdate(),interval -8 day)
[gray]-- 23/08/2011 20:39:28[/gray]
str_to_date(opencall1.closedate,'%d/%m/%Y %H:%i:%S') > date_add(curdate(),interval -8 day)
[gray]-- 23/08/2011 8:39am[/gray]
str_to_date(opencall1.closedate,'%d/%m/%Y %h:%i%p') > date_add(curdate(),interval -8 day)

Feherke.

http://free.rootshell.be/~feherke/

AnnetteB1305 · Aug 23, 2011

Still getting the error message near (opencall1.closedate,'%d/%m/%Y %H:%i:%S') > date_add(curdate(),interval -8 day).....I'm sure it's just a ploy to wind me up

feherke · Aug 23, 2011

Hi

Could you post the entire SQL command and error message as they look now ?

Feherke.

http://free.rootshell.be/~feherke/

AnnetteB1305 · Aug 23, 2011

This is the command:-

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 (Isnull(`opencall1`.`closedate) or
(`opencall1`.`closedate`,'%d/%m/%Y %H:%i:%S') > (date_add(curdate(),interval -8 day))
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 (Isnull(`opencall1`.`closedate) or
(`opencall1`.`closedate`,'%d/%m/%Y %H:%i:%S') > (date_add(curdate(),interval -8 day))
ORDER BY `opencall1`.`priority`

and this is the error

http://www.mediafire.com/?9drd649pffw96je

AnnetteB1305 · Aug 23, 2011

Sorry I've done that wrong didn't enter the date function correctly but still get the same error message

AnnetteB1305 · Aug 23, 2011

Try again:

This is the command:

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 (Isnull(`opencall1`.`closedate) or
str_to_date (`opencall1`.`closedate`,'%d/%m/%Y %H:%i:%S') > (date_add(curdate(),interval -8 day))
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 (Isnull(`opencall1`.`closedate) or
str_to_date (`opencall1`.`closedate`,'%d/%m/%Y %H:%i:%S') > (date_add(curdate(),interval -8 day))
ORDER BY `opencall1`.`priority`

feherke · Aug 23, 2011

Hi

The date handling should be fine now. The only syntax errors I see are the opening parentheses ( ( ) before the isnull() function calls which are never closed.

Feherke.

http://free.rootshell.be/~feherke/

AnnetteB1305 · Aug 23, 2011

I have just closed that but still the error???AAAAAAAAAAHHHH!!!

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Need help with a date function in mysql

Technical User

Programmer

Technical User

Technical User

Programmer

Technical User

Programmer

Technical User

Programmer

Technical User

Programmer

Technical User

Programmer

Technical User

Programmer

Technical User

Technical User

Technical User

Programmer

Technical User

Similar threads

Log in

Part and Inventory Search

Sponsor