Hi everyone.

Anyone knows how to store in the database a DateTime as a Date-only value (e.g., store 2010/02/09 00:00:00 in the SQL field)?

I have a (simple!) requirement to filter out a table based on two dates, but the SLX Web Client and/or the OLEDB provider are conjuring against me!

Whatever I try, I keep getting UTC-converted datetimes.

Thanks to everyone wanting to help!

Alberto Chiesa

Yes. In Architect DBMgr - always set the type to Date (not Date/Time). Whilst it's stored as a date-time it will lose the time portion (00:00:00.000). If you cannot change/drop the field then simply do this:

update sectabledefs set datetimetype = 'D' where tablename = 'yourtable' and columname = 'yourcolumn'

Then restart the slxserver.

Hi Mike! Thank you.

Unfortunately, I already did it!!! Apparently, the web DateTimePicker control does not cope well with the DateOnly option.

The little bastard is still storing with my SLX User Timezone (-2). I almost forgot, I'm on SLX Web 7.5.4.

I'm kinda lost, at the moment. Should I set something else at web form configuration?

Anything else I could check?

Thank you again.

Alberto

Oh! Well, the provider (underneath) should honour this - not the control really ? That's very odd ! What is that you are trying to do (and from where) - as you maybe able to use Convert() instead and reformat it without the time etc.

Quote: Originally posted by Alberto Chiesa Unfortunately, I already did it!!! Apparently, the web DateTimePicker control does not cope well with the DateOnly option.

I have ran into this issue for quite a while, you need to do 2 things:

a) Properly set the Field definition (as shown on a Post Above)

b) On the TimePicker Control, set the following 2 properties as follows:

    1.- DisplayTime:  False (This is the Default)

    2.- Timeless:  False  (The default is True)

Thank you guys!

The suggestion from Raul is on the right track. I tested thorougly the issue and found that:

1. Setting the 'D' on the sectabledefs changes the oledb provider workings: it didn't change the field value anymore.


2. If you only change that, the DateTimePicker will still apply conversions for the User's TimeZone, so it will SEEM that the oledb provider is messing with the date.


3. The Timeless option should be set to TRUE (not false) in order to have the DateTimePicker disregard the TimeZone conversion thing: it will always set the SQL DateTime to 00:00:05 (5 seconds after midnight, don't ask me why...)

Having all my dates set to 5 seconds after midnight is good enough for my requirement.

Thank you much, guys, you're great!

Alberto

Quote: Originally posted by Alberto Chiesa The Timeless option should be set to TRUE (not false) in order to have the DateTimePicker disregard the TimeZone conversion thing: it will always set the SQL DateTime to 00:00:05 (5 seconds after midnight, don't ask me why...) Having all my dates set to 5 seconds after midnight is good enough for my requirement.

The 5 seconds past Midnight is how SLX knows a Date vs a DateTime for Conversion purposes (at least this is how it does it on the Activity table when the activity is set as "Timeless"), so we expect those 5 seconds past midnight to mean it is a Date with no Timestamp.

That's 'Z' time......SLX arbitrary convention to  ignore GMT \ time for DateTime type DB fields.