| Author |
Message |
coountsr
Newbie
Joined: 23 May 2013
Online Status: Offline
Posts: 20
|
   Topic: String to Date Issue
Posted: 12 Nov 2014 at 5:43pm |
|
I have a database that was migrated from DB2 to Oracle. In one of the Date fields is was configured as CHARACTER instead of DATE. So when I run the report I get the following error: A string is required here and this is highlighted in my formula, (CurrentDate -1).
Is thee a string that replaces this call?
Thanks,
|
 IP Logged |
|
|
praveeng
Senior Member
Joined: 11 Jul 2011
Online Status: Offline
Posts: 165
|
Posted: 12 Nov 2014 at 11:09pm |
Hi, Convert your Date string field to Date format using Cdate(Date_database_field) and use this in formula. --Praveen G
|
|
Praveen Guntuka,
praveen_guntuka@yahoo.com
|
|
IP Logged |
|
coountsr
Newbie
Joined: 23 May 2013
Online Status: Offline
Posts: 20
|
Posted: 13 Nov 2014 at 3:19am |
I am currently have it defined as {WC4U999S.WS_DATE} >= ToText(CurrentDate - 1) to get the previous 24 hours of data. Can you show me what the string should look like now? Thanks,
|
|
IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 13 Nov 2014 at 5:09am |
really depends on what your string is formatted as but in general:
cdate({WC4U999S.WS_DATE}) >= (CurrentDate - 1)
|
|
IP Logged |
|
coountsr
Newbie
Joined: 23 May 2013
Online Status: Offline
Posts: 20
|
Posted: 14 Nov 2014 at 6:26am |
|
|
|
IP Logged |
|
coountsr
Newbie
Joined: 23 May 2013
Online Status: Offline
Posts: 20
|
Posted: 14 Nov 2014 at 6:31am |
Thanks that worked. I have one more question. I am trying to automate another report. I created a RunDate parameter that prompts you for a run date. I thought I could use something like you described above to autmate it but when I run the report it just keeps reading records and eventually times out. I want to go from this, {WC4U999S.WS_DATE} = {?Run Date}, to something like this, cdate({WC4U999S.WS_DATE}) = (CurrentDate). Any suggestions? Thanks,
|
|
IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 14 Nov 2014 at 6:33am |
is WC4U999S.WS_DATE a datetime field or just a date field and is it a string data type?
and you want it to select any records where the WC4U999S.WS_DATE has the same date as today, correct?
datediff("d",cdate({WC4U999S.WS_DATE}),CurrentDate)=0
Edited by DBlank - 14 Nov 2014 at 6:42am
|
|
IP Logged |
|
coountsr
Newbie
Joined: 23 May 2013
Online Status: Offline
Posts: 20
|
Posted: 14 Nov 2014 at 6:46am |
Its just a date field that is a string data type and I want it to select any records where the WC4U999S.WS_DATE was the previous day so CurrentDay -1.
|
|
IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 14 Nov 2014 at 7:15am |
|
datediff("d",cdate({WC4U999S.WS_DATE}),CurrentDate)=0
|
|
IP Logged |
|
coountsr
Newbie
Joined: 23 May 2013
Online Status: Offline
Posts: 20
|
Posted: 14 Nov 2014 at 9:07am |
Ok, i tried that but its just sits there and reads records and times out. Here is my complete SQL. {WC4U999S.FIRSTNAME} <> "BATCH" and datediff("d",cdate({WC4U999S.WS_DATE}),CurrentDate)=0 and {WC4U999S.GROUPCD} <> "AWD FACIL" and not ({WC4U999S.USERID} like ["*SCANNER*", "*SRVR*"]) and {@Status Change} in ["Create Work", "Status Change"] |
|
IP Logged |
|
|
|
