CODEWITHSUNDEEP
sqlsql-servercrystal-reports
Andy K
Andy K

Reputation: 5085

Crystal reports ; How to change the format of a field

I'm very new to crystal reports so please bear with me

I have created a report. The report connects to a SQL server database. One of the field is a date field but set as a varchar type and looks like this 19890105.

I want to change it to this format 05/01/1989

I tried this 

CDate (
(ToNumber ({ELEC_.DOB_DROIT} [1 to 4])),
(ToNumber ({ELEC_.DOB_DROIT} [5 to 6])),
(ToNumber ({ELEC_.DOB_DROIT} [7 to 8]))
)

I've put it my formula editor (see below)

enter image description here

I'm doing a preview of the report and the date is still in the format of yyyymmdd ...

I'm missing something but I'm unsure of what ...

TIA for your insights ...

Update since yesterday: I found a solution since yesterday but I still would like an answer , out of curiosity and who knows, I might be able to use it in the near future. Thanks.

Upvotes: 0

Views: 1961

Answers (2)

Andy K
Andy K

Reputation: 5085

Please find my answer / workaround in SQL

I went through the database expert, add another command and put that sql query

 SELECT iu_ent,CONVERT(varchar(10),CAST(DOB_DROIT AS DATE),103) AS DOB_DROIT,
        CASE 
            WHEN DOB_DROIT_CONJ = '' THEN ''
            ELSE CONVERT(varchar(10),CAST(DOB_DROIT_CONJ AS DATE),103) END AS DOB_DROIT_CONJ 
FROM Elec_My_Table

It is a bit long (10s for 12k rows) but my table is not a big one

And this one is my final solution all in CR's language

stringVar DateNaissED := if isnull({ELEC_.DOB_DROIT}) then '' else {ELEC_.DOB_DROIT};

IF DateNaissED = '' THEN ''
ELSE
right(DateNaissED,2) + '/' + mid(DateNaissED,5,2) + '/' + left(DateNaissED,4)

Upvotes: 0

CoSpringsGuy
CoSpringsGuy

Reputation: 1645

Not sure why I didnt think of this before

totext(
CDate (
(ToNumber ({ELEC_.DOB_DROIT} [1 to 4])),
(ToNumber ({ELEC_.DOB_DROIT} [5 to 6])),
(ToNumber ({ELEC_.DOB_DROIT} [7 to 8]))
),"dd/MM/yyyy")

Upvotes: 1

Related Questions