DateTime from SQL Server to FP Cursor

[TonyScarpelli]

I have a SQL Server table that came from a FoxPro table.

The Date fields are set as SmallDateTime and when listed show up as MM/DD/YYYY.

In the FoxPro application STRICTDATE is set to 0.

When I pull the SQL table into a FoxPro cursor, the dates show up as MM/DD/YYYY HH:MM:SS or as a Datetime field.

When I show these fields on a form, they display as MM/DD/YYYY HH:MM:SS, I don't want the time part, I just want MM/DD/YYYY.

The textbox control's properties I have on the form show the Inputmask as ##/##/#### and there is no Format, but when run, the textbox still has the time in it.

I've tried setting the format to ##/##/####, 99/99/9999 and the maxlength to 10 with no change.

Is there a way to set this control so that only the date shows and not the time?

Thanks.


Tony Scarpelli
Clinical Engineering Dept.
Maine Medical Center
Portland, Maine 04102

 

[JimWWinter]

Tony,

Would the TTOD() function work for you?

Regards,
Jim

 

[TonyScarpelli]

TTOD()?
Maybe. Where would it go on a form? Or in the control?

Thanks.


Tony Scarpelli
Clinical Engineering Dept.
Maine Medical Center
Portland, Maine 04102

 

[Mike Lewis]

Tony,

TTOD()?
Maybe. Where would it go on a form? Or in the control?

Neither. It would go in the SELECT statement that creates the cursor:

SELECT Field1, Field1, TTOD(MyDateField) FROM ... etc.

However, that won't work if the cursor is an updateable remote view and you want to update the underlying datetime field.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

My sites:
Visual FoxPro (www.ml-consult.demon.co.uk)
Crystal Reports (www.ml-crystal.com)

 

[MarciaAkins]

Hi Tony.

If you need to be able to update the DateTime field, here is a little class that does what you want - displays only the date portion of the field:

**************************************************
*-- Class:        txtdate 
*-- ParentClass:  txtbase 
*-- BaseClass:    textbox
*
DEFINE CLASS txtdate AS txtbase


  ALIGNMENT = 3
  VALUE = {}
  FORMAT = "YL"
  *-- Used to store the actual DateTime ControlSource of the control so we can enter Data Values and update the "real" datetime controlsource manually behind the scenes
  ccontrolsource = ""
  NAME = "txtdate"


  *-- Called from the Valid to update the field specified in the cControlSource property from the control's value. It expects the specified field to of data type datetime.
  PROCEDURE updatecontrolsource
  LOCAL ldValue, lcField, lcAlias

  ldValue = IIF( VARTYPE( THIS.VALUE ) = 'T', TTOD( THIS.VALUE ), THIS.VALUE )
  lcField = JUSTEXT( THIS.ccontrolsource )
  lcAlias = JUSTSTEM( THIS.ccontrolsource )

  *** Update cControlSource from the Control's Value
  IF NOT EMPTY( THIS.ccontrolsource )
    IF EMPTY( NVL( ldValue, {} ) )
      *** Check to see if we are updating a form property
      IF UPPER( LEFT( THIS.ccontrolsource, 4 ) ) == 'THIS'
        lcControlSource = THIS.ccontrolsource
        &lcControlSource = {/:}
      ELSE
        REPLACE ( lcField ) WITH .NULL. IN ( lcAlias )
      ENDIF
    ELSE
      *** make sure we have a valid date
      *** so we do not get datetime overflow errors
      *** in sql server when we attempt an update
      IF NOT( UPPER( LEFT( THIS.ccontrolsource, 4 ) ) == 'THIS'  )
        IF BETWEEN( ldValue, {^1900-01-01}, {^3000-01-01} )
          REPLACE ( lcField ) WITH DTOT( ldValue ) IN ( lcAlias )
        ELSE
          MESSAGEBOX( 'Invalid Date', 16, 'Please Fix Your Input' )
          RETURN .F.
        ENDIF
      ELSE
        lcControlSource = THIS.ccontrolsource
        &lcControlSource = DTOT( ldValue )
      ENDIF
    ENDIF
  ENDIF
  ENDPROC


  *-- Refreshes the control's (DATE) value from the datetime value conained in the field specified in its cControlSource property
  PROCEDURE refreshvalue
  LOCAL ltValue

  IF NOT EMPTY( THIS.ccontrolsource )
    ltValue = EVALUATE( THIS.ccontrolsource )

    *** Update the control's value from its cControlSource
    IF EMPTY( NVL( ltValue, {/:} ) )
      THIS.VALUE = {}
    ELSE
      IF VARTYPE( ltValue ) = 'T'
        THIS.VALUE = TTOD( ltValue )
      ELSE
        THIS.VALUE = ltValue
      ENDIF
    ENDIF
  ELSE
    THIS.VALUE = {}
  ENDIF
  ENDPROC


  PROCEDURE INIT
  DODEFAULT()

  *** Unbind the control and save the controlsource to a special property
  IF NOT EMPTY( THIS.CONTROLSOURCE )
    THIS.ccontrolsource = THIS.CONTROLSOURCE
    THIS.CONTROLSOURCE = ''
  ENDIF
  THIS.FORMAT = 'D'
  THIS.VALUE = {}
  ENDPROC


  PROCEDURE VALID
  IF NOT THIS.updatecontrolsource()
    RETURN 0
  ENDIF
  ENDPROC


  PROCEDURE REFRESH
  DODEFAULT()
  THIS.refreshvalue()
  ENDPROC


ENDDEFINE
*
*-- EndDefine: txtdate
**************************************************

Marcia G. Akins

http://www.TightlineComputers.com

 

[TonyScarpelli]

Thanks, I'll give it a try.

The "SELECT Field1, Field1, TTOD(MyDateField) FROM..." might be all right, but I have some tables with lots and lots of fields, and I'd have to re-write the select statement in all my apps from "SELECT * ..." to putting in all the field names. For small tables it would be all right though.

What I'm really wondering is why the Format and InputMask properties aren't working. Do we have a bug in FoxPro 9 or is there a setting somewhere that's not working.

The "txtdate" class might be all right, I'll have to try it some time today, however, again, the properties in the controls should be doing this. Are others having the same problem?

Thanks again.

CU




Tony Scarpelli
Clinical Engineering Dept.
Maine Medical Center
Portland, Maine 04102

 

[Mike Lewis]

Tony,

What I'm really wondering is why the Format and InputMask properties aren't working. Do we have a bug in FoxPro 9 or is there a setting somewhere that's not working.

The Format and InputMask don't distinguish between dates and datetimes. They are more concerned with the actual characters that can be entered or are displayed -- such as numerics, spaces, minus signs, and the like.

In any case, this is not specific to VFP 9.0 -- the behavioiur has been the same since VFP 3.0. It's not a bug, but I agree that it would desirable for Microsoft to do something about it.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

My sites:
Visual FoxPro (www.ml-consult.demon.co.uk)
Crystal Reports (www.ml-crystal.com)

 

[TonyScarpelli]

Thanks.

I guess one other option is to make sure the date in SQL Server tables is a character instead of a datetime field, and then do the stuff I currently do and that is have my users always see the date as 'Dec 15, 2005'. They enter the date in any format they want and it always is displayed as the above format. The date is always validated that way before it gets into the table.

This is what I did when we changed from 1999 to 2000 and everyone wasn't sure what the millenia would do to dates. Since then I've been usin a couple functions that goes from one format to another and it always validates to the correct format. Since I'm just starting with VFP and SQL Server I think I'll do that and see what happens.

CU


Tony Scarpelli
Clinical Engineering Dept.
Maine Medical Center
Portland, Maine 04102

 

[Mike Lewis]

Tony,

I guess one other option is to make sure the date in SQL Server tables is a character instead of a datetime field

The problem with that is that you won't be able to easily perform date comparisons or date arithmetic, nor can you easily respect your user's Control Panel choices for how the date is displayed.

In fact, it seems like quite a drastic solution to what should be a relatively simple problem.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

My sites:
Visual FoxPro (www.ml-consult.demon.co.uk)
Crystal Reports (www.ml-crystal.com)

 

[TonyScarpelli]

Your're so right. I tried it last night and found that it wouldn't sort right either. Had to try it, though.

Think I'll try the txtdate class above and see what happens with that.

I'm also trying to do stored procedures with SQL Server from FP and I'm trying to set the sort order from within the stored procedure. What a bugger that is. As you know, in FP we just do a SET ORDER TO IndexName, and we can use a variable here. In SQL Server you can't pass a variable to the sort order:

'ORDER BY @SortOrder'

just doesn't work, you have to do a CASE statement after the ORDER BY and send in an integer; it's not pretty. If I want to set up returned records in different sort orders I would have to have 8 different sort order procedures, again not pretty, since it makes for too much handling of code. (Of course I could bring in the whole table set and sort the cursor in FP. I'll try that today and see what happens.)

FoxPro and it's tables is so much better with this kind of thing. But I have to start using SQL Server and I have to go to .NET which is a whole other story.

Good day.

CU


Tony Scarpelli
Clinical Engineering Dept.
Maine Medical Center
Portland, Maine 04102

 

[Mike Lewis]

Tony,

Rather than having a CASE with eight different ORDER BY clauses, you can construct the entire SELECT statement programmatically, store it in a variable, and then execute it with EXECUTE @<variable>. It works in much the same way as VFP's macro expansion.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

My sites:
Visual FoxPro (www.ml-consult.demon.co.uk)
Crystal Reports (www.ml-crystal.com)

 

[TonyScarpelli]

Thanks, but I don't think I'm going to need that now.

I've been working on the sorting today. I had two problems that I was able to solve within FoxPro which is better at this stuff than SQL Server.

When the data entry screen loads, I just send a command to the sql server to run a stored procedure to pull in all the records without any sorting. This is pretty fast. Since it comes into a cursor, I then create an index with the required initial sort. Of course the fields on the form are bound to the cursor fields.

I also have a form that allows users to pick the sort order they want. This also creates a new index based on the sort order set up in the datadictionary table. Since this application can use both FoxPro and/or SQL Server tables most of the code is the same except for the data access parts.

It's the end of the day and so far it works great. I stopped trying to fool around getting the sorting done on the SQL Server. It just wasn't worth it. In my other applications I know exactly what sort order I need so never have to bother with the ORDER BY whatever.

Much of my data access code is based on the Pinter book _Visual FoxPro to Visual Basic .NET_, and lots of the coding I'm doing now is based on the CodeBook stuff I've been using in FP 2.6 which I still have to do for now.

So now off to home, and then to DateTime fields.

Thanks again.

CU


Tony Scarpelli
Clinical Engineering Dept.
Maine Medical Center
Portland, Maine 04102

 

[Mike Lewis]

Tonya,

Glad you've got on top of the sorting problem. We're always being told to do as much of the sorting, grouping, etc as possible on the server, but often it's easier just to get the records into a cursor and do it in VFP. I'd probably have done the same in those circumstances.

Mikw

__________________________________
Mike Lewis (Edinburgh, Scotland)

My sites:
Visual FoxPro (www.ml-consult.demon.co.uk)
Crystal Reports (www.ml-crystal.com)

 

[TonyScarpelli]

MarciaAkins

Thanks for the txtdate class. I've installed it into one of my libraries and it works just fine.

I've been using the Office Calendar control inside a form that allows the user to pick dates, and had to modify it's results a bit since it's return is just a date and the data going back to the cursor is date/time.

Everything working out bit by bit.

Thanks again.

CU


Tony Scarpelli
Clinical Engineering Dept.
Maine Medical Center
Portland, Maine 04102