Century date

[nareshthavidishetty]

Hi,

We are having date as

(Year((TODAY()-1))-1900)*10000 + Month((TODAY()-1))*100+ day(Today()-1) = 1161109

Here we need the month start like = 1161101

Please let met me know how to get this.

Thanks..

[rubenmarin · Accepted solution]

Hi naresh, so it's only set the first day of the month?, wouldn't be easier to set a fixed day '1'?:

(Year((TODAY()-1))-1900)*10000 + Month((TODAY()-1))*100+ 1

[Anil_Babu_Samineni]

You may try this way,

=(Year((TODAY()-1))-1900)*10000 + Month((TODAY()-1))*100+ MonthStart(day(Today()))-1

[Oleg_Troyansky]

Sorry to discourage you guys, but this formula is wrong... Use Ruben's suggestion...

[Anil_Babu_Samineni]

Oleg, Why So it is wrong? Would i expect reason. so that i can gain bit more

[sunny_talwar]

Although, I agree with Oleg that Ruben's method is probably the best, but you have incorrect order of things. This might make more sense:

=(Year((TODAY()-1))-1900)*10000 + Month((TODAY()-1))*100+ Day(MonthStart(Today()))

Here you are first calculating MonthStart for today and then finding what day it is. Fortunately, MonthStart is always 1 and hence Ruben's method is more straight forward

[Oleg_Troyansky]

Yeah, what Sunny said... To be absolutely precise, you should also subtract 1 from Today() as the rest of the formula does, but not from the final result as you suggested:

=(Year((TODAY()-1))-1900)*10000 + Month((TODAY()-1))*100+ Day(MonthStart(Today()-1))

[MarcoWedel]

Hi,

maybe you could create your century date as dual value using your required format, yet still keeping the underlying numerical value to be able to apply date functions to it:

LOAD *,
    Dual((Div(Year(Date),100)-19)&Date(Date,'YYMMDD'),Date) as CenturyDate,
    Dual((Div(Year(Date),100)-19)&Date(Date,'YYMM01'),MonthStart(Date)) as CenturyDateMonthStart;
LOAD Date(RecNo()+1) as Date
AutoGenerate Today();

regards

Marco

[rwunderlich]

Yet another option

=num#(Date(Addmonths(MonthStart(today()),-1900*12),'YYYMMDD'))

-Rob

[johnw]

I'm very happy you've kept the underlying numeric value of CenturyDate the same as a regular date. If it were my application, I'd also want to be able to make it as easy as possible to use date functions on any century date, and get back something in century date format. So to expand on your idea, I'm thinking a formatting variable.

SET cd = dual(year($1)-1900&date($1,'MMDD'),$1);

table1:
LOAD $(cd(Date)) as CenturyDate;
LOAD date(recno()+1) as Date
AUTOGENERATE today();

Then we can $(cd(monthstart(CenturyDate))) for the first of the month, still a date, but in the correct format, and we can do that in a chart or in the script as desired.