Format string date to yyyy-MM-dd 1

[Lhuffst]

thread767-1484779

I tried the formula referenced in the above thread and keep getting a "to many arguments" error.

totext({Command.NEWSTARTDATE},"yyyy-MM-dd")

Not sure what to check

 

[CoSpringsGuy]

show an example of the contents of Command.NEWSTARTDATE... and is there always data there?

_____________________________________
Crystal Reports 2008 and XI
Intersystems Cache 2012 ODBC connection

 

[CoSpringsGuy]

either Command.NEWSTARTDATE is not a date or there are records that are null or blank

_____________________________________
Crystal Reports 2008 and XI
Intersystems Cache 2012 ODBC connection

 

[Lhuffst]

Yes there is always a date. When I browse the field, it says its a string of 20

Values are
01-APR-2015
01-JUL-2011

What I am actually trying to do is get the max of this column but sorting didn't work so I thought I would make it a date in yyyy-MM-dd

 

[CoSpringsGuy]

try this formula and then sort on that

date(Command.NEWSTARTDATE)

_____________________________________
Crystal Reports 2008 and XI
Intersystems Cache 2012 ODBC connection

 

[SkipVought]

Yes there is always a [highlight]date[/highlight]. When I browse the field, it says its a [highlight]string[/highlight] of 20

[highlight]THAT[/highlight] is an oxymoron.

A DATE cannot be a STRING, nor can a STRING be a DATE.

DATES are NUMBERS, which is how they can be colated and do math.

Hence the suggestion from CoSpringGuy.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Lhuffst]

Skip, you are correct. I should have said that the information is a date but the datatype is a string. I had to convert a date in oracle to string so I could sort. The field Command.NEWSTARTDATE was the result but I couldn't get it to sort. I'm going to go back and see if I can change the sql and try and pass it as a data datatype.
Thanks
Lhuffst

 

[SkipVought]

I had to convert a date in oracle to string so I could sort

??? HUH ???

That's NOT a good reason!

Dates sort just fine!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Lhuffst]

I think there are a couple of issues going on. I created the sql in oracle then I copied that to crystal using the command area under the database connections.

1. If I try to import the dates as dates (To_Date) reformatted to yyyy-mm-dd instead of the oracle default dd-MON-yyyy, then I get an error in crystal saying that it failed to retrieve from the database. If I use [bold]to_Char[/bold] instead (making the date field a string), then I can paste the sql in the command area under the database connnections.

Ultimately my goal is to get the minimum start date and the maximum end date for all the records. I was trying to sort it just to verify that I was getting the same number of records in crystal that I had in oracle.

select distinct
b.proj_catg_name,
b.Proj_catg_short_name,
c.wbs_name as cwbs,
d.Task_name,
d.Status_code,
--f.target_cost,
to_Char(D.EARLY_START_DATE, 'YYYY-MM-DD') as EarlyStartDte,
To_Char(D.ACT_START_DATE, 'YYYY-MM-DD') as TaskActStart,
Min(CASE
  WHEN d.Status_code = 'TK_Active'  THEN To_Date(D.ACT_START_DATE, 'YYYY-MM-DD')
  WHEN d.Status_code = 'TK_NotStart' THEN To_Date(D.EARLY_START_DATE, 'YYYY-MM-DD')
  WHEN d.Status_code = 'TK_Complete' THEN To_Date(D.ACT_START_DATE, 'YYYY-MM-DD')
END) as NewStartDate ,
--extract(year from d.Early_end_date) as YrEndDate,
To_Char(d.early_end_date, 'YYYY-MM-DD') as EarlyEndDate,
To_Char(d.act_end_date, 'YYYY-MM-DD') as TaskActEnd,
e.proj_Short_Name,
--count(e.proj_short_name) as cntTasks,
--Max(CASE
--  WHEN d.Status_code = 'TK_Complete'  THEN to_Char(D.ACT_End_DATE, 'DD-MON-YYYY')
--  WHEN d.Status_code = 'TK_Active' THEN To_Char(D.EARLY_END_DATE, 'DD-MON-YYYY')
--  When d.Status_Code = 'TK_NotStart' THEN To_Char(D.EARLY_END_DATE, 'YYYY-MM-DD')
--END) as NewEndDate

Max(CASE
  WHEN d.Status_code = 'TK_Complete'  THEN To_Date(D.ACT_End_DATE, 'YYYY-MM-DD')
  WHEN d.Status_code = 'TK_Active' THEN  To_Date(D.EARLY_END_DATE, 'YYYY-MM-DD')
  When d.Status_Code = 'TK_NotStart' THEN To_Date(D.EARLY_END_DATE, 'YYYY-MM-DD')
END) as NewEndDate

from admuser.projpcat a, admuser.pcatval b, admuser.projwbs c, 
     admuser.task d, admuser.project e, admuser.taskrsrc f
where 
b.proj_catg_id = a.Proj_catg_id
and c.proj_id = a.proj_id
and d.wbs_id = c.wbs_id
and d.proj_id = a.proj_id
and E.PROJ_ID = a.proj_id(+)
and f.proj_id = a.proj_id
--and a.proj_id = '9892'
and b.proj_catg_name like 'Beaver%'
and e.proj_short_name like 'BeaverDam-ESA'
--and rownum <= 100
group by d.task_id,
a.proj_id,
a.proj_catg_id,
a.proj_catg_type_id,
b.proj_catg_name,
b.Proj_catg_short_name,
c.proj_id,
c.wbs_name,
c.wbs_id,
c.delete_date,
c.ANTICIP_END_DATE,
d.wbs_id,
D.EARLY_START_DATE,
extract(year from d.Early_end_date),
To_date(d.early_end_date, 'YYYY-MM-DD'),
d.Task_name,
d.Status_code,
D.ACT_START_DATE,
d.act_end_date,
e.proj_Short_Name,
F.TARGET_COST,
D.EARLY_END_DATE

 

[CoSpringsGuy]

So what happened when you didnt try to convert at all in the sql?

_____________________________________
Crystal Reports 2008 and XI
Intersystems Cache 2012 ODBC connection

 

[Lhuffst]

That's what I'm trying to do now.

 

[Lhuffst]

I removed all of the formatting for the dates and was able to bring it into crystal. Then I reformatted all of the dates under options (I hope that applies only to this report) to a date with a format of yyyy/mm/dd - this seems ok as well. I was able to sort properly this time and it does show that I'm missing some records but I think that's unrelated to my other problems.

Thanks for the help - it really does help to have someone look over what you did and give fresh perspectives. On that note, I'm going home
Have a great evening
lhuffst

 

[SkipVought]

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}