What about wrapping it in regexp_replace(..., "[-]", "") ? It may not be the cleanest, but I'd recommend passing variables from the shell :)
Matt Tucker From: Zoltán Tóth-Czifra [mailto:zoltan.tothczi...@softonic.com] Sent: Tuesday, May 15, 2012 9:27 AM To: user@hive.apache.org Subject: RE: Date format - any easier way 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 [nitinpawar...@gmail.com] Sent: Tuesday, May 15, 2012 3:24 PM To: user@hive.apache.org<mailto:user@hive.apache.org> 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 <zoltan.tothczi...@softonic.com<mailto:zoltan.tothczi...@softonic.com>> 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
