SQL Datetime in VFP Textboxes

[stanlyn]

Hi,

1. From a view, a sql row that is not assigned a datetime) is showing in a VFP textbox as NULL (expected). If I change the value in the textbox to empty, then VFP textbox is empty (expected), however when saved to sql and reload, the textbox now shows "1900-01-01 00:00:00.0000000" (not expected). I need it to show empty.

2. What do I enter into a textbox that will update and show the SQL field as NULL again?

3. What format is best for viewing the datetime() format returned from the view instead of: "1900-01-01 00:00:00.0000000" I need to drop the ".0000000" from their ends.

Thanks,
Stanley

 

[Mike Lewis]

Stnaley,

When you talk about a "SQL row" or a "SQL field", you really need to specify what you mean. I would guess you are referring to data in a SQL back-end database. If that's so, you need to be aware that different back ends treat null or empty dates in a different way.

So are you talking about MySQL, or SQL Server, or Oracle? Or what?

As a generalisation, back-end databases don't support the concept of an empty date. If the date field has no value, you need to store it as a NULL. By default, VFP will then show the contents of the field as the word [tt].NULL.[/tt] (including the dots). To avoid that, you need to [tt]SET NULLDISPLAY TO ""[/tt].

As for your second question, the answer depends on how you update the back end from VFP. With a remote view? Or by sending UPDATE commands? Or waht. So, again, you need to clarify your question.

In your third question, those trailing zeroes won't be an issue if you are storing your date as NULLs.

Mike



__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Olaf Doschke]

I assume you're using MSSQL Server backend. It doesn't know an empty date, but CAST('' as datetime) will be 1900-01-01 00:00:00.000:

You should query a datetime field as datetime field, there is no need to cast or convert it to a string, VFP has the datetime type, too and it also can be null.
What control do you use for data display and modification? A textbox will show NULL as .NULL., if you don't do anything to that it also remains NULL and won't turn into an empty string. You can SET NULLDISPLAY TO '' to get rid of the .NULL.

So that makes me wonder what you work with. It's as simple as can be to bind to a cursoradapter getting the field 1:1 as is in SQL Server. To specify how you want dates to display you set the dateformat property of a tetbox and don't do any ttoc or other conversions.

Bye, Olaf.

 

[stanlyn]

Hi Mike,

When you talk about a "SQL row" or a "SQL field", you really need to specify what you mean. I would guess you are referring to data in a SQL back-end database.

A SQL row translates to a VFP record, and a field is a field...

So are you talking about MySQL, or SQL Server, or Oracle? Or what?

Yes, I'm using a MSSQL backend.

As a generalisation, back-end databases don't support the concept of an empty date. If the date field has no value, you need to store it as a NULL. By default, VFP will then show the contents of the field as the word .NULL. (including the dots). To avoid that, you need to SET NULLDISPLAY TO "".

This is the reason for my #2 question above. What do I enter into the VFP textbox to update the SQL field back to .NULL. so that it reappears again in the VFP textbox as a .NULL.?

As for your second question, the answer depends on how you update the back end from VFP. With a remote view? Or by sending UPDATE commands? Or waht.

The data comes in from a remote view and when updating I issue a TableUpdate command, then a requery(). I will be adding another question about changing this as it really isn't what I'd like, however I did get it to work.

In your third question, those trailing zeroes won't be an issue if you are storing your date as NULLs.

I need to make the textboxes shorter and the trailing zeros are eating screen space and really doesn't need to be displayed. I need to display it the same as VFP displays its native datetime() result. Also, are you saying that vfp date and time functions works on the sql date and datetime fields without conversion when doing VFP date and time math?

Thanks Stanley

 

[Olaf Doschke]

What do I enter into the VFP textbox to update the SQL field back to .NULL. so that it reappears again in the VFP textbox as a .NULL.?

You don't enter something, you then don't SET NULLDISPLAY TO "", but keep it at the default or you SET NULLDISPLAY TO without giving any reeplacement display string. But the display won't change the value anyway, what is null is displayed as you say and doesn't change to that sting, it is kept at .NULL.

Besides that you can .NULL. a textobx by typing CTRL+0, so if you get a datetime and want to set it NULL you can use CTRL+0.
For safety reasons I rather recommmend to suppress that hotkey, as it's doing more harm than it helps, so instead I rather recommend ON KEY LABEL CTRL+0 *, which means you suppress this hotkey. Then rather add some button to REPLACE field WITH .NULL. or do that in the textbox doubleclick.

are you saying that vfp date and time functions works on the sql date and datetime fields without conversion when doing VFP date and time math?

Yes. If you don't use any cast or convert in your remote view, simply have a SELECT * or list of SQL Server table fields, a datetime field is read into a VFP datetime field and not converted to a string. You then also don't get .000, as it is a VFP datetime.

You only would need to convert SQL types, if VFP doesn't have an equivalent type. Whatever you do now,as you get .000 you must do some cast or convert in your remote view.

Bye, Olaf.

 

[Dan Freeman]

Disabling CTRL-0 is a good idea.

I recently spent time maintaining an app that used CTRL-O (the letter O) as a menu shortcut. Some users reached a little wide and added nulls in fields. It took a long time to figure out where they were coming from.

(Making things worse, DISP STRU showed the fields not nullable while LIST STRU showed them nullable. It was weird.)

 

[stanlyn]

Hi Olaf,

but CAST('' as datetime) will be 1900-01-01 00:00:00.000:

Yes, this is what I'm seeing. I am not using any cast or conversions of any kind in either VFP or SQL. I'm also using generic textboxes with no formatting.

If we change the .NULL. datetime value to something else and change it back to empty or blanking it, then we get "1900-01-01 00:00:00.000" value. Id rather have it .null. than a real datetime, as .null. signifies that it is unknown, while "1900-01-01 00:00:00.000" signifies that it is known and is actually "1900-01-01 00:00:00.000" which is really blank or empty.

Besides that you can .NULL. a textobx by typing CTRL+0, so if you get a datetime and want to set it NULL you can use CTRL+0.
For safety reasons I rather recommmend to suppress that hotkey, as it's doing more harm than it helps, so instead I rather recommend ON KEY LABEL CTRL+0 *, which means you suppress this hotkey. Then rather add some button to REPLACE field WITH .NULL. or do that in the textbox doubleclick.

Thanks as this is what I needed...

Thanks,
Stanley

 

[Olaf Doschke]

You may not do anything by intention, but the way you describe what happens and what values you get you (or anything else) seem to convert datetimes to strings and also puts them back in string form, as that would explain getting 1900 dates.

Bye, Olaf.

 

[atlopes]

Exchanging date-based values between MSSQL and VFP also depends on the ODBC driver that is being used, and the actual datatype of the SQL backend.

For reference, this is a summary of how MSSQL date-based datatypes are read into VFP by some of MSSQL ODBC drivers (by Microsoft and by Devart).

Driver: "SQL Server" (Microsoft)
- Date read as Character
- Datetime read as Datetime
- Datetime2 read as Character
- Smalldatetime read as Datetime
- Time read as Character
- Datetimeoffset read as Character

Driver: "SQL Server Native Client 10.0", "... 11.0", "ODBC Driver 13 for SQL Server" (Microsoft)
- Date read as Date
- Datetime read as Datetime
- Datetime2 read as Datetime
- Smalldatetime read as Datetime
- Time read as Character
- Datetimeoffset read as Character

Driver: "Devart ODBC Driver for SQL Server" (Devart)
- Date read as Datetime
- Datetime read as Datetime
- Datetime2 read as Datetime
- Smalldatetime read as Datetime
- Time read as Datetime
- Datetimeoffset read as Datetime