Printed Icetips Article

Somebody made a really stupid decision when designing SQL when he invented the Date-Time type. Two values in one field? Hey, let's have a firstname-lastname type, and a city-state-country-postalcode type! Why stop with one stupid idea?

But it's done, and we have to live with it.

In the demo app at http://www.icetips.com/downloadfile.php?FileID=59 you can see one simple way of handling a date-time column for a browse queue. In the UpdatePlayers procedure, there is a browse for "At bats". The first column in the browse is a date filed. Because the queue will be filled by the QueryFromQueue procedure, some special thought has to be given to the queue structure.

To handle a date, declare your queue like this:

atbatqueue    queue
Date            string(10), name('atbats.date')
OppTeam         like(tea:teamname), name('teams.teamname')
Result          string(10), name('atbats.result')
AtBatID         like(atb:atbatid), name('atbats.atbatid')
              end

The date field in the queue is declared as string(10) simply because that's what you are going to get for the value, in the format "yyyy-mm-dd". There can also be a time portion to that, but I'll get to that below.

After you fill the queue from the query, do something like this:

loop i=1 to records(atbatqueue)
  get(atbatqueue, i)
  atbatqueue.Date = deformat(atbatqueue, @d010-)
  atbatqueue.Result = AtBatResultToString(atbatqueue.Result)
  put(atbatqueue)
end

That converts the SQL date format to a standard date format that Clarion handles, and you can use any picture in the browse for that column that you want.

Don't worry too much about handling date-time fields for updates. The standard Clarion update procedure works pretty well for doing all the conversions.

If you need the time portion as well, it's another one line of code. Suppose this queue:

MyQueue    queue
MyDate       string(20), name('mytable.date')
MyTime       long, name('''''')
           end

Note the name attribute for the MyTime field. Your query, when generated by the QueryFromQueue procedure, will include '' in the SELECT portion. How much bandwidth does a blank string take? I don't know, but I suspect it's not much. When you have a field in the queue which you will set later yourself, you can set the name atrribute like that. Or you could set it to a default value. I don't think there is much difference one way or the other, since you'll be processing it later anyway.

Also notice that the MyDate field is now string(20). That's so it can hold the full date-time combination from the result set.

Now to get the date and time fields converted in the queue. Be sure to convert the time field first, because when you convert the date, you'll wipe out the time portion. Do it like this:

loop i=1 to records(MyQueue)
  get(MyQueue, i)
  MyQueue.MyTime = deformat(MyQueue.MyDate[12 : 19], @t4)
  MyQueue.MyDate = deformat(MyQueue.MyDate[1 : 10], @d010-)
  put(MyQueue)
end

Now look back at the atbatsqueue declaration. It has this field:

Result        string(10), name('atbats.result')

In the atbats table, the result column is a numeric. In practice, it should contain only the values 0,1,2,3, or 4. So why not declare the Result field as a byte in the queue? Because we want to hold something descriptive, something like "Out", "Single", "Double", "Triple", or "Home Run."

Immediately after the query, the Result field holds the number that is in the table row. While converting the date column for the queue, the code in the SendAtBatsQuery routine also converts that number to the string representation so it can display in the browse.

Now back to date-time fields. Don't rely on something like this:

  SELECT CUSTOMER WHERE LASTBILLED >= '2000-01-01' and
  LASTBILLED <= '2000-01-31'

The first part of the condition is okay. But what if there is a time portion to the LASTBILLED column? You will miss the records that have a value of '2000-01-31 14:00:00'.

The correct way to do that is to look for values that are less than the target date plus one date. For example, if you want the dates up through 2000-01-31, then it would be

   LASTBILLED < '2000-02-01'

Comments...
23-July-2002 -- Michael Gould

That depends on the SQL backend. MS-SQL has it's roots in Sybase ASE 4.2 and they only have a single timestamp type of column. We use Sybase's ASA 8.0.1 and it has column types of timestamp, date and time. We use all three formats depending on what the need for that is. Works well within Clarion also. If your using just the date or time, there is nothing special that needs to be done, except for formatting the date when doing a prop:sql but other than that, it's a breeze.

Comments...
23-July-2002 -- Gus M. Creces

Another way to handle a compound date/time field when querying only the date is as follows:

ORD.ORDERDATE BETWEEN {ts '1994-09-30 00:00:00'} AND {|
 ts '1994-09-30 23:59:59'} )

The new SQL extensions to The Clarion Handy Tools handle date stamps in this way.

The classes detect when the back end is using a date stamp data type. If you're doing a query involving only the date part of this, it intercepts your query statement (date = 9/30/1994) and expands it to include the highest and lowest possible times in a range. You don't really have to think about this since the work is done for you in the background.

We've added some easy to use functions to our browse class extensions that let you keep your querying statements in pure Clarion if you want to:

OBJ.AppendSQLFilter('ORD:ORDERDATE = ' &  YourDateValue)
OBJ.ReplaceSQLFilter('ORD:ORDERSATE = ' & YourDateValue)

The back end receives

ORD.ORDERDATE BETWEEN {ts '1994-09-30 00:00:00'} AND |
 {ts '1994-09-30 23:59:59'} )

The classes are aware that it is possible to change the date formats used by various back end data bases and allow you to indicate by template configuration or at run time how the back end is formatting dates. So the SQL output string is adjusted accordingly.