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:MM: SS.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, Lee: Patio,' '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)