I am limited with the date insert I can use with my translation software. I can only insert dates using the {ts’2007-10-01 00:00:00’} format. Since the TxnDate is in the {d’2007-10-01’} format, I am inserting my date into a varchar field and hope to update the TxnDate with this varchar date.  Can this be done?

Thanks,

John

Yes, you can use the CONVERT function within your SQL Statement to convert the timestamp to a date like this:

SELECT name, {fn CONVERT({ts'2007-10-01 00:00:00'}, SQL_TYPE_DATE)} AS "Date" From Customer

The date is being stored in the memo field as 2007-10-01. I need to update TxnDate with the value that is in the memo field.

Thanks,

John

Well this question is nothing like the first question, but if you're asking how to move a date from a memo field to the TnxDate, this is an example:

SELECT RefNumber, TxnDate, Memo FROM Invoice where RefNumber = '87'

Note the Memo has December 30 while the transaction date is December 23. Using this update statement I will move the Memo date into the TxnDate for this Invoice:

Update Invoice Set TxnDate = {fn CONVERT(Memo, SQL_TYPE_DATE)}  where RefNumber = '87'

Now when I rerun the query, the tranaction date is now December 30. I used the verify tag to reload the new values into the QODBC optimizer.

SELECT RefNumber, TxnDate, Memo FROM Invoice verify where RefNumber = '87'