Formatting dates in a cursor

[CFB]

I've created a cursor by querying an Oracle table. In the Oracle table I'm querying, the date format is short date. When I create my cursor it seems that FoxPro is formatting the date to a long date. I'm trying to display the cursor in a grid with the short date. Does anyone have any suggestions as to how I can either format the date in the cursor or force the grid to format the date to short date format? Thanks.

 

[jonscott8]

Hi CFB,

Have you SET CENTURY OFF? Jon Hawkins

The World Is Headed For Mutiny,
When All We Want Is Unity. - Creed

 

[ramani]

SET CENTURY OFF before the grid is shown will do the trick as JonHawkins pointed out. I am adding this for little more explanation... The date field is always in long format. SET CENTURY OFF on ON control the date display format.

Also knowing the format you want to display the date can also be helpgful to you. You can sat SET DATE BRITISH for DDMMYY format or SET DATE YMD or SET DATE MDY or SET DATE DMY etc. etc.

If you are want to set this in all your programming without variation, you can do it by setting in in your Environment configuration section by SET CENTURY OFF and SET DATE DMY etc.

You can also set it by using TOOLS/OPTIONS/REGINAL tab of the main VFP menu choice and set the choices as default instead of in your programme.

You can opt whichever suits you.

ramani
(Subramanian.G)
FoxAcc
ramani_g@yahoo.com

 

[CFB]

I've SET CENTURY OFF and SET DATE MDY and I'm still getting a date time value in the cursor I'm creating. This is the first time I've run into this problem before, normally I can format my dates with no problem.

 

[ramani]

Check that the field is only a date field not a date time field.

ramani ramani
(Subramanian.G)
FoxAcc
ramani_g@yahoo.com

 

[ramani]

Check that the field is only a date field not a date time field.
ramani
(Subramanian.G)
FoxAcc
ramani_g@yahoo.com

 

[CFB]

When I look at the table description in Oracle, it is only a date field. When I query the table in Oracle I get a date value, not a date time value.

 

[ramani]

I suggest you try giving in the SQL query ..
SELECT *, mydatefield+0 as mynewdate etc to check what happens

ramani
(Subramanian.G)
FoxAcc
ramani_g@yahoo.com

 

[CFB]

I think this is the test you wanted me to do. Here's the query I sent off to Oracle:

"select mydate+0 as mynewdate from tablename"

I get the same result, mynewdate is returned as a date type value. I've also tried formatting the dates through Oracle using TO_CHAR AND TO_DATE, but any date that is returned in my cursor gets turned into a date time data type. If I use TO_CHAR to format the date and change it into a character value, it looks correct in the grid. The problem is that it's now a character data type and not a date data type so it changes the format of the text box in my grid from a date format to a character format.

 

[Guest_imported]

The oracle (as well as SQL Server) ODBC Drivers return Datetime values for dates. You must change your select to get just the date portion like select.., ttod(<datefield>) as justdateonly, .... INTO CURSOR ....


HTH,

Robert McNeal

 

[CFB]

Ah...ok, that makes more sense now. I normally use the values that are returned in a different manner than this. The problem with doing another select on the cursor that I created with my SQLEXEC statement is that the new cursor I create will be read only. I need to be able to edit the values that are displayed in the grid.

 

[CFB]

I think I've got it. Instead of creating another cursor I created a temporary table which I can edit. Thanks for all the help everyone.

 

[krisb]

You can also:

Alter table A alter column datetime d(8)

Instead of creating a whole other table, just alter the one that already exists.

K-