Livereport date field

I have a livereport that uses 2 user-supplied variables, a folder and a date. I can use User Input to supply the folder but when I allow the user to supply a date the SQL gets transfigured from "d.CreateDate < %2" to d.CreateDate < { ts '2017-09-20 00:00:00' } , which results in no rows returned. If I hard-code the date the query works fine. Any idea why the report is adding the {ts} around the date field?

Find more posts tagged with

Comments

Colin_Schmidt

That’s the default since it has no idea what time you want. The D.Createdate will have the date and time in it. If Oracle, you can do it this way:

to_char(d.createdate, ‘yyyymmdd’) < to_char(%2, ‘yyyymmdd’)

The comparison works from the Input Parameter when you compare to “today-start” or “Last week-start”.

Colin J. Schmidt

From: eLink Entry: Content Server LiveReports Forum [mailto:livereportsdiscussion@elinkkc.opentext.com]
Sent: Wednesday, September 20, 2017 4:17 PM
To: eLink Recipient <devnull@elinkkc.opentext.com>
Subject: Livereport date field

Livereport date field

Posted by Saunders, Tim On 09/20/2017 04:12 PM

[To post a comment, use the normal reply function]
Forum:	Content Server LiveReports Forum
Content Server:	Knowledge Center CS16

The information contained in this e-mail is confidential and/or proprietary to Capital One and/or its affiliates and may only be used solely in performance of work or services for Capital One. The information transmitted herewith is intended only for use by the individual or entity to which it is addressed. If the reader of this message is not the intended recipient, you are hereby notified that any review, retransmission, dissemination, distribution, copying or other use of, or taking of any action in reliance upon this information is strictly prohibited. If you have received this communication in error, please contact the sender and delete the material from your computer.

Nizar Ghazal

I haven't tried this, yet, but maybe since it looks like such a bizarre construction, that you can the substring of that output, and convert it?

Ex: SUBSTRING(DATENAME(xxxx, %1), X, Y)

Where X is the first and Y is the last character you want, from that date input?

xxxx being whatever format you want it put into, as seen in the following:
https://technet.microsoft.com/en-us/library/ms174395(v=sql.105).aspx

Hope that helps.

sıɹɥɔ

Quoted Tim Saunders on 09/22/2017 04:47 PM:

------------------------------------------------------------

Hi Tim,

The {ts} is part of the syntax for an ODBC timestamp literal. Its use by Content Server shouldn't be the reason for the lack of rows being returned. I'd suspect a logic error elsewhere in the sql condition.

-chris