Need help with a date function in mysql

[AnnetteB1305]

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]

Hi

Another syntax error : in the isnull() function's parameter the closing backtick is missing after the field name. ( Personally I hate them. )

If still get error, let use try to reduce the amount of code. Is this cut off version still throwing the error ?

[b]SELECT[/b] [teal]*[/teal]

[b]FROM[/b] opencall1

[b]WHERE[/b] opencall1[teal].[/teal]priority[teal]<>[/teal][green][i]'NL-MDIS SALES OFF SEND'[/i][/green]  
[b]and[/b] isnull[teal]([/teal]opencall1[teal].[/teal]closedate[teal])[/teal]
[b]or[/b] str_to_date[teal]([/teal]opencall1[teal].[/teal]closedate[teal],[/teal][green][i]'%d/%m/%Y %H:%i:%S'[/i][/green][teal])[/teal] [teal]>[/teal] [teal]([/teal]date_add[teal]([/teal]curdate[teal](),[/teal]interval [teal]-[/teal][purple]8[/purple] day[teal]))[/teal]

Feherke.

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

 

[AnnetteB1305]

Hello,

Ok I've stripped each part out into a query analyser. I've amended the isnull as couldn't get that to work to (`opencall1`.`closedate='') and all seems to work apart from the string to date section. I have tried this in the query analyser:
select str_to_date (closedate,'%d/%m/%Y %H:%i:%S') from opencall

and getting this error:

[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' at line 1

Bizarre!!!

 

[feherke]

Hi

Should not that be opencall[red]1[/red] ?


Feherke.

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

 

[AnnetteB1305]

Well it is in crystal for some reason it has named the database opencall1 in crystal however the actual database name is opencall.

 

[feherke]

Hi

You are not confusing the database and the table name right ?

Sorry, I have no more idea. That last query posted by you works for me in MySQL 5.1.41 on Linux :

[blue]mysql>[/blue] select closedate as original_string, str_to_date(closedate,'%d/%m/%Y %H:%i:%S') as converted_to_date, str_to_date(closedate,'%d/%m/%Y %H:%i:%S')>(date_add(curdate(),interval -8 day)) as compared from opencall;
+---------------------+---------------------+----------+
| original_string     | converted_to_date   | compared |
+---------------------+---------------------+----------+
| 01/02/0003 04:05:06 | 0003-02-01 04:05:06 |        0 |
+---------------------+---------------------+----------+
1 row in set (0.00 sec)

Feherke.

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

 

[feherke]

Hi

Just one more thing to check : what is the [tt]describe[/tt] command saying ? For my test table :

[blue]mysql>[/blue] describe opencall closedate;
+-----------+--------------+------+-----+---------+-------+
| Field     | Type         | Null | Key | Default | Extra |
+-----------+--------------+------+-----+---------+-------+
| closedate | varchar(100) | YES  |     | NULL    |       |
+-----------+--------------+------+-----+---------+-------+
1 row in set (0.01 sec)

Feherke.

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

 

[AnnetteB1305]

+-----------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+--------------+------+-----+---------+-------+
| closedate | varchar(20 ) | YES | | NULL | |
+-----------+--------------+------+-----+---------+-------+

 

[AnnetteB1305]

what about a convert function would that work? and do you know the convert function in mysql

 

[feherke]

Hi

Convert for what ? For converting from varchar to datetime there is the str_to_date() function you are already using in that query.

By the way, have you tried that query outside of that Crystal ?


Feherke.

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

 

[AnnetteB1305]

Yes tried it in query analyser that was the last error I sent.

I meant the Convert( ) function Convert(closedate,date) or the cast function.

Just thinking it isn't because the closedate may be empty, is it?

 

[feherke]

Hi

For null and invalid date str_to_date() returns null, for empty string returns datetime 0000-00-00 00:00:00. So no, there should be no problem with those.


Feherke.

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

 

[AnnetteB1305]

04/11/2009 07:15:41 this is how closedate looks, so I've got enough expressions now haven't I.....don't get it

 

[vacunita]

As feherke said, you need to start breaking down the query. If you remove the date comparison part does it work? Or does it still throw an error?

I have a feeling the error is not in that section but some place before.

Start removing sections, doesn't matter if the results are not what you want, once you get to a point where the query runs add a portion back in and check again, do that until the error comes back.

You can also try your date conversion alone to make sure its right.

SELKCT str_to_date(closedate,"%d/%m/%Y") as convertedDate from opencall1;

----------------------------------
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]

Hello,

I've already done this as stated above. The error is occurring with SELECT str_to_date(closedate,"%d/%m/%Y") as convertedDate from opencall

and

SELECT str_to_date(closedate,"%d/%m/%Y %h:%i:%s") as convertedDate from opencall

 

[vacunita]

O.k, so what version of MYSQL do you have? Is it at all possible it doesn't have the functions required for the conversion and so is throwing an error?



----------------------------------
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]

Pardon my ignorance but how do I find out the version, I'm afraid I'm more SQL than MYSQL so struggling a bit with this one. Thanks in advance.

 

[vacunita]

SELECT version();

----------------------------------
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]

Someone is definitely getting at me....getting an error with that:-

[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 '()' at line 1

 

[vacunita]

If your mysql can;t even run th verion fcuntion there's somethng serisouly wrong with it.

O.k try:

SHOW variables LIKE "%version%";

----------------------------------
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]

that worked:-


protocol_version 10
version 4.0.16