date_sub/last_day

2 posts / 0 new
Last post
vdhanda
vdhanda's picture
Offline
Last seen: 23 hours 2 min ago
Joined: Dec 3 2010
Expert Boarder

Posts: 49

Vidur Dhanda
date_sub/last_day

Wondering if anyone can share a workaround for using last_day and date_sub in an update statement. infinidb doesn't support it yet and I need that functionality.

I need to execute the following two updates:
#yyyymm_dt is values of the form 201006 meaning June 2010
#And I want to set _as_of_date to the last day of the previous month referred to by #yyyymm_dt. May 31, 2010 in the example above.
update table1 set as_of_date = date_sub(str_to_date(yyyymm_dt*100+1, '%Y%m%d'), interval 1 day);
#Then I want to update as_of_date to the last day of the month of the greatest of three #other dates
update table1 set as_of_date = last_day(greatest(
ifnull(date_1, as_of_date),
ifnull(date_2, as_of_date),
ifnull(date_3, as_of_date),
as_of_date));

Thanks,
Vidur

davidhill
davidhill's picture
Offline
Last seen: 22 hours 12 min ago
Joined: Oct 27 2009
Administrator

Posts: 561

david hill
Re:date_sub/last_day

Here is an example couple of queries that will do what you need:

To get last day of previous month:

select date_sub(convert(yyyymm_dt *100+1, date), INTERVAL 1 DAY) from table1;

To get last day of current month:

select date_sub( date_format(as_of_date + interval 1 month,'%Y-%m-01') , INTERVAL 1 DAY) a from table1;

David H
Calpont