CODEWITHSUNDEEP
c#mysqlsql-serverdatetime
Kinchit Dalwani
Kinchit Dalwani

Reputation: 398

Error in datetime query

I am using the following sqlquery in asp.net to Group by three tables using inner join :

  SELECT tblVendorItem.Name, 
  tblEventItem.Quantity * tblEventItem.Price AS 'Sale', 
  tblEventService.ServiceDate
  FROM tblEventService 
  INNER JOIN  tblEventItem ON 
  tblEventService.EventServiceID = tblEventItem.EventServiceID 
  INNER JOIN  tblVendorItem ON 
  tblEventItem.VendorItemID = tblVendorItem.VendorItemID
  INNER JOIN  tblVendor ON 
  tblVendorItem.VendorID = tblVendor.VendorID
  WHERE (tblEventService.VendorID = 1) 
  AND (tblEventService.ServiceDate BETWEEN '20-04-2015 00:00:00' AND '23-04-2015 00:00:00')
  GROUP BY 
  tblVendorItem.Name, tblEventItem.Quantity, tblEventItem.Price, tblEventService.ServiceDate

Here, VendorID and Dates are static. The query got parsed but on executing,it shows the following error:

Image of the Error has been attached

I have check the datatype in both,the model class and table, it shows datetime.

Can anyone tell me what is wrong in the query or how can this error can be solved?

Upvotes: 1

Views: 76

Answers (3)

Dinav Ahire
Dinav Ahire

Reputation: 583

If You want Time in your result and,looking at your query it seems you will need 3 parameters.VendorID,FromDate andToDate. So Add below code in your method. VendorID you need to access differently.. 

 string startdate = Convert.ToDateTime(txtDateTo.Text + " 00:00:00").ToString("yyyy-MM-dd" + " 00:00:00");
 string enddate = Convert.ToDateTime(txtDateFrom.Text + " 00:00:00").ToString("yyyy-MM-dd" + " 00:00:00");

Upvotes: 1

Suresh
Suresh

Reputation: 1169

Try to add MM-DD-YYYY date i.e. 

> where mydatecol BETWEEN CONVERT(datetime, '04-20-2015 00:00:00') AND
> CONVERT(datetime, '04-20-2015 00:00:00')

Else you have to apply format specified for DD-MM-YYYY

> mydatecol BETWEEN CONVERT(datetime, '20-04-2015 00:00:00', 103) AND
> CONVERT(datetime, '20-04-2015 00:00:00', 103)

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269463

Try using ISO standard formats for your constants:

tblEventService.ServiceDate BETWEEN '2015-04-20' AND '2015-04-23'

Also, the time components are unnecessary.

Also, I would recommend only using single quotes for string and date constants. Don't use them for column aliases.

Upvotes: 3

Related Questions