Date in not normalized string remote table

Databases
1

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:

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:

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:

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

once again, you seem to be making things way too hard for yourself :slight_smile:

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

3

many thanks for suggestion.

If try this you have right, the output is correct:

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

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

5

thanks, now I understand your suggestion and I’ve simplified my query:


Affected rows: 0
Time: 15.660ms

Sincerely