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!

Register Log in
  • Congratulations derfloh on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Need help with a date function in mysql

Status
Not open for further replies.
AnnetteB1305

AnnetteB1305

Technical User
Joined
Aug 17, 2011
Messages
166
Location
GB
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
 
Sort by date Sort by votes
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
 
Upvote 0 Downvote
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 :
Code: 
[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.
 
Upvote 0 Downvote
I don't really require the time....anyway tried this but just getting a column of 0's.
 
Upvote 0 Downvote
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.
 
Upvote 0 Downvote
Actually just ran some queries and there are lots of rows where the closedate is in the last 8 days and above.
 
Upvote 0 Downvote
Hi

Well, it still works form me :
Code: 
[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 :
Code: 
[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.
 
Upvote 0 Downvote
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?
 
Upvote 0 Downvote
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.
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
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`
 
Upvote 0 Downvote
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.
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
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?
 
Upvote 0 Downvote
Hi

I find one logic error. The parentheses in the [tt]where[/tt] clause were removed during the modifications :
Code: 
[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.
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

E
  • Locked
  • Question Question
MySQL calculator tools
Replies
0
Views
679
EdZzy
E
bharons
  • Locked
  • Question Question
function argument value, type or count is invalid
Replies
3
Views
938
bharons
bharons
bharons
  • Locked
  • Question Question
filtering data with vfp 8
Replies
0
Views
324
bharons
bharons
lupidol
  • Locked
  • Question Question
mysql cannot add rows with &quot;UNION&quot;
Replies
5
Views
996
Chris Miller
Chris Miller
lupidol
  • Locked
  • Question Question
Cannot create new table in mySQL
Replies
6
Views
1K
lupidol
lupidol

Part and Inventory Search

Sponsor

Back
Top