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 <p...@pole-conseils.com> > Hi jerome, > > > > What about the from_unixtime and unix_timestamp Udf ? > > > > > > from_unixtime() which accept bigint > > > > my 2 cents > > > > Paul > > > > *De :* Nitin Pawar [mailto:nitinpawar...@gmail.com] > *Envoyé :* mercredi 3 juillet 2013 09:29 > *À :* user@hive.apache.org > *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 < > verdier.jerom...@gmail.com> 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 <sprag...@gmail.com> > > 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 <verdier.jerom...@gmail.com> > 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 verdier.jerom...@gmail.com