How Do I Trim Off TIME in a Date Object?

[system]

A different opinion:

Why would you have a LOV for dates anyway?
LOV’s for dates are of little value to the end-user (unless you have very specific dates you want the end-user to select from).

---

Andreas (BOB member since 2002-06-20)

[system]

digpen is nearly right but Dave has a point in not hardcoding the SQL. Try if the following helps…

Create a new object to_char(Date, ‘yyymmdd’) and change your orig object to to_char(Date, ‘mm/dd/yyyy’). Then in the LOV of the original object sort it by the new object. That way no hardcoding, u get the dates in order and no timestamp is shown!!

Did that make any sense at all… :crazy:

---

avaksi (BOB member since 2002-08-22)

[system]

@avaksi

Create a new object to_char(Date, ‘yyymmdd’) and change your orig object to to_char(Date, ‘mmddyyyy’). Then in the LOV of the original object sort it by the new object. That way no hardcoding, u get the dates in order and no timestamp is shown!!

Did that make any sense at all…

It makes sense, but will also kill any indexes on the date values. And since dates are very often used in conditions… which is the entire point of haing a LOV query afterall … then you want to be sure to have an index available for use if there is one.

---

Dave Rathbun (BOB member since 2002-06-06)

[system]

I do believe in Oracle (8.x or higher) one could create a function index on to_char(Date object, ‘String Format’)…still I am not favoring LOVs for Dates in general.

---

Andreas (BOB member since 2002-06-20)

[system]

Why would you have a LOV for dates anyway?

The table that holds the FULL_DATE and has values like 12/31/9999. So the dates are not natural…So the LOV is nice for them to see which potential dates they need to use.

---

David Smith (BOB member since 2002-07-10)

[system]

Dave,

You’re right about it being poor universe design, and it was a quick patch. For a “real” solution, you can create an LOV object and use its LOV.

To Create an LOV that appears like a date (minus the time) and sorts like a date, yet is a Character, use the following LOV SQL.

I built an object called INV_DT_LOV which is a character object.
The LOV Name is customized to: INV_DT
and the SQL is:

SELECT inv_dt
FROM(
   SELECT DISTINCT
               to_char(Sales.invoice_date,'mm/dd/yyyy') as inv_dt
   FROM sales
) order by to_date(inv_dt,'mm/dd/yyyy')

You have to use a sub-select, since you can’t apply the ORDER BY to the SELECT DISTINCT query.

I force this SQL to not regenerate, and build my LOV object.

For my INVOICE DATE object, I just use my Customized INV_DT LOV, built by the hidden INV_DT_LOV object.

Make sense? This prevents me from modifying my original object (other than changing the LOV name) and keeps my LOVs in a special LOV generating folder along with all of my other LOVs. So… If I ever need to changes LOVs, I don’t bother with the original objects, just the LOV object defs.

-RM

---

digpen (BOB member since 2002-08-15)

[system]

"You have to use a sub-select, since you can’t apply the ORDER BY to the SELECT DISTINCT query. "

Why can I not apply “ORDER BY” to the SELECT DISTINCT clause. I did it in the last project. Please tell me why, I might have to go back and correct something atleast e-mail them.
It was working fine there. Or are we talking about just the Date LOV in this context.
thanks,

---

bond1001 (BOB member since 2004-10-29)

[system]

Just to add to this topic, the way to use Digpen’s solution in SQL server.

Thnx Digipen

The main difference is having to use an alias in the sub select, in this case ‘a’…

select INVDate
from (
 select distinct convert(varchar(10),INVOICE_DATE,103) AS INVDate
 from dbo.INVOICE_DATE
 ) a
order by convert(datetime,INVDate,103)

---

Mak 1 (BOB member since 2005-01-06)

[system]

Okay, I am still not clear.
What code should go into the select box for the date object to trim the Time stamp?
DB: SQL server
BO Version: XI Rel 2

Thanks!

---

Minni (BOB member since 2005-10-29)

[system]

Create a new object to_char(Date, ‘yyymmdd’) and change your orig object to to_char(Date, ‘mm/dd/yyyy’). Then in the LOV of the original object sort it by the new object. That way no hardcoding, u get the dates in order and no timestamp is shown!!

I dont see any to_char function while defining any object in a Universe? Can anyone kindly tell me hwo can i achieve this? Do you want me to use a Derived Table?

thanks
namit

---

namitrs (BOB member since 2006-11-28)

[system]

@namitrs

I dont see any to_char function while defining any object in a Universe?

to_char() is an Oracle function. What database are you using?

---

Dave Rathbun (BOB member since 2002-06-06)

[system]

Hello Dave

Thanks for your reply. I know to_char is a f(x) in Oracle. But my DB is SQL Server 2000, so I know I have to use convert f(x).

What I wanted to know is where should I put that f(x).
The way I do that right now is - Go to Object Properties - Properties tab - select edit - and then in the SQL put -

SELECT DISTINCT
CONVERT(CHAR(10),dbo.PERSON.DATE_OF_LEAVING,105)  
FROM
  dbo.PERSON

Is this the correct way, or I should be using some other way. Mind you this way works now, cos now when i say ‘Display data’ it shows me in ‘ddmmyyyy’ format.

Thanks
Namit

---

namitrs (BOB member since 2006-11-28)

[system]

You don’t put all of the clauses of the SQL – just the phrase from the SELECT that would go before a comma:

CONVERT(CHAR(10),dbo.PERSON.DATE_OF_LEAVING,105)

If you click the >> button to the right of the Select box, Designer will even put you into a formula builder, where you can double-click functions and columns and other objects, rather than type them in. You might want to take a Designer class, or at least read through the Designer user manual.

---

Anita Craig (BOB member since 2002-06-17)

[system]

Hello Anita

I have tried that, but it does not seem to work.
Please refer both the attachments to see what I am talking about -

Thanks
Namit

---

namitrs (BOB member since 2006-11-28)

[system]

Hello

When I try this in SQL server 2000,

SELECT DISTINCT
CONVERT(datetime,(CONVERT(CHAR(10),DATE_OF_LEAVING,103)),103) 
FROM
  dbo.PERSON

it gives the data back as a date time (2004-07-16 00:00:00.000) which does not finally help me?

Can anyone help?

Namit

---

namitrs (BOB member since 2006-11-28)

[system]

You’re converting it back to Datetime – so it’s displaying the time portion for you.

Have you tried change the default format of the object to only display the date portion? That wouldn’t change the default of the object on any existing reports, but it would change it for new reports.

---

Anita Craig (BOB member since 2002-06-17)

[system]

You’re converting it back to Datetime – so it’s displaying the time portion for you.

If i dont change it back to datetime, it stays as char, and then I cannot use it in compariosn with a prompt. It says invalid comaprison between ‘char’ and ‘date’.

Have you tried change the default format of the object to only display the date portion? That wouldn’t change the default of the object on any existing reports, but it would change it for new reports.

Yes, I have set the Object format to ‘dd/mm/yyyy’ but it did not help.

namit

---

namitrs (BOB member since 2006-11-28)

[system]

Well, I haven’t yet built a universe on Oracle, so I’ll have to let someone who’s used Oracle datetime fields to chime in here.

---

Anita Craig (BOB member since 2002-06-17)

[system]

I solved this problem see my post of solution

How do I workaround custom dt LOV with hidden midnight TS

I did the Metzker method to create a Date LOV with no timestamps for my customers prompt. Testing as a report writer- I created a prompt -where my date between say- 2/1/2007 and 2/20/2007 and it does not return any rows from 2/20/2007. (and there is data present in the db) I discovered the reason for this when, instead I used hardcoded dates in the where condition- The completed condition appear with midnight timestamps on the dates- and so I return nothing from my ending date. What's the be…

This is in Oracle. BOBJ 6.5

Upside- the user doesnt’ see a timestamp in a prompt. It is a date object and therefore sorts and ranges correctly.

Downside- This required me to make 4 objects and hardcode a do not regerate SQL

Special note in building the end prompt if original object was a timestamp-so hours were greater than midnight. Don’t forget the minus 1 in the formula, otherwise you will lose the last day of your dates.

---

benslow (BOB member since 2005-11-04)

[system]

try using this see if it works:

In Designer right click on the datetime object , go to object format ,
set it to mm-dd-yy ie remove the time stamp.

then refresh structure and see the display
if this doesn"t work you might have to change it at database level .

Warm regards,

Disha james

---

Disha (BOB member since 2007-05-23)