Knowledgebase
[QODBC-Desktop] How are dates formatted in SQL queries when using the QuickBooks generated timestamps
Posted by brad waddell on 12 March 2009 05:21 PM

Date and TimeStamps Format Used for Normal

Note: If you have any problems with the date format, use this format for direct SQL calls to our driver: {d'YYYY-MM-DD'}  

For Example, {d ‘2006-01-27’}

Examples

Date Format

SELECT * from InvoiceLine WHERE TxnDate >= {d '2005-09-23'}

Time Stamps Format

SELECT * FROM Customer WHERE TimeCreated = {ts '1999-07-29 14:24:18.000'}
SELECT * from InvoiceLine WHERE TimeModified >= {ts '2005-09-23 00:00:00.000'}
 

Date and Time Stamps Format Used for Microsoft Access Queries

Examples

Date Format

SELECT * from InvoiceLine WHERE TxnDate >= #9/23/2005#

Time Stamps Format

SELECT * FROM InvoiceLine WHERE Invoice.TimeModified >= #2003-09-23 00:00:01#

Note: The Access format may vary based on the setting for your default date format for your region in your Windows control panel.
 

Date and Time Stamps Conversion

Note: If you need to convert a Timestamp to a Date, or a Date to a Timestamp, use the following CONVERT function.

Examples

Date and TimeStamps Conversion for Now

SELECT {fn CONVERT({ts '2005-09-23 00:00:00.000'}, SQL_TYPE_DATE)} as ToDate,
{fn CONVERT(TxnDate, SQL_TIMESTAMP)} as ToTimeStamp, RefNumber FROM Invoice
 

Date and TimeStamps Used in Queries

Convert Date to the Past

To convert today's date to eight months ago, do the following.

select {fn Curtime()}, TxnDate,SubTotal from Invoice NOSYNC
where TxnDate> {d'2007-08-01'}
and TxnDate> {fn CONVERT( {fn TIMESTAMPADD(SQL_TSI_MONTH,-8, {fn CURDATE()} )}, SQL_DATE)}

Display Modified Records in a Period of Time

This will display modified Sales Order lines from 15 minutes ago.

Select * from SalesOrderLine Calldirect where timemodified >= {fn TIMESTAMPADD(SQL_TSI_MINUTE, -15, {fn NOW()})}

Display Today's Modified Records

While the below will display today's modified Sales Order Lines.

Select * from SalesOrderLine CallDirect where timemodified >= {fn CONVERT({fn Curdate()}, SQL_TIMESTAMP)}

See QODBC Function List for more about related Date functions.

 

Keywords: ts, timestamp, time stamp, date format, time format

(170 vote(s))
Helpful
Not helpful

Comments (1)
Jodecy
20 September 2012 06:35 AM
It's a pleasure to find someone who can think so calerly
Post a new comment
 
 
Full Name:
Email:
Comments:
CAPTCHA Verification 
 
Please complete the captcha below (we use this to prevent automated submissions).