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> 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
