Conversion failed when converting date and/or time from character string.

Question

0.00/5 (No votes)

See more:

VB.NET

Private Sub ReportForm_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load


       If PLM.drpreports.SelectedIndex = 0 Then
           Dim startDate = New Date(DateTime.Today.Year, DateTime.Today.Month, 1)
          
           Dim endDate = New Date(DateTime.Today.Year, DateTime.Today.Month, DateTime.DaysInMonth(DateTime.Today.Year, DateTime.Today.Month))
           qry = "SELECT h.id, h.name, h.commission, SUM(p.totalamt) AS totalamt, SUM(p.totalamt * (h.commission / 100.0)) AS totalcomm FROM HospitalMaster AS h INNER JOIN Patient_Report AS p ON h.id = p.hospitalid WHERE (p.date >= '" + startDate + "') AND (p.date <= '" + endDate + "') GROUP BY h.id, h.name, h.commission"
           Dim rptCommission As New DataTable
           rptCommission = dal.getData(qry, Nothing)
           Dim crpt As New rptCommission
           crpt.SetDataSource(rptCommission)
           CrystalReportViewer1.ReportSource = crpt
         
       ElseIf PLM.drpreports.SelectedIndex = 1 Then

           Dim dt = New Date(DateTime.Today.Year, DateTime.Today.Month, 1)
           qry = "SELECT p.labno, p.initial, p.name, p.sex, p.date, h.name AS hospitalname FROM Patient_Report AS p INNER JOIN HospitalMaster AS h ON p.hospitalid = h.id WHERE (p.date > '" + dt + "') ORDER BY p.hospitalid, p.labno"
           Dim rptPatients As New DataTable
           rptPatients = dal.getData(qry, Nothing)
           Dim crpt As New rptpatients
           crpt.SetDataSource(rptPatients)
           CrystalReportViewer1.ReportSource = crpt

       ElseIf PLM.drpreports.SelectedIndex = 2 Then
           DateTimePicker1.Visible = False
           Dim startDate As New Date(DateTime.Today.Year - 5, 1, 1)
           Dim endDate As New Date(DateTime.Today.Year - 1, 12, 31)



           qry = "select * from Patient_Report where date > '" + startDate + "' and date < '" + endDate + "'"
           Dim Patient_Report As New DataTable
           Patient_Report = dal.getData(qry, Nothing)

Posted 31-Jan-16 22:52pm

Member 11679869

Add a Solution

Comments

koolprasad2003 1-Feb-16 5:00am

which line of code give you error ?

Member 11679869 1-Feb-16 5:06am

i think at (p.date >= '" + startDate + "') AND (p.date <= '" + endDate + "')

Member 11679869 1-Feb-16 5:15am

https://drive.google.com/file/d/0B-2sM94uHIyAT0JaLWJNUDZnbnc/view check the screen shot here

F-ES Sitecore 1-Feb-16 5:55am

Try converting your date to an unambiguous string

(p.date >= '" + startDate.ToString("dd MMM yyyy") + "') AND (p.date <= '" + endDate.ToString("dd MMM yyyy") + "')

2 solutions

Solution 2

C#

Dim startDate As New Date(DateTime.Today.Year, DateTime.Today.Month, 1)
           startDate = DateTimePicker1.Value.ToString("dd-MM-yyyy")
Dim endDate = New Date(DateTime.Today.Year, DateTime.Today.Month, DateTime.DaysInMonth(DateTime.Today.Year, DateTime.Today.Month))
           endDate = DateTimePicker1.Value.ToString("dd-MM-yyyy")

Posted 1-Feb-16 13:30pm

Member 11679869

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Kornfeld Eliyahu Peter · Accepted Answer · 2016-01-31T23:56:00

Solution 1

You are trying to concatenate String with DateTime...VB will try to convert the String to DateTime to use the + operator of DateTime...I'm sure you can see why the string can not be converted to DateTime here...
You have to convert the DateTime to string of your desired format before concatenation...
Like ToShortDateString or others...

Posted 31-Jan-16 23:56pm

Kornfeld Eliyahu Peter

Comments

Member 11679869 1-Feb-16 19:29pm

thanks alot kornfeld

Dim startDate As New Date(DateTime.Today.Year, DateTime.Today.Month, 1)
startDate = DateTimePicker1.Value.ToString("dd-MM-yyyy")
Dim endDate = New Date(DateTime.Today.Year, DateTime.Today.Month, DateTime.DaysInMonth(DateTime.Today.Year, DateTime.Today.Month))
endDate = DateTimePicker1.Value.ToString("dd-MM-yyyy")