SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Evangelist Miguel61's Avatar
    Join Date
    Mar 2008
    Posts
    402
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)

    Date in not normalized string remote table

    Hello guys, I need your help.

    Are looking for easy solution, can you help me?

    In remote dotable mysql I have this not normalized string in the field start:
    Code:
    1/1/2013 14:29:00
    I need for this string calculate the WEEK and WEEKDAY.

    I tried this query version, but the output WEEK and WEEKDAY is NULL:
    Code:
    SELECT
    	WEEK (
    		STR_TO_DATE(start, '%d/%m/%Y %H:%i'),
    		5
    	) AS strWeek,
    	weekday(
    		STR_TO_DATE(start, '%d/%m/%Y %H:%i')
    	) AS strWeekDay,
    	start AS start
    FROM
    	(
    		SELECT
    			STR_TO_DATE(
    				start,
    				'%d/%m/%Y %H:%i'
    			) AS start
    		FROM
    			dotable
    	) AS qx;
    I tried this version and the output WEEK and WEEKDAY is correct, but the query is long time:
    Code:
    SELECT
    	WEEK (
    		STR_TO_DATE(start, '%d/%m/%Y %H:%i'),
    		5
    	) AS strWeek,
    	weekday(
    		STR_TO_DATE(start, '%d/%m/%Y %H:%i')
    	) AS strWeekDay,
    	start AS start
    FROM
    	(
    		SELECT
    			REPLACE (
    
    				IF (
    					mid(
    
    						IF (
    							mid(start, 2, 1) = '/',
    							concat('0', start),
    							start
    						),
    						5,
    						1
    					) = '/',
    					concat(
    						mid(
    
    							IF (
    								mid(start, 2, 1) = '/',
    								concat('0', start),
    								start
    							),
    							1,
    							3
    						),
    						'0',
    						mid(
    
    							IF (
    								mid(start, 2, 1) = '/',
    								concat('0', start),
    								start
    							),
    							4,
    							100
    						)
    					),
    
    				IF (
    					mid(start, 2, 1) = '/',
    					concat('0', start),
    					start
    				)
    				),
    				'.',
    				':'
    			) AS start
    		FROM
    			dotable
    	) AS qx;

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by Miguel61 View Post
    I tried this query version, but the output WEEK and WEEKDAY is NULL:
    once again, you seem to be making things way too hard for yourself

    in the FROM clause, you perform STR_TO_DATE on the start column

    but then in the outer query, inside the WEEK and WEEKDAY functions, you do it again -- don't
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Evangelist Miguel61's Avatar
    Join Date
    Mar 2008
    Posts
    402
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    once again, you seem to be making things way too hard for yourself

    in the FROM clause, you perform STR_TO_DATE on the start column

    but then in the outer query, inside the WEEK and WEEKDAY functions, you do it again -- don't
    many thanks for suggestion.

    If try this you have right, the output is correct:
    Code:
    SELECT
    	WEEK (
    		STR_TO_DATE(start, '%d/%m/%Y %H:%i'),
    		5
    	) AS strWeek,
    	weekday(
    		STR_TO_DATE(start, '%d/%m/%Y %H:%i')
    	) AS strWeekDay,
    		FROM
    			dotable
    But I need a subquery because I have join in three different tables, when I try the same code in a subquery the output in null.

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by Miguel61 View Post
    when I try the same code in a subquery the output in null.
    in post #2 i said that if you're doing STR_TO_DATE in the subquery, then don't do it in the outer query
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Evangelist Miguel61's Avatar
    Join Date
    Mar 2008
    Posts
    402
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    in post #2 i said that if you're doing STR_TO_DATE in the subquery, then don't do it in the outer query
    thanks, now I understand your suggestion and I've simplified my query:
    Code:
    Affected rows: 0
    Time: 15.660ms
    Sincerely


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •