Hi All,

A simple question on dates, how can I convert integer column dateint in
"yyyymmdd" format to string column in "yyyy-mm-dd" format in hive.

Thanks in advance.

Sonia

I don't have the language manual in frot of me, but I would suggest casting dateint to string, then using the unix_timestamp function. From_unixtime should get it back into a date time string

Matt

On Jun 25, 2012, at 8:05 PM, sonia gehlot <[EMAIL PROTECTED]> wrote:

> Hi All,
>
> A simple question on dates, how can I convert integer column dateint in "yyyymmdd" format to string column in "yyyy-mm-dd" format in hive.
>
> Thanks in advance.
>
> Sonia

https://cwiki.apache.org/Hive/languagemanual-udf.html#LanguageManualUDF-DateFunctions

--
Alexander Alten-Lorenz
http://mapredit.blogspot.com
German Hadoop LinkedIn Group: http://goo.gl/N8pCF

On Jun 26, 2012, at 2:05 AM, sonia gehlot wrote:

> Hi All,
>
> A simple question on dates, how can I convert integer column dateint in
> "yyyymmdd" format to string column in "yyyy-mm-dd" format in hive.
>
> Thanks in advance.
>
> Sonia

More specific, you need to use three functions in a row:
1) Use unix_timestamp(string date, string pattern) to convert the date
variable, say dt, which you get from a hive query
2) Then use from_unixtime(bigint unixtime) to convert the bigint returned
in 1)
3) The use to_date(string timestamp) to convert date-and-time string
returned in 2) to just date.

Long story short: Let's say dt is the date returned from your hive query,
then this would do the job:
to_date(from_unixtime(unix_timestamp(dt,'yyyyMMdd' )  ) )

Keep in mind that MM is capital, in distinguishing from little mm, which is
used for minutes, so be careful!!
Also, modify the format yyyyMMdd so that it matches the format of the
timestamp returned by your hive query.
On Mon, Jun 25, 2012 at 10:35 PM, alo alt <[EMAIL PROTECTED]> wrote:

>
> https://cwiki.apache.org/Hive/languagemanual-udf.html#LanguageManualUDF-DateFunctions
>
> --
> Alexander Alten-Lorenz
> http://mapredit.blogspot.com
> German Hadoop LinkedIn Group: http://goo.gl/N8pCF
>
> On Jun 26, 2012, at 2:05 AM, sonia gehlot wrote:
>
> > Hi All,
> >
> > A simple question on dates, how can I convert integer column dateint in
> > "yyyymmdd" format to string column in "yyyy-mm-dd" format in hive.
> >
> > Thanks in advance.
> >
> > Sonia
>
>

Thanks everyone,

This worked for me: from_unixtime(unix_timestamp(dt,'yyyyMMdd' ),
'yyyy-MM-dd').

-Sonia

On Mon, Jun 25, 2012 at 11:18 PM, VanHuy Pham <[EMAIL PROTECTED]> wrote:

> More specific, you need to use three functions in a row:
> 1) Use unix_timestamp(string date, string pattern) to convert the date
> variable, say dt, which you get from a hive query
> 2) Then use from_unixtime(bigint unixtime) to convert the bigint returned
> in 1)
> 3) The use to_date(string timestamp) to convert date-and-time string
> returned in 2) to just date.
>
> Long story short: Let's say dt is the date returned from your hive query,
> then this would do the job:
> to_date(from_unixtime(unix_timestamp(dt,'yyyyMMdd' )  ) )
>
> Keep in mind that MM is capital, in distinguishing from little mm, which
> is used for minutes, so be careful!!
> Also, modify the format yyyyMMdd so that it matches the format of the
> timestamp returned by your hive query.
>
>
> On Mon, Jun 25, 2012 at 10:35 PM, alo alt <[EMAIL PROTECTED]>wrote:
>
>>
>> https://cwiki.apache.org/Hive/languagemanual-udf.html#LanguageManualUDF-DateFunctions
>>
>> --
>> Alexander Alten-Lorenz
>> http://mapredit.blogspot.com
>> German Hadoop LinkedIn Group: http://goo.gl/N8pCF
>>
>> On Jun 26, 2012, at 2:05 AM, sonia gehlot wrote:
>>
>> > Hi All,
>> >
>> > A simple question on dates, how can I convert integer column dateint in
>> > "yyyymmdd" format to string column in "yyyy-mm-dd" format in hive.
>> >
>> > Thanks in advance.
>> >
>> > Sonia
>>
>>
>