Home | About | Sematext search-lucene.com search-hadoop.com
 Search Hadoop and all its subprojects:

Switch to Threaded View
Hive >> mail # user >> Date format - any easier way


Copy link to this message
-
Re: Date format - any easier way
may be something like this will work

can you try using concat(split(date_sub(),"-")))

split returns the array and then you can concat them as you want

if this does not work for you, writing a simple UDF is easy as well

Thanks,
nitin
On Tue, May 15, 2012 at 6:56 PM, Zoltán Tóth-Czifra <
[EMAIL PROTECTED]> wrote:

>  Nitin,
>
>  Thank you. As you see below I know and use this function. My problem is
> that it doesn't give YYYYMMDD format, but YYYY-MM-DD instead, and
> formatting is not trivial as you can see it too.
>
>  ------------------------------
> *From:* Nitin Pawar [[EMAIL PROTECTED]]
> *Sent:* Tuesday, May 15, 2012 3:24 PM
> *To:* [EMAIL PROTECTED]
> *Subject:* Re: Date format - any easier way
>
>  you may want to have a look at this function
>
>    date_sub(string startdate, int days) Subtract a number of days to
> startdate: date_sub('2008-12-31', 1) = '2008-12-30'
> On Tue, May 15, 2012 at 6:41 PM, Zoltán Tóth-Czifra <
> [EMAIL PROTECTED]> wrote:
>
>>  Hi guys,
>>
>>  Thanks you very much in advance for your help.
>>
>>  My problem in short is getting the date for yesterday in a YYYYMMDD
>> format. As I use this format for partitions, I need this format in quite
>> some queries.
>>
>>  So far I have this:
>>
>>  concat(
>> year( date_sub( to_date( from_unixtime( unix_timestamp() ) ), 1 ) ),
>> CASE
>> WHEN month( date_sub( to_date( from_unixtime( unix_timestamp() ) ), 1 ) )
>> < 10
>> THEN concat( '0', month( date_sub( to_date( from_unixtime(
>> unix_timestamp() ) ), 1 ) ) )
>> ELSE trim( month( date_sub( to_date( from_unixtime( unix_timestamp() ) ),
>> 1 ) ) )
>> END,
>> CASE
>> WHEN day( date_sub( to_date( from_unixtime( unix_timestamp() ) ), 1 ) ) <
>> 10
>> THEN concat( '0', day( date_sub( to_date( from_unixtime( unix_timestamp()
>> ) ), 1 ) ) )
>> ELSE trim(day( date_sub( to_date( from_unixtime( unix_timestamp() ) ), 1
>> ) ) )
>> END
>> );
>>
>>
>>  ...but it seems to be a bit crazy, especially if you have to repeat it
>> in hundreds of queries. Is there any other (better) way to get this format
>> from yesterday? - there has to be. As I can't use local user variables nor
>> macros whatsoever, I need to repeat myself a lot here. If there is no other
>> way, probably I need to change my partitions.
>>
>>  Any ideas are appreciated. Thank you!
>>
>>  Zoltan
>>
>
>
>
>  --
> Nitin Pawar
>
>
--
Nitin Pawar