$431: MS SQL Date fields

[Phil Sparmilk]

Hi, Ben,

I bet the key is in your select statement. You can use a date format function to prepare the proper format as part of the SQL query. Another method is to set the definition of iList to include appropriate Omnis date fields. 

If you provide the select statement or code, it may be easier to offer a solution.

I hope this helps.

 - Mark Phillips
 
On Aug 26, 2014, at 4:07 PM, Ben Price <ben at clintelsystems.com> wrote:

> 
> Hi List,
> 
> I have only just joined the Omnis language again after a 2 year break in the solar industry and not sure if my omnisdev-en list is working correctly so please be nice while I get back up to speed with the omnis list.
> 
> I have a problem with Dates.
> 
> MS SQL 2008 r2 stores dates in 'y-m-D' format and I need them to be in 'D-m-y' format
> 
> I need this after Statement.$fetch(lList,kfetchall)
> 
> The reason I need this is because for some reason I cannot compare dates eg llist.date>#D after the fetch with llist.date being in 'y-m-D' format.
> 
> UPDATE:
> 
> right after
> 
> Statement.$fetch(lList,kfetchall)
> 
> I have to do:
> 
> plist is a row/list field reference
> l is a long int
> x is a long int
> ldate is a datetime, Date Time D m Y
> 
> For l from 1 to plist.$colcount() step 1
>    If plist.$cols.[l].$coltype=kDate&plist.$cols.[l].$colsubtype<>kDatetime
>        For x from 1 to plist.$linecount step 1
>            If plist.$linecount=1     ;; Its a row so access is different
>                Calculate ldate as jst(plist.[l],'D m y')
>                Calculate plist.[l] as ldate
>            Else
>                Calculate ldate as jst(plist.[x].[l],'D m y')
>                Calculate plist.[x].[l] as ldate
>            End If
>        End For
>    End If
> End For
> 
> which fixed the issue but will be a big performance hit.
> 
> There must be some overriding ODBC / session or statement setting for getting this right but I can't find it. Anyone with any ideas?
> 
> -- 
> All the best,
> 
> Ben Price
> 
> 
> 
> 
> _____________________________________________________________
> Manage your list subscriptions at http://lists.omnis-dev.com

Phil Sparmilk
email: phil at mophilly.com

"Debugging is twice as hard as writing the code in the first place. Therefore, if you write the code as cleverly as possible, you are, by definition, not smart enough to debug it."
-- Brian W. Kernighan