Click here to Skip to main content
15,867,308 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
Hi,

I am developing a software that will generate certificates for the users. Using:

VB.NET 2008 w/Crystal Reports & MySQL 5.0

One of my requirements, on the printed certificate it should have a SPECIAL DATE with a format like below:

FROM FORMAT: "01/24/2013" TO "24th Day of January, 2013"
FROM FORMAT: "03/2/2013" TO "2nd Day of March, 2013"
FROM FORMAT: "01/3/2013" TO "3rd Day of January, 2013"

Do you know how to format date even in MySQL to show something these?


I didn't see any options from crystal report under 'FORMAT OBJECTS even in custom date format.

But i saw other .exe software that somehow able to do similar to my requirement
Posted

Format using MySQL

DATE_FORMAT('2013-01-24', '%D Day of %M, %Y)


Sample Query

SELECT DATE_FORMAT(ColumnDate, '%D Day of %M, %Y) FROM MySQL_TABLE
 
Share this answer
 
Comments
JMAM 30-Aug-13 2:17am    
Thanks Jayron.
JMAM 30-Aug-13 3:55am    
Hi Jaryon. It wokrs with MySQL query however it changes when i use that field in my report.

For example: SELECT date_format(`tbl_member`.`col_dateissued`,'"%D Day of %M, %Y"') AS `col_dateissued` FROM MyTABLE

The Date Will be displayed like this "1st Day of August, 2002" 8/1/2002 12:00:00 AM


I do not know why crystalreport changes it
jayron527 4-Sep-13 22:06pm    
Maybe in your datatable... if your using datatable set column to String not DateTime or date
JMAM 19-Sep-13 9:26am    
Thanks it works
make a custome veriable with the value and format it
 
Share this answer
 
Comments
JMAM 28-Aug-13 16:33pm    
Hi, thanks for the response. Can you give more details? how would i know what to insert suffix for DAY format (rd, th or nd) example if 2 its 2nd, if 23 its 23rd. if 4 its 4th.
creat a custome function name test in crystal report, select "Basic Syntax" from drop down.
VB
Function test (t as date) as string
dim outString as string

if day(t)=1 then 
    outString="1st day of "
else 
    if day(t)=2 then 
        outString="2nd day of "
    else 
        if day(t)=3 then 
            outString="3rd day of "
        else 
            if day(t)=4 then 
                outString="4th day of "
            else 
                if day(t)=5 then 
                    outString="5th day of "
                End if
            end if
        end if
    end if
end if

if month(t)=1 then 
    outString=outString + "January, " + Replace (ToText (year(t),0),"," ,"" )
else 
    if month(t)=2 then 
        outString=outString + "February, " +    Replace (ToText (year(t),0),"," ,"" )
    else 
        if month(t)=3 then 
            outString=outString + "March, " +   Replace (ToText (year(t),0),"," ,"" )
        else 
            if month(t)=4 then 
                outString=outString + "April, " +    Replace (ToText (year(t),0),"," ,"" )
            else 
                if month(t)=5 then 
                    outString=outString + "May, " +    Replace (ToText (year(t),0),"," ,"" )
                End if
            end if
        end if
    end if
end if

    test =outString
End Function


call this function from new formula fields.
VB
Formula=test(CDate ({Command.Work_date}))


put the formula field into report details section.
 
Share this answer
 
Comments
Hasan Habib Surzo 29-Aug-13 4:20am    
Before submit this ans i test it with crystal report. So If you think this is not going to work, please share your solution.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900