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
