SQL DateTime Filter

You can use dates formatted as @D12, but why? MS-SQL stores   DateTime   ¬†fields as a numeric, somewhat like a Real.

For browses and reports, I always set my filter to be a local CSTRING variable.

Here is how I declare it:

SQLFilter     CSTRING(400)

Then, early in the procedure, I initialise it. If I want no filtering for by view, I set it to..

  SQLFilter = 'SQL(1=1)'


I may want NO records to show in a browse until the user has set some relevant settings.
Say the browse was of a financial transactions table, and I wanted only trans for the past week to show. Then I could set the filter thus

  SQLFilter = 'SQL( a.dtDate > ' & format( TODAY() - 7 -1,@D12-) & ')' 
          ! Dont forget the closing parenthesis.

Now if TODAY() returned January 23 2012, the WHERE clause for the SQLEngine will look something like

WHERE a.dtDate > '2012-01-15'

When that query hits the MSSQL Engine, the engine needs to convert the date string ‘2012-01-15’ back to a numeric which will then be the number 40921.00000
and thus the filter would be applied. Note that I deducted an extra 1 days for my CW filter as I do not stare any time data in the SQL DateTime fields. If I did also store times, then I would have needed to use

  SQLFilter = 'SQL( a.dtDate >= ' & format( TODAY() - 7 ,@D12-) & ')'

This would translate to the SQL Engine WHERE as

WHERE a.dtDate >= '2012-01-16'

and the SQL internal code translated to

WHERE a.dtDate >= 40922.00000

So, now we have Clarion calling the FORMAT function, and the MSSQL Engine doing an implicit conversion of a string date to a numeric. The SQL Engine first need to determine if the date string is ‘yyyymmdd’ or ‘yyyy-mm-dd’ or any other standard date string formats that it can recognize. All these conversions can be eliminated by sending the SQL Engine a suitable numeric direct from CLARION. Let’s cut out the two middlemen by trying..

  SQLFilter = ' a.dtdate > ' &  (TODAY() -7 -1 )  & ')'

When we run our browse now, we get no transactions, but we know there are some in the database that are dated since 15 Jan 2012. Something is broken. Easy fixed..

CLARION date numbering starts at the date 28-December-1800 and MSSQL datetime numbering starts at 1 January 1900. What we should have done is adjust the CLARION numeric by the number of days between 1-Jan-1900 and 28-Dec-1800. This happens to be a difference of 36163 days.

As we may be adjusting these dates throughout out program and the number of difference days will never change, we will declare it as an   EQUATE  

DATE1900    EQUATE(36163)

Apply this to our filter string and all should now work as expected, and may just be a tad quicker as we have bypassed a couple of data conversions.

SQLFilter = ' a.dtdate > ' &  (TODAY() -7 -1 - DATE1900 )  & ')'

This will now give us our expected dataset.

Why I Think This is Better

  1. We bypass at least TWO data conversions
  2. We don’t need to worry about the ” quotes
  3. It works great with DateTime fields.

 If you are working with the newer DATE type fields (introduced in MSSQL2008) then you need to make some adjustments to the techniques shown above. You would need to use the SQL DateDiff() function to convert those DATE fields to 4 byte integers, so that they can be directly compared to the (adjusted by -36163) numeric CW Date integer. Then you also need to be careful not to work with DATE fields where values are outside the range allowed for DateTime fields. 

 Date fields in MSSQL2008 and later are stored in 3 Byte fields. 

  John Griffiths