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