Generate date

[Former Member]

Hi All,

I'm using BODI 3.1 and Oracle 11g as my backend, how do I generate date from start of month to end of month in designer as specified below

DATE

'2014/10/01 12:00:00' 

                  ---

                  --- 

'2014/10/31 12:00:00'

Any help would be greatly appreciated.

Thanks.

[Former Member · Accepted solution]

I think you can use date generation transform. Not sure its part of 3.1 check this link.

Date Generation Transform - Enterprise Information Management - SCN Wiki

[Former Member · Accepted solution]

Thanks for the reply Arun,

I think date generation transform works for me, but I want to convert that date to following format

"2014/10/01 12:00:00"

Any inputs on this would be very helpful.

Thanks.

[mageshwaran_subramanian · Accepted solution]

You can keep your target column's data type to date time ,then code something like this.

to_char( Date_Generation.DI_GENERATED_DATE,'YYYY/MM/DD 12:00:00')

but your result would look something like this : 2014/10/01 12:00:00:000000000 (with the milliseconds)

If you do not want the milliseconds then you may need to keep your target column's data type as varchar and play around with the string functions to get rid of the milliseconds

[Former Member · Accepted solution]

Thank you for your reply,

I dont think its working, I applied the logic as you mentioned but my output is

"2014/10/01 00:00:00"

Thanks.

[former_member187605 · Accepted solution]

to_char( Date_Generation.DI_GENERATED_DATE,'YYYY/MM/DD)||' 12:00:00'

[Former Member · Accepted solution]

to_char(to_date(fieldname,'MM/DD/YYYY HH:MM:SS'),'YYYY/MM/DD HH:MM:SS')

[mageshwaran_subramanian]

If you want to generate rows with subsequent dates , then you can achieve that with previous_row_value() function.Set your first row date as 2014/10/01 and for the next row use previous_row_value() and increment the date by 1 and so on.Have a look at this.

Access to Previous Row Values - Enterprise Information Management - SCN Wiki

[former_member211387]

Hi

Are you trying to load dates for a full month into a table with 12:00:00 as the time pat of the date?

Can you please give a bit more detail on what you are going to load? Just dates??? or dates with other columns?? .....

kind regards ¨

Raghu

[Former Member]

Thanks for your reply Raghu,

Yes I'm trying to load full months date into a table with 12:00:00 as time stamp. I will be using to this date column to compare with another table date column to extract the data, dates with other columns

Thanks.