$431: MS SQL Date fields

[Bastiaan Olij]

Hey Ben,

Date and datetime fields are actually stored as a timestamp, they are
simply displayed in whatever display format is set when you look at the
data with a SQL client.

When you select the data into Omnis and your column is a proper date or
datetime field (as it looks like it is), again Omnis stores the
information as a timestamp but retains some formatting information.
Still comparing dates and times should work properly, the display
date/time format shouldn't effect your filter.

That said, if your column is a date field, yet is loaded into a datetime
field the value stored will actually be something like midnight on that
date while #D is right now.

Comparing midnight August 27 to August 27 13:30pm may give you a
different result then the intended compare of just the date part.

I've found that making sure the column is a date field, not a datetime
field and load #D into a temporary variable which is a date field, works
a lot better then comparing datetime fields which hold a timestamp that
behind the scenes is stored by seconds.

P.S. regardless off the above, the date format set on the server can be
a PITA especially considering it is dependent on locales. We ran into
this when we were using Sybase many moons ago (which shared a common
ancestor with MS SQL).
There is a nice little SQL command you can execute right after you logon
to the database that will make your life easy:
set dateformat dmy

http://msdn.microsoft.com/en-us/library/ms189491.aspx

Cheers,

Bas

On 27/08/14 9:07 AM, Ben Price 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