Date Format without time

[system]

I need Date Format without time. (only DD.MM.YYYY)

I have tested the following things:

1. To_Date(To_car())
2. right click and then “Objectformat”

any ideas?

Thank a lot
Qami

---

Qami (BOB member since 2010-09-08)

[system]

did u tried Truncate?

regards,

Gaurav

---

gauravg (BOB member since 2009-04-27)

[system]

Hi,

I give you more information with this gif

Thanks

---

Qami (BOB member since 2010-09-08)

[system]

Ohh , the problem is with the list of values…
I am not sure whether Truncate will work or not but if possible please try it once…I don’t have BO environment currently to test .

If its not working… the only solution that I can give here is to convert the date to character format in the required format …it will work for sure.

---

gauravg (BOB member since 2009-04-27)

[system]

Also try the Designer FAQ…

---

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

[system]

Hi,
Converting in character was my first solution but I get the wrong data.
I get not all the month that I select example: when I select between 01.05.2006 and 05.08.2007

Thanks a lot for your help guys

---

Qami (BOB member since 2010-09-08)

[system]

select convert(datetime,convert(char(10),getdate(),112))

To use in prompt values, you should select something like this…

---

kranthidotcom (BOB member since 2011-06-08)

[system]

Try doing this in the designer, you can format the date in the object properties… Hope this helps.

---

armel9979 (BOB member since 2008-09-27)

[system]

Hi,

can you explain it a little bit more, please?
I’ve never worked with convert() or getdate () in Universe Designer.

select convert(datetime,convert(char(10),getdate(),112))

To use in prompt values, you should select something like this…

thanks

---

Qami (BOB member since 2010-09-08)

[system]

Qami,

@Qami

I need Date Format without time. (only DD.MM.YYYY)

I have tested the following things:

1. To_Date(To_car())
2. right click and then “Objectformat”

Objectformat does not apply on a LOV

@Qami

Converting in character was my first solution but I get the wrong data.
I get not all the month that I select example: when I select between 01.05.2006 and 05.08.2007

You can convert dates to character AND create a predefined condition on the dates (as a date-field).
Example Object (Oracle), dates as a character-field:

TO_CHAR(TABLE.DATE,'DD.MM.YYYY')

Example Predefined Condition (Oracle), dates as a date-field:

TABLE.DATE between @Prompt(From) and @Prompt(Until)

Hope this helps,
Johan

---

JdVries (BOB member since 2006-02-06)

[system]

On the universe define the required date field as

convert(varchar(10),datefield,110)

---

kranthidotcom (BOB member since 2011-06-08)

[system]

Hi,

If you have to show calander then you can change the following parameter USER_INPUT_DATE_FORMAT under sqlsrv.prm file.
By default it will be mm/dd/yyyy HH:m:s, if you need only date change it to mm/dd/yyyy

Try this once, but it will affect all the reports in that environment.
You can convert that date time field into the required format using convert functions in sql server

---

divyap (BOB member since 2010-09-08)

[system]

Hi

I have an issue when converting a smalldatetime value to a formatted date object.

I have an object using the following code:
cast(convert(varchar(10), table.column, 103) AS datetime)

The object parses ok but I get the following error in WEBI (XI R3) “The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value… (WIS 10901)”

However in another universe I use the same function and it works fine in WEBI. I have checked as many Universe parameters as I can see and as many database parameters as I can see also - I did have one issue whete the SQL database had a different Collation but I have now changed that so both databases are the same. Both databases sit on the same server.

The issue can’t lie with the .prm files either as it works against one universe but not the other in WEBI and the same goes for the locale settings.

Does anyone have any suggestions as to why this object returns the error as above.

Many thanks

---

Woomaster (BOB member since 2010-04-16)

[system]

The date fields in the database are of the format mm/dd/yyyy and whenever a report is modified which has a data field as query filters the dat shows up as mm/dd/yyyy 12:00:00 AM even though the date in the Teradata database is scripted as mm/dd/yyyy.
I tried doing the object format in the universe, adding USER_INPUT_DATE_FORMAT = mm/dd/yyyy in parameters but that didn’t help me.
I even followed the FAQ link which Dave posted in the thread.

---

rnola (BOB member since 2006-03-26)

[system]

@rnola

The date fields in the database are of the format mm/dd/yyyy and whenever a report is modified which has a data field as query filters the dat shows up as mm/dd/yyyy 12:00:00 AM

This is a tool feature Even if you have (just) dates in database, BO will still put 12:00:00 AM to your date field while showing you the LOV.

I am afraid you don’t have too many options, I don’t think there is any direct solution to just show date part in LOV. The only way is to convert it into Character format. You may be able to achieve this with some sort of Customization but I really won’t suggest going down that path.

You may like to see this thread (from Dave’s link):

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…

Cheers !

Rajat

---

Rajat Sapru (BOB member since 2008-08-28)

[system]

I do not have any LOV for the date field. Its just that the date appears as mm/dd/yyyy 12:00:00 AM when you refresh your report if the date field as a prompt.

---

rnola (BOB member since 2006-03-26)

[system]

@rnola

I do not have any LOV for the date field. Its just that the date appears as mm/dd/yyyy 12:00:00 AM when you refresh your report if the date field as a prompt.

I think I need some clarification. The Prompt for your date field would be based on some Object/Column. When you run report, you will get the Prompt and then you would see some values (date and time). Do you want these values to be displayed without Time part?

In case yes, then my post holds true that you cannot remove the time part (unless you do some kind of heavy customization) because BO doesn’t allow it. You will have to convert it into character.

In case you have a different concern, please share with us (your exact requirement).

Cheers !

---

Rajat Sapru (BOB member since 2008-08-28)

[system]

Yes, I do not want the time part to be seen when you try to schedule an already existing date prompt values. So this is a BO feature !

---

rnola (BOB member since 2006-03-26)