st: Re: handling dates in odbc load exec

["Joseph Coveney" <jcoveney@bigplanet.com>]

David Hamilton wrote:

I am hoping someone can please provide some clarification on the use  
of exec SELECT WHERE in odbc load.  In the Data Management  
documentation for odbc load, it says that SQL data types are converted  
into Stata data types.  Does this just refer to the data format AFTER  
retrieving it from the database?   I'm trying to read in a subset from  
a very large Sybase data table (daily financial data for the past 20  
years) based on a date formatted variable, date.  However, I cannot  
seem to get the "SELECT * from tablename WHERE date>12/31/2008" to  
read in the subset correctly because I cannot seem to get the "WHERE  
date>12/31/2008" part right.  I've tried specifying the date WHERE a  
few ways with no luck.  Do I need specify the date as a Stata date? a  
string? a SQL date? Thanks in advance for the help.

--------------------------------------------------------------------------------

It might have more to do with how you've got Sybase set up and with its ODBC
drivers than with Stata's -odbc- commands.

To the extent that Sybase SQL Server and Microsoft SQL Server are still the
same, you could try something like that below, which works with the latter.  (I
broke up the SQL statement and placed it into a local macro variable in order to
prevent wrapping in the mail so that you can see the date's format in the WHERE
clause more clearly.)

You can also check out what others do with dates in predicates with Sybase: 

http://forums.databasejournal.com/showthread.php?p=69456 

http://www.selectorweb.com/sql_sybase.html

Good luck!

Joseph Coveney

. local Statement SELECT * FROM Northwind.dbo.Orders 

. local Statement `Statement' WHERE OrderDate <= '1996-07-06T00:00:00';

. odbc exec("`Statement'"), dsn(db1SQL)

    1. +---------------------------------------------------------+
       |OrderID        | 10248                                   |
       |CustomerID     | VINET                                   |
       |EmployeeID     | 5                                       |
       |OrderDate      | 1996-07-04 00:00:00.000                 |
       |RequiredDate   | 1996-08-01 00:00:00.000                 |
       |ShippedDate    | 1996-07-16 00:00:00.000                 |
       |ShipVia        | 3                                       |
       |Freight        | 32.3800                                 |
       |ShipName       | Vins et alcools Chevalier               |
       |ShipAddress    | 59 rue de l'Abbaye                      |
       |ShipCity       | Reims                                   |
       |ShipRegion     |                                         |
       |ShipPostalCode | 51100                                   |
       |ShipCountry    | France                                  |
       +---------------------------------------------------------+

    2. +---------------------------------------------------------+
       |OrderID        | 10249                                   |
       |CustomerID     | TOMSP                                   |
       |EmployeeID     | 6                                       |
       |OrderDate      | 1996-07-05 00:00:00.000                 |
       |RequiredDate   | 1996-08-16 00:00:00.000                 |
       |ShippedDate    | 1996-07-10 00:00:00.000                 |
       |ShipVia        | 1                                       |
       |Freight        | 11.6100                                 |
       |ShipName       | Toms Spezialitäten                      |
       |ShipAddress    | Luisenstr. 48                           |
       |ShipCity       | Münster                                 |
       |ShipRegion     |                                         |
       |ShipPostalCode | 44087                                   |
       |ShipCountry    | Germany                                 |
       +---------------------------------------------------------+



. odbc load, exec("`Statement'") dsn(db1SQL) clear

. format *Date %tcCCYY-NN-DD_HH:MM:SS

. list 

     +---------------------------------------------------------------+
  1. |  OrderID  |  Custom~D  |  Employ~D   |            OrderDate   |
     |    10248  |     VINET  |         5   |  1996-07-04 00:00:00   |
     |---------------------------------------------------------------|
     |        RequiredDate |         ShippedDate | ShipVia | Freight |
     | 1996-08-01 00:00:00 | 1996-07-16 00:00:00 |       3 |   32.38 |
     |---------------------------------------------------------------|
     |                  ShipName  |        ShipAddress  |  ShipCity  |
     | Vins et alcools Chevalier  | 59 rue de l'Abbaye  |     Reims  |
     |---------------------------------------------------------------|
     |      ShipRe~n      |      ShipPo~e      |      ShipCo~y       |
     |                    |         51100      |        France       |
     +---------------------------------------------------------------+

     +---------------------------------------------------------------+
  2. |  OrderID  |  Custom~D  |  Employ~D   |            OrderDate   |
     |    10249  |     TOMSP  |         6   |  1996-07-05 00:00:00   |
     |---------------------------------------------------------------|
     |        RequiredDate |         ShippedDate | ShipVia | Freight |
     | 1996-08-16 00:00:00 | 1996-07-10 00:00:00 |       1 |   11.61 |
     |---------------------------------------------------------------|
     |                  ShipName  |        ShipAddress  |  ShipCity  |
     |        Toms Spezialitäten  |      Luisenstr. 48  |   Münster  |
     |---------------------------------------------------------------|
     |      ShipRe~n      |      ShipPo~e      |      ShipCo~y       |
     |                    |         44087      |       Germany       |
     +---------------------------------------------------------------+




*
*   For searches and help try:
*   http://www.stata.com/help.cgi?search
*   http://www.stata.com/support/statalist/faq
*   http://www.ats.ucla.edu/stat/stata/