| Author |
Message |
cujo
Newbie
Joined: 23 May 2011
Online Status: Offline
Posts: 4
|
  
Topic: Input Parameter Date Format
Posted: 23 May 2011 at 1:12am |
|
Hello,
I have the following problem: I have data sql statement which compares dates using some_date_to_compare > date('2011-05-05') syntax. The date is currently hardcoded in the sql statement and I would like to change it, to pass the date as a parameter, like this: some_date_to_compare > date({?date_from}). The date_from is Date type, not Date/Hour.
The problem, however with this is that when i try to run the report Crystal passess the date from the Input Parameter box in the following format YYYY-MM-DD hh:mm:ss, so for example if I choose 2011-05-05 as a date from the calendar I get 2011-05-05 00:00:00 passed to 'date_from' parameter.
Is there any way to have a workaround, or to pass the date in format that i expect (YYYY-MM-DD) ?
Regards
|
 IP Logged |
|
|
|
lockwelle
Moderator
Joined: 21 Dec 2007
Online Status: Offline
Posts: 4374
|
Posted: 23 May 2011 at 3:16am |
you could make it a range, ie:
date_to_compare between date_from and dateadd("s",-1,dateadd("d",1,{fromdate})
or fromDate + 23:59:59...at least that is what you are after.
the other way is to strip the time portion off.
BUT
if the date of the table is just a date (no time) the database will convert it to your date + 00:00:00.000, so it is all the same. It is when your database is storing the time with date that you need to either strip the time or look in a time range.
HTH
|
| IP Logged |
|
cujo
Newbie
Joined: 23 May 2011
Online Status: Offline
Posts: 4
|
Posted: 23 May 2011 at 3:38am |
|
Thanks for the replay, but I don't quite get it all.
I'm using Informix and the column is of DATE type (not DATETIME - date + time), so it's only date.
Now the part of my sql is as follows:
date_to_compare between date({?date_from}) AND date({?date_to}),
so i'm trying to compare value from column in my table against date range.
The problem however is that when Crystal passess the date from Input Parameter Box as YYYY-MM-DD hh:mm:ss the result of query validation is: String to date conversion error: -1218, and it's because of the time part in the passed date.
Btw, is there any possibility to turn the query validation off by Crystal in the SQL command modification window?
Edited by cujo - 23 May 2011 at 3:40am
|
| IP Logged |
|
lockwelle
Moderator
Joined: 21 Dec 2007
Online Status: Offline
Posts: 4374
|
Posted: 23 May 2011 at 6:23am |
Sorry, I don't know Informix...
with that said, have you tried removing the date() function around the parameter?
SQL Server will cast a string without a time to a datetime and since no time is specified, it defaults to midnight of the date...but then again CR places the time in the parameter. so....
since I haven't tried to edit the CR SQL command in years (I just call a stored proc) I don't feel qualified to suggest how to proceed... unless of course, you want to create a stored proc in Informix and call it from the report passing in the parameters that might simplify the whole mess.
HTH
|
| IP Logged |
|
cujo
Newbie
Joined: 23 May 2011
Online Status: Offline
Posts: 4
|
Posted: 23 May 2011 at 7:07am |
|
Well, with or without the date() function is almost the same. I will still get String to date conversion error: -1218 since i'm trying to compare colum of type DATE, but pass string in format YYYY-MM-DD hh:mm:ss, which corresponds to the DATETIME. At least it's Informix behavior.
Maybe you know if there is any possibility to turn the query validation off by Crystal in the SQL command modification window?
|
| IP Logged |
|
Emir_W
Senior Member
Joined: 25 Apr 2010
Online Status: Offline
Posts: 228
|
Posted: 23 May 2011 at 9:18pm |
|
try to help.
you can add/modify the selection formula.
{tbl.yourdate}=
totext(year({?date_from}),0,'')+'/'+
(
if month({?date_from})<10 then
'0'+totext(month({?date_from}),0,'')
else
totext(month({?date_from}),0,'')
)
+'/'+
(
if day({?date_from})<10 then
'0'+totext(day({?date_from}),0,'')
else
totext(day({?date_from}),0,'')
)
hope it help.
|
|
Emir W
|
| IP Logged |
|
cujo
Newbie
Joined: 23 May 2011
Online Status: Offline
Posts: 4
|
Posted: 24 May 2011 at 12:34am |
|
It appears to be a case related with the connection type. Earlier I used
simple JDBC connection. Now, when I'm using ODBC connection (Informix
driver) the problem with the date format is gone.
Anyway, thank you guys for hints..
|
| IP Logged |
|
Emir_W
Senior Member
Joined: 25 Apr 2010
Online Status: Offline
Posts: 228
|
Posted: 24 May 2011 at 1:11am |
|
thanks for the share Cujo.
|
|
Emir W
|
| IP Logged |
|
|
|
