You are viewing a plain text version of this content. The canonical link for it is here.

Posted to user@phoenix.apache.org by Michael McAllister <mm...@homeaway.com> on 2015/06/22 17:07:05 UTC

TO_DATE function playing up?

Hi everyone

New user, first time post. I have a question about the TO_DATE function seeming to misbehave. I issue the following SQL statement:-

SELECT TO_DATE('2015-06-22','yyyy-MM-DD')
FROM system.catalog
LIMIT 1;

I get the following response:-

2015-01-21

Similarly, when I issue the following SQL statement:-

select TO_DATE('2015-05-12','yyyy-MM-DD')
FROM system.catalog
limit 1;

I get the following response:-

2015-01-11

My issue is not with the day being returned - I understand what’s going on there. My issue is with the MONTH that’s being returned.

Interestingly, things are OK if I issue the following SQL statement:-

SELECT TO_DATE('22 Jun 2015','d MMM yyyy')
FROM system.catalog
limit 1;

I get the following response:-

2015-06-21

Screenshot is attached. Phoenix version is 4.2 on HDP 2.2.

Regards

Mike

[cid:908CEE1D-8901-46B1-B1D4-FF0E5BDF7E12@wvrgroup.internal]

Re: TO_DATE function playing up?

Posted by Michael McAllister <mm...@homeaway.com>.

Thanks for the help Gabriel, I really appreciate it. That did the trick!

Regards

Mike

On Jun 22, 2015, at 10:38 AM, Gabriel Reid <ga...@gmail.com>> wrote:

Hi Michael,

Thanks for the very detailed explanation of your scenario.

I believe the issue is in your date format format string (yyyy-MM-DD). According to the Joda Time docs[1], "D" is the format specifier for day of year (and not day of month). If you use the format string "yyyy-MM-dd", things should work fine (similar to what you saw in your last example above).

- Gabriel


1. http://www.joda.org/joda-time/apidocs/org/joda/time/format/DateTimeFormat.html

On Mon, Jun 22, 2015 at 5:07 PM Michael McAllister <mm...@homeaway.com>> wrote:
Hi everyone

New user, first time post. I have a question about the TO_DATE function seeming to misbehave. I issue the following SQL statement:-

SELECT TO_DATE('2015-06-22','yyyy-MM-DD')
FROM system.catalog
LIMIT 1;

I get the following response:-

2015-01-21

Similarly, when I issue the following SQL statement:-

select TO_DATE('2015-05-12','yyyy-MM-DD')
FROM system.catalog
limit 1;

I get the following response:-

2015-01-11

My issue is not with the day being returned - I understand what’s going on there. My issue is with the MONTH that’s being returned.

Interestingly, things are OK if I issue the following SQL statement:-

SELECT TO_DATE('22 Jun 2015','d MMM yyyy')
FROM system.catalog
limit 1;

I get the following response:-

2015-06-21

Screenshot is attached. Phoenix version is 4.2 on HDP 2.2.

Regards

Mike

<1__homeaway_asthad011____ssh_.png>




<1__homeaway_asthad011____ssh_.png>

Re: TO_DATE function playing up?

Posted by Gabriel Reid <ga...@gmail.com>.

Hi Michael,

Thanks for the very detailed explanation of your scenario.

I believe the issue is in your date format format string (yyyy-MM-DD).
According to the Joda Time docs[1], "D" is the format specifier for day of
year (and not day of month). If you use the format string "yyyy-MM-dd",
things should work fine (similar to what you saw in your last example
above).

- Gabriel

1.
http://www.joda.org/joda-time/apidocs/org/joda/time/format/DateTimeFormat.html

On Mon, Jun 22, 2015 at 5:07 PM Michael McAllister <mm...@homeaway.com>
wrote:

>  Hi everyone
>
>  New user, first time post. I have a question about the TO_DATE function
> seeming to misbehave. I issue the following SQL statement:-
>
>  SELECT TO_DATE('2015-06-22','yyyy-MM-DD')
> FROM system.catalog
> LIMIT 1;
>
>  I get the following response:-
>
>  2015-01-21
>
>  Similarly, when I issue the following SQL statement:-
>
>  select TO_DATE('2015-05-12','yyyy-MM-DD')
> FROM system.catalog
> limit 1;
>
>  I get the following response:-
>
>  2015-01-11
>
>  My issue is not with the day being returned - I understand what’s going
> on there. My issue is with the MONTH that’s being returned.
>
>  Interestingly, things are OK if I issue the following SQL statement:-
>
>  SELECT TO_DATE('22 Jun 2015','d MMM yyyy')
> FROM system.catalog
> limit 1;
>
>  I get the following response:-
>
>  2015-06-21
>
>  Screenshot is attached. Phoenix version is 4.2 on HDP 2.2.
>
>  Regards
>
>  Mike
>
>
>
>
>
>