Convert string yyyyMMdd to dd/MM/yyyy

I am trying to perform a databind to a dropdown box with the below code.

However when i run my app I get this error on the databind command

Format specifier was invalid

I cant figure out whats the problem
Dim strSQL9 As String = "SELECT THRU AS Date, CUTOFF, CHECK, CLOSED FROM LOCALDTA.ESLECF1 WHERE THRU <= " & EndDate & " AND Substr(DIGITS(From),1,4) = " & sYear & " ORDER BY Date DESC"
        Dim objRS9 As New IBM.Data.DB2.iSeries.iDB2DataAdapter(strSQL9, AS400conn)
        Dim readerobjrs9 As IBM.Data.DB2.iSeries.iDB2DataReader
        readerobjrs9 = objRS9.SelectCommand.ExecuteReader()
 
 
 
        ddPayperiod.DataTextField = "Date"
        ddPayperiod.DataValueField = "Date"
        ddPayperiod.DataTextFormatString = "{0:d}"
        ddPayperiod.DataSource = readerobjrs9
        ddPayperiod.DataBind()

Open in new window

LVL 6
ITHelper80Asked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
RamanaChoudaryConnect With a Mentor Commented:
or

Dim strSQL9 As String = "SELECT CONVERT(varchar, THRU,103) as Date, CUTOFF, CHECK, CLOSED FROM LOCALDTA.ESLECF1 WHERE THRU <= " & EndDate & " AND Substr(DIGITS(From),1,4) = " & sYear & " ORDER BY Date DESC"
0
 
RamanaChoudaryCommented:
TRY

ddPayperiod.DataTextFormatString = "dd/MM/yyyy"
0
 
ITHelper80Author Commented:
That fills my dropdown box with the letters dd/MM/yyyy...:(
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
RamanaChoudaryCommented:
try to format in u r sql query only..

Dim strSQL9 As String = "SELECT CONVERT(varchar(Max), THRU,101) as Date, CUTOFF, CHECK, CLOSED FROM LOCALDTA.ESLECF1 WHERE THRU <= " & EndDate & " AND Substr(DIGITS(From),1,4) = " & sYear & " ORDER BY Date DESC"
 

and

        ddPayperiod.DataTextField = "Date"
        ddPayperiod.DataValueField = "Date"
        ddPayperiod.DataSource = readerobjrs9
        ddPayperiod.DataBind()

0
 
RamanaChoudaryCommented:
If you want date format in British
use 103 insted of 101
0
 
ITHelper80Author Commented:
Thanks but I get an error about there not being a "max" column when I try to run the qry
0
 
RamanaChoudaryCommented:
use 60 insted of MAX
0
 
RamanaChoudaryCommented:
sorry use 10
0
 
ITHelper80Author Commented:
Now I get this SQL0204 CONVERT in *LIBL type *N not found.
0
 
RamanaChoudaryCommented:
Dim strSQL9 As String = "SELECT CONVERT(varchar(10), THRU) as Date, CUTOFF, CHECK, CLOSED FROM LOCALDTA.ESLECF1 WHERE THRU <= " & EndDate & " AND Substr(DIGITS(From),1,4) = " & sYear & " ORDER BY Date DESC"
0
 
ITHelper80Author Commented:
Still same error message :(
0
 
srikanthreddyn143Commented:
if you do as Mr.Ramana said, remove the Format string for ddlist
0
All Courses

From novice to tech pro — start learning today.