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

 

[AnnetteB1305]

Hello,

I have found this on a forum as a workaround for str_to_date pre version 4.1.1 and have replaced their text field with my field:-

select DATE_FORMAT(CONCAT( RIGHT( closedate, length( closedate) - LOCATE('/', closedate , LOCATE('/', closedate ) + 1 ) ) , '-' ,
LEFT( closedate , LOCATE('/', closedate ) - 1 ) , '-', SUBSTRING( closedate , LOCATE('/', closedate )
+ 1, LOCATE('/', closedate , LOCATE('/', closedate ) + 1 ) - LOCATE('/', closedate) - 1 ) ) , '%d-%m-%y' ) from opencall

however my string is a dd/mm/yyyy hh:mm:ss so I'm not sure how I would get this to work? Any ideas?

When I run this as it is, it doesn't have an error but I just get 0 affected rows

Thanks in advance

Annette

 

[feherke]

Hi

That is hard to convert to handle time too. If in your date and time the values are always represented on 4 ( year ) and 2 ( the others ) digits, substring() and concat() is enough :

[b]select[/b]
concat[teal]([/teal]substring[teal]([/teal]closedate[teal],[/teal][purple]7[/purple][teal],[/teal][purple]4[/purple][teal]),[/teal][green][i]'-'[/i][/green][teal],[/teal]substring[teal]([/teal]closedate[teal],[/teal][purple]4[/purple][teal],[/teal][purple]2[/purple][teal]),[/teal][green][i]'-'[/i][/green][teal],[/teal]substring[teal]([/teal]closedate[teal],[/teal][purple]1[/purple][teal],[/teal][purple]2[/purple][teal]),[/teal][green][i]' '[/i][/green][teal],[/teal]substring[teal]([/teal]closedate[teal],[/teal][purple]12[/purple][teal],[/teal][purple]2[/purple][teal]),[/teal][green][i]':'[/i][/green][teal],[/teal]substring[teal]([/teal]closedate[teal],[/teal][purple]15[/purple][teal],[/teal][purple]2[/purple][teal]),[/teal][green][i]':'[/i][/green][teal],[/teal]substring[teal]([/teal]closedate[teal],[/teal][purple]18[/purple][teal],[/teal][purple]2[/purple][teal]))>[/teal]date_add[teal]([/teal]curdate[teal](),[/teal]interval [teal]-[/teal][purple]8[/purple] day[teal])[/teal]

[b]from[/b] opencall

Feherke.

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

 

[AnnetteB1305]

I don't really require the time....anyway tried this but just getting a column of 0's.

 

[feherke]

Hi

just getting a column of 0's.

That means none of the values is from the last 8 days. Is that not correct ?


Feherke.

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

 

[AnnetteB1305]

Actually you may have a point this is a test database. I'll get it updated and run it again.

Lol this whole report has turned my brain to mush...apologies will let you know how I get on but let me say Thank you for your help.

 

[AnnetteB1305]

Actually just ran some queries and there are lots of rows where the closedate is in the last 8 days and above.

 

[feherke]

Hi

Well, it still works form me :

[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, concat(substring(closedate,7,4),'-',substring(closedate,4,2),'-',substring(closedate,1,2),' ',substring(closedate,12,2),':',substring(closedate,15,2),':',substring(closedate,18,2)) as processed_40, concat(substring(closedate,7,4),'-',substring(closedate,4,2),'-',substring(closedate,1,2),' ',substring(closedate,12,2),':',substring(closedate,15,2),':',substring(closedate,18,2))>date_add(curdate(),interval -8 day) as compared_40 from opencall;
+---------------------+---------------------+----------+---------------------+-------------+
| original_string     | converted_to_date   | compared | processed_40        | compared_40 |
+---------------------+---------------------+----------+---------------------+-------------+
| 01/02/0003 04:05:06 | 0003-02-01 04:05:06 |        0 | 0003-02-01 04:05:06 |           0 |
| NULL                | NULL                |     NULL | NULL                |        NULL |
|                     | 0000-00-00 00:00:00 |        0 | -- ::               |           0 |
| foo bar             | NULL                |     NULL | r- b-fo ::          |           0 |
|                     | NULL                |     NULL | --  ::              |           0 |
| 24/08/2011 05:06:07 | 2011-08-24 05:06:07 |        1 | 2011-08-24 05:06:07 |           1 |
| 20/08/2011 06:07:08 | 2011-08-20 06:07:08 |        1 | 2011-08-20 06:07:08 |           1 |
| 30/08/2011 07:08:09 | 2011-08-30 07:08:09 |        1 | 2011-08-30 07:08:09 |           1 |
+---------------------+---------------------+----------+---------------------+-------------+

Note that my solution does not convert the string to date. If you have some local setting, that may break it.

One last idea, this should cure the locale setting problem :

[b]select[/b]
concat[teal]([/teal]substring[teal]([/teal]closedate[teal],[/teal][purple]7[/purple][teal],[/teal][purple]4[/purple][teal]),[/teal][green][i]'-'[/i][/green][teal],[/teal]substring[teal]([/teal]closedate[teal],[/teal][purple]4[/purple][teal],[/teal][purple]2[/purple][teal]),[/teal][green][i]'-'[/i][/green][teal],[/teal]substring[teal]([/teal]closedate[teal],[/teal][purple]1[/purple][teal],[/teal][purple]2[/purple][teal]),[/teal][green][i]' '[/i][/green][teal],[/teal]substring[teal]([/teal]closedate[teal],[/teal][purple]12[/purple][teal],[/teal][purple]2[/purple][teal]),[/teal][green][i]':'[/i][/green][teal],[/teal]substring[teal]([/teal]closedate[teal],[/teal][purple]15[/purple][teal],[/teal][purple]2[/purple][teal]),[/teal][green][i]':'[/i][/green][teal],[/teal]substring[teal]([/teal]closedate[teal],[/teal][purple]18[/purple][teal],[/teal][purple]2[/purple][teal]))>[/teal]date_format[teal]([/teal]date_add[teal]([/teal]curdate[teal](),[/teal]interval [teal]-[/teal][purple]8[/purple] day[teal]),[/teal][green][i]'%Y-%m-%d %H:%i:%S'[/i][/green][teal])[/teal]

[b]from[/b] opencall

Feherke.

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

 

[AnnetteB1305]

I'm getting a column with some zeros, nulls and "1"s now and it appears that obviously the rows with 1s are within date range required. So how do I just get the 1s and is this correct should this be the output?

 

[feherke]

Hi

Yes, that is correct. In MySQL the boolean values are displayed as 0 ( false ) and 1 ( true ). The null means some of the values in the formula where null. In a condition null will also evaluate to false. If you want to get 0 instead of null, use the coalesce() function on them.


Feherke.

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

 

[AnnetteB1305]

Great so going back to my original command is this correct:-

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
(concat(substring(closedate,7,4),'-',substring(closedate,4,2),'-',substring(closedate,1,2),' ',
substring(closedate,12,2),':',substring(closedate,15,2),':',substring(closedate,18,2))
>date_format(date_add(curdate(),interval -8 day),'%Y-%m-%d %H:%i:%S')
)
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
(concat(substring(closedate,7,4),'-',substring(closedate,4,2),'-',substring(closedate,1,2),' ',
substring(closedate,12,2),':',substring(closedate,15,2),':',substring(closedate,18,2))
>date_format(date_add(curdate(),interval -8 day),'%Y-%m-%d %H:%i:%S')
)
ORDER BY `opencall`.`priority`


Thanks

Annette

 

[AnnetteB1305]

Or should I say:-

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
(concat(substring(`opencall`.`closedate`,7,4),'-',substring(`opencall`.`closedate`,4,2),'-',substring(`opencall`.`closedate`,1,2),' ',
substring(`opencall`.`closedate`,12,2),':',substring(`opencall`.`closedate`,15,2),':',substring(`opencall`.`closedate`,18,2))
>date_format(date_add(curdate(),interval -8 day),'%Y-%m-%d %H:%i:%S')
)
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
(concat(substring(`opencall`.`closedate`,7,4),'-',substring(`opencall`.`closedate`,4,2),'-',substring(`opencall`.`closedate`,1,2),' ',
substring(`opencall`.`closedate`,12,2),':',substring(`opencall`.`closedate`,15,2),':',substring(`opencall`.`closedate`,18,2))
>date_format(date_add(curdate(),interval -8 day),'%Y-%m-%d %H:%i:%S')
)
ORDER BY `opencall`.`priority`

 

[feherke]

Hi

Personally I prefer the later. I mean, if there are more than one tables involved I prefix the field names with table or table alias name. For example if somewhere in the future another table, let us say updatedb, will be enhanced with a field also called closedate, then you will not have to rewrite all your old queries to avoid ambiguous field reference errors.


Feherke.

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

 

[AnnetteB1305]

Sorry yes I realised what I'd done and corrected it but does this query only select the dates that fit the criteria as my report is still not working. I mean to say the command is working in that I don't get error message but it's still not working correctly on my report. Sorry to be a pain, I'm just at the end of my tether with it and just want to ensure I've got this correct before I try anything else.

Thanks,

Annette

 

[AnnetteB1305]

or should I have the =1 in the command i.e. 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
(concat(substring(`opencall1`.`closedate`,7,4),'-',substring(`opencall1`.`closedate`,4,2),'-',substring(`opencall1`.`closedate`,1,2),' ',
substring(`opencall1`.`closedate`,12,2),':',substring(`opencall1`.`closedate`,15,2),':',substring(`opencall1`.`closedate`,18,2))
>date_format(date_add(curdate(),interval -8 day),'%Y-%m-%d %H:%i:%S')
) =1
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
(concat(substring(`opencall1`.`closedate`,7,4),'-',substring(`opencall1`.`closedate`,4,2),'-',substring(`opencall1`.`closedate`,1,2),' ',
substring(`opencall1`.`closedate`,12,2),':',substring(`opencall1`.`closedate`,15,2),':',substring(`opencall1`.`closedate`,18,2))
>date_format(date_add(curdate(),interval -8 day),'%Y-%m-%d %H:%i:%S')
) =1
ORDER BY `opencall1`.`priority`


Although I've tried this now and report is still not working as I'd like!

But which do you think is best for the MySql?

 

[feherke]

Hi

I find one logic error. The parentheses in the [tt]where[/tt] clause were removed during the modifications :

[b]WHERE[/b]  [green][i]`opencall1`[/i][/green][teal].[/teal][green][i]`priority`[/i][/green][teal]<>[/teal][green][i]'NL-MDIS SALES OFF SEND'[/i][/green]
[b]and[/b] [highlight][teal]([/teal][/highlight] [green][i]`opencall1`[/i][/green][teal].[/teal][green][i]`closedate`[/i][/green][teal]=[/teal][green][i]''[/i][/green] [b]or[/b]
[teal]([/teal]concat[teal]([/teal]substring[teal]([/teal][green][i]`opencall`[/i][/green][teal].[/teal][green][i]`closedate`[/i][/green][teal],[/teal][purple]7[/purple][teal],[/teal][purple]4[/purple][teal]),[/teal][green][i]'-'[/i][/green][teal],[/teal]substring[teal]([/teal][green][i]`opencall`[/i][/green][teal].[/teal][green][i]`closedate`[/i][/green][teal],[/teal][purple]4[/purple][teal],[/teal][purple]2[/purple][teal]),[/teal][green][i]'-'[/i][/green][teal],[/teal]substring[teal]([/teal][green][i]`opencall`[/i][/green][teal].[/teal][green][i]`closedate`[/i][/green][teal],[/teal][purple]1[/purple][teal],[/teal][purple]2[/purple][teal]),[/teal][green][i]' '[/i][/green][teal],[/teal]
substring[teal]([/teal][green][i]`opencall`[/i][/green][teal].[/teal][green][i]`closedate`[/i][/green][teal],[/teal][purple]12[/purple][teal],[/teal][purple]2[/purple][teal]),[/teal][green][i]':'[/i][/green][teal],[/teal]substring[teal]([/teal][green][i]`opencall`[/i][/green][teal].[/teal][green][i]`closedate`[/i][/green][teal],[/teal][purple]15[/purple][teal],[/teal][purple]2[/purple][teal]),[/teal][green][i]':'[/i][/green][teal],[/teal]substring[teal]([/teal][green][i]`opencall`[/i][/green][teal].[/teal][green][i]`closedate`[/i][/green][teal],[/teal][purple]18[/purple][teal],[/teal][purple]2[/purple][teal]))[/teal]
[teal]>[/teal]date_format[teal]([/teal]date_add[teal]([/teal]curdate[teal](),[/teal]interval [teal]-[/teal][purple]8[/purple] day[teal]),[/teal][green][i]'%Y-%m-%d %H:%i:%S'[/i][/green][teal])[/teal]
[teal])[/teal] [highlight][teal])[/teal][/highlight]

Feherke.

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