[QODBC-Desktop] Troubleshooting: Problems with Date Format
Posted by Juliet (QODBC Support) on 30 August 2011 06:02 AM

Problem With Date Format

Troubleshooting: Problems with the Date Format

Problem Description 1:

When you want to filter records by date, input a query like the one below:

select top 1 * from DepositLine WHERE TimeCreated < '2011-06-06' ORDER BY TimeCreated DESC

But the system returns the following error:

[QODBC] Invalid operand for operator

Solution 1:

The time data in QODBC has its formation. The date type format is {d 'YYYY-MM-DD'} and the time stamps format is {ts 'YYYY-MM-DD HH:MMSS.SSS'} The time created field is timestamp type, so the correct query should be like the below:

SELECT TOP 1 * from DepositLine WHERE TimeCreated < {ts '2011-06-06 00:00:00.000'} ORDER BY TimeCreated DESC

Please refer to How are dates formatted in SQL queries when using the QuickBooks generated timestamps for more instructions.

 

Problem Description 2: 

Using SQL Server 2008, I have a table with a date field using the format date time.

I select from that table a date time variable:

set @vDATE = (Select [DATE] from dbo.OrderHeader where dbo.OrderHeader.REFNO = @RefNo)

I then convert that date to the QODBC format using this logic:

Set @vDATE = '{d''' + Left(CONVERT(nvarchar(30), @vDATE, 121),10) + '''}'

If I run that logic in a Query window by itself, it results in a string of "{d'yyyy-mm-dd'}."

But when the full script is run, I get this error message:

"Msg 241, Level 16, State 1, Line 102

Conversion failed when converting date and time from character string."

If I manually set the date to "Set @vShipDate = {d'2014-10-14'}" it works fine

Also, "select ('{d''' + Left(CONVERT(nvarchar(30), DATE, 121),10) + '''}') from order header" results in a date string with QODBC formatting

So what's wrong with this process?

 

Solution 2:

I would like to inform you that there is no need for conversion, just declare the date parameter as date time data type & set the value of the parameter & use its value in the insert statement.

Please refer to the sample query.

Note: Please change the value according to your company data.

Declare @vDATE as datetime
Declare @vShipDate as DateTime
set @vDATE='10/24/2014.'
set @vShipDate='10/28/2014.'

INSERT INTO LinkedServerName...InvoiceLine (CustomerRefFullName, ARAccountRefFullName, TxnDate, RefNumber,InvoiceLineItemRefListID, InvoiceLineDesc, InvoiceLineRate, 
InvoiceLineAmount, BillAddressAddr1, BillAddressAddr2, BillAddressCity, BillAddressState, BillAddressPostalCode, IsPending, TermsRefFullName, ShipDate, Memo, IsToBePrinted )
VALUES ('Smith, LeePatio,' 'Accounts Receivable,' @vDATE, '76647', '240000-933272656', 'POWER TRAK-2000', 
200.00000, 200.00, 'Brad Lamb,' '1921 Appleseed Lane', 'Bayshore,' 'CA,' '94326', 0, 'Net 30', @vShipDate, 'Memo Test,' 0) 

(57 vote(s))
Helpful
Not helpful

Comments (3)
Connie
20 September 2012 03:25 PM
Hahahhaa. I'm not too bright today. Great post!
waaaa
18 April 2018 04:45 PM
I am receiving the Error

"ERROR [42500] ERROR: 3020 - There was an error when converting the date value "0000-00-48. In the field "salesOrder Transaction Date".


The Date Value i am trying to insert is "4/4/2018"

My Code

DateTime JobDate = Wintac_JobDate;

string addSalesOrder = "INSERT INTO SalesOrderLine (CustomerRefListID, TemplateRefListID," +
" SalesOrderLineItemRefListID, SalesOrderLineDesc,SalesOrderLineQuantity, SalesOrderLineRate, " +
"SalesOrderLineSalesTaxCodeRefListID, Memo, SalesOrderLineInventorySiteRefListID, SalesOrderLineInventorySiteLocationRefListID" +
",TxnDate,ShipAddressAddr1,ShipAddressAddr2,ShipAddressAddr3,ShipAddressAddr4,ShipAddressAddr5,FQSaveToCache)" +
"VALUES('" + QBCustomerListID + "','" + templateLID + "', '" + LID + "', '" + Description + "', " + Quantity + ", " + 120 + "," +
" '" + SalesTax + "', '" +Wintac_WipNo+"','"+LaborSite+"','"+LaborSiteLocation+"',"+
"?,'" + shipAdr1+ "','" + shipAdr2 + "','" + shipAdr3 + "','" + shipAdr4 + "','" + shipAdr5 + "'," +
""+ FQSaveToCache + ")";




OdbcCommand sqlcmd2 = new OdbcCommand(addSalesOrder, quickbookscon2);

sqlcmd2.CommandType = CommandType.Text;
sqlcmd2.CommandTimeout = 180;

MessageBox.Show(JobDate.ToShortDateString());
sqlcmd2.Parameters.Add("P7", OdbcType.DateTime).Value = JobDate.ToLongDateString()
if (Quantity != 0)
{
if (sqlcmd2.ExecuteNonQuery() == 1)
{
if(FQSaveToCache==0)
MessageBox.Show(" added successfully.");
}
}



I have tried converting the variable Job Date
-short date string
-long date string
-Entering the variable directly into the query


Any help would be appreciated.
Jack
19 April 2018 07:04 AM
Hi,

I would like to share that date format in QODBC is {d 'yyyy-mm-dd'}. So, You need to provide date format as per QODBC date format.

You can refer below sample code which converts provided date to QODBC Date format & can make changes in your code.

For Example:

string date = "4/25/2018";
string longDate = string.Format("{0}{1}{2}","{d'",Convert.ToDateTime(date).ToString("yyyy-MM-dd"),"'}");

If you are still the facing issue, Please raise a support ticket to the QODBC Technical Support department from below mentioned link & provide requested information:
http://support.flexquarters.com/esupport/index.php?/Tickets/Submit

We may need the following information, Please attach below listed files when replying to the ticket.
1) Screenshot of QODBC Setup Screen -- > About
2) Screenshot of the issue you’re facing.
Share Entire Log Files as an attachment in text format from
3) QODBC Setup Screen -- > Messages -- > Review QODBC Messages
4) QODBC Setup Screen -- > Messages -- > Review SDK Messages
Refer: How to take a screenshot: www.qodbc.com/links/screenshot.htm
Post a new comment
 
 
Full Name:
Email:
Comments:
CAPTCHA Verification 
 
Please complete the captcha below (we use this to prevent automated submissions).