Datetime problem

[jkrassman]

Hi,

I am having problems trying to fetch a recordset between dates. Query looks like this:

Code: Select all

select TH.roundOff, TH.invoiceNo, TH.OCR, CONVERT(char(10), TH.DueDate,126) DUEDATE , 
	CONVERT(char(10), TH.InvoiceDate,126) INVDATE, TH.isPayed, TH.PayedAmount,
	(select SUM(Total + totalTax) - TH.roundOFf from tblInvoiceRows where headid = TH.id) TOTAL,
        tblUsers.fname + ' ' + tblUsers.lName NAME from tblInvoiceHead TH 
                    join tblusers on tblUsers.id = TH.userid

        where TH.Release = 1 and TH.corporationID = {corporationid}
        and convert(varchar(100),TH.invoiceDate,103)  >= {startdate} 
        and  convert(varchar(100),TH.invoiceDate,103) <= {enddate} 
        and TH.RemovedDate is null order by TH.invoiceDate

The field I am comparing the dates between is just date and not datetime, and the date format on the SQL server is "Y-m-d" and not "d/m/Y".

The attachment datetime.png is no longer available

The error I am getting is as below:

datetime.png (65.84 KiB) Viewed 1717 times

[jkrassman]

Case closed - I solved it by converting the datetime in my query as below:

Code: Select all

CONVERT(char(10),  CONVERT(datetime, '{startdate}', 104),126)

[Andrew]

Hello,

Thank you for sharing your solution.