Hi,
Thanks for your help.
I resolve the problem by changing my variable in_co_an_mois into a normal
date format, and extract month and year by using apporopriate functions :
year() and month().
But, i have a new question :
the PL/SQL script i have to translate in hive is written like this :
SELECT min(dt_jour)
INTO D_debut_semaine
FROM ods.calendrier
WHERE co_an_semaine = in_co_an_sem;
I have to record a value in a variable (here : D_debut_semaine) to use this
later.
Is there a way to do this in Hive ?
2013/7/3 Paul COURTOIS <[email protected]>
> Hi jerome,
>
>
>
> What about the from_unixtime and unix_timestamp Udf ?
>
>
>
>
>
> from_unixtime() which accept bigint
>
>
>
> my 2 cents
>
>
>
> Paul
>
>
>
> *De :* Nitin Pawar [mailto:[email protected]]
> *Envoyé :* mercredi 3 juillet 2013 09:29
> *À :* [email protected]
> *Objet :* Re: Dealing with differents date format
>
>
>
> easiest way in this kind would be write up a small udf.
>
> As Stephen suggested, its just a number so you can do maths to extract
> year and month out of the number and then do the comparison.
>
>
>
> also 201307 is not a supported date format anywhere as per my knowledge
>
>
>
> On Wed, Jul 3, 2013 at 12:55 PM, Jérôme Verdier <
> [email protected]> wrote:
>
> Hi Stephen,
>
> Thanks for your reply.
>
>
>
> The problem is that my input date is this : in_co_an_mois (format :
> YYYYMM, integer), for example, this month, we have 201307
>
> and i have to deal with this date : add one month, compare to over date,
> etc...
>
> The problem is that apparently, there is no way to do this, because Hive
> can't deal with this type of data because it's not a date format.
>
> For hive, this is just a number.
>
> Hive can deal with this : 1970-01-01 00:00:00, or this : 2009-03-20, but
> not with this unusual format : 201307.
>
> Thanks.
>
>
>
>
>
>
>
> 2013/7/2 Stephen Sprague <[email protected]>
>
> not sure i fully understand your dilemma. have you investigated any of
> the date functions listed here?
>
>
> https://cwiki.apache.org/confluence/display/Hive/LanguageManual+UDF#LanguageManualUDF-DateFunctions
>
> seems to me you could pull the year and month from a date. or if you have
> an int then do some arithmetic to get the year and month. eg. year =
> floor( <your int>/10000) and month = cast( <your int> % 100 as int) [% ==
> modulus operator]
>
> or am i not even answering your question?
>
>
>
>
>
> On Tue, Jul 2, 2013 at 2:42 AM, Jérôme Verdier <[email protected]>
> wrote:
>
> Hi,
>
> i trying to translate some PL/SQL script in HiveQL, and dealing with
> unusual date format.
>
> i added a variable in my hive script : '${hiveconf:in_co_an_mois}' which
> is a year/month date format, like this : 201307 (INT format).
>
> I would like to transform this in date format, because i have to increment
> this (add one month/one year).
>
> Is there a way to do this in hive ?
>
> Thanks.
>
>
>
>
> --
> *Jérôme*
>
>
>
>
>
>
>
>
>
>
>
>
>
> --
> Nitin Pawar
>
--
*Jérôme VERDIER*
06.72.19.17.31
[email protected]
