LimeReport Forum

General Category | Основное => Discussion | Обсуждение => Topic started by: Paul Traut on January 11, 2018, 04:38:20 PM

Title: Date format in the sql statement
Post by: Paul Traut on January 11, 2018, 04:38:20 PM

Hi,

I have problems to create an sql statement
If I try something like this:

Code: [Select]

SELECT DISTINCT ID as OrderID, Name, ProductId, Creationdate
   FROM ttorders
   where productid = $D{products.productid} and creationdate >= '11/28/17 2:42 PM'I get the error: ORA-01843:not a valid month.

If I try this:

Code: [Select]

SELECT DISTINCT ID as OrderID, Name, ProductId, Creationdate
   FROM ttorders
   where productid = $D{products.productid} and creationdate >= '2017-11-28 09:21:29'I get the error: ORA-01861: literal does not match format string.

The Problem is:
This

Code: [Select]

'2017-11-28 09:21:29'is the format within my db and this

Code: [Select]

'11/28/17 2:42 PM'is how lime report shows me the date within the preview.

How can I filter my results depending on the date???

Title: Re: Date format in the sql statement
Post by: Arin Alex on January 11, 2018, 06:32:31 PM

You have to convert  string according server language parameter, something like this:
creationdate>=to_date('2017-11-28 09:21:29', 'YYYY-MM-DD HH24:MI:SS','NLS_DATE_LANGUAGE = AMERICAN')