Custom date prompt with SQL Server

building semantic layer
1

Hi all,

We are using an SQL Server 2005 database and we’re facing a problem formatting the datatype: datetime.

When using an object we just want to show the part : mm-dd-yyyy of the datetime, not the HH:mm:sss.

We changed the format of the object to the date pattern “mm-dd-yyyy” and it’s working well when used in the report.
Unfortunately, this is not the same with the list of value. When using a prompt, webI always shows the “HH:mm:sss” part of the datetime.

Any idea?

bbo1991 (BOB member since 2008-07-02)

2

When you say Format, did you just format the object or did you provide a SQL format, see below?

http://www.sql-server-helper.com/tips/date-formats.aspx

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

3

We’ve just format the object.

We tested to format with an SQL statement, just like the samples given in your link: it formats well but it returns a varchar !!!
Then, you loose all the benefits of using a date type : sorting date as varchar is a mess :hb:

bbo1991 (BOB member since 2008-07-02)

4

Well, cast or convert back to date time.

Read up on your SQL server functions…:).

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