Sql dateime parameter not working for datetime picker value in C#

Posted by Raj.Trivedi under Sql Server on 4/2/2015 | Points: 10 | Views : 721 | Status : [Member] [MVP] | Replies : 1
Hello Team,

I have field in table for Date and the date is in Datetime format
ex : 2015-04-02 15:55:10.000

Now i want to search for records for 2 april 2015

I am using datetime picker and the value that datetime picker gives is

"4/2/2015 12:00:00 AM"

I have the record for the date 2 april 2015 but when i select from date as 2 april 2015 and to date 2 april 2015, i do not get any records.

Any way to resolve this

here is my stored procedure

ALTER proc [dbo].[GetOutstandingInvoiceforPurchaseonbaseofDate]
(
@Fromdate datetime,
@todate datetime
)
as
begin
select TOP 1 TMN.Invoiceno,TMN.BillDate,PIM.CurrentPaidAmount,PIM.InvoiceAmount,CM.VendorName,
PIM.PaymentDate,TMN.OutstandingAmount,TMN.SumforAmountPaid,PIM.PaymentId
from VTransactionMasterNew TMN
inner join VPaymentInfo PIM
on
TMN.Invoiceno = PIM.InvoiceNo
inner join
VendorMaster CM on
CM.VendorId = TMN.VendorId
where
TMN.Billdate between convert(varchar(10),@Fromdate,111) and convert(varchar(10),@todate,111) and
TMN.OutstandingAmount > 0.00
order by PIM.PaymentDate Desc
end


Here is the code for Windows Application C #

private void SearchInvoice(string fromdate,string todate)
{
try
{
int Snu = 0;
AppCode.MasterDAL objgetOutstandingAmount = new AppCode.MasterDAL();
DataSet dsoutstanding = new DataSet();
dsoutstanding = objgetOutstandingAmount.GetOutstandingInvoiceforPurchaseonbaseofDate (fromdate,todate);
if (dsoutstanding.Tables[0].Rows.Count > 0)
{
foreach (DataRow row in dsoutstanding.Tables[0].Rows)
{
xgvOutstanding.Rows.Add();
xgvOutstanding.Rows[Snu].Cells[0].Value = Convert.ToString(row["InvoiceNo"]);
xgvOutstanding.Rows[Snu].Cells[1].Value = Convert.ToString(row["BillDate"]);
xgvOutstanding.Rows[Snu].Cells[2].Value = Convert.ToString(row["VendorName"]);
xgvOutstanding.Rows[Snu].Cells[3].Value = Convert.ToString(row["InvoiceAmount"]);
xgvOutstanding.Rows[Snu].Cells[4].Value = Convert.ToString(row["SumforAmountPaid"]);
xgvOutstanding.Rows[Snu].Cells[5].Value = Convert.ToString(row["OutstandingAmount"]);
++Snu;
}
}
else
{
MessageBox.Show("No Outstanding for Search Criteria");
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}

Function GetOutstandingInvoiceforPurchaseonbaseofDate

public DataSet GetOutstandingInvoiceforPurchaseonbaseofDate(string fromdate,string todate)
{
DataSet dsTemp = new DataSet();
errdesc = dbmgr.Execute<DataSet>(out dsTemp, "GetOutstandingInvoiceforPurchaseonbaseofDate", Convert.ToDateTime(fromdate), Convert.ToDateTime(todate));
return dsTemp;
}

Any reference will be appreciated

Regard's
Raj.Trivedi
"Sharing is Caring"
Please mark as answer if your Query is resolved



Responses

Posted by: Vuyiswamb on: 4/3/2015 [Member] [MVP] [Administrator] NotApplicable | Points: 25

Up
0
Down
Always put your code in a Code tag , it will be difficult for someone to help you with a code that is just pasted like this.

your code is correct what you need to look at is the date format that is coming from the DatePicker and the Date format that is stored in the Database , make sure they are the same else write a small function to swop the date parts around.

There is a Difference between

2015-04-02


and

4/2/2015




Thank you for posting at Dotnetfunda
[Administrator]

Raj.Trivedi, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response