Displaying dates only part of datetime (from sql server)

[MathiasB]

Hi all,

I am looking for suggestions even if it means creating a new class. I have dates displaying in date text field like this: 22/11/17 00. Data is in SQL Server which explains the datetime format. How can I suppress those 00? I have tried but so far nothing works so the text box only shows the date (without parts of the time).

Any ideas, suggestions please will be much appreciated.

Kind regards

Mathias

 

[GriffMG]

You might try converting your field in your original select from SQL


M$ has some info here...

https://docs.microsoft.com/en-us/pr.../foxpro/convert-function-gets-date-sql-server

Essentially the syntax is as follows

SELECT CONVERT(CHAR(10), <field name>, 101) FROM <table name>

Regards

Griff
Keep [Smile]ing

There are 10 kinds of people in the world, those who understand binary and those who don't.

I'm trying to cut down on the use of shrieks (exclamation marks), I'm told they are !good for you. ​

 

[Mike Lewis]

It's not quite clear what you are seeing. You say the date displays as [tt]22/11/17 00[/tt]. Do you mean that the 17 represents the year 2017, and it is followed by a spurious space and two zeroes?

If so, it is possible that [tt]22/11/17 00[/tt] is not a date but a datetime, with the right-most characters truncated because the text box is not wide enough. If so, you can use VFP's TTOD() function to get rid of the time element. You would do that in VFP, after you have downloaded the cursor from SQL Server and before you display the form.

If I have misunderstood what you are seeing, perhaps you could clarify it.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Chris Miller]

Using cursoradapter adds the possibility to define a cursorschema, which then can have a date field with the SQL Server datetime field mapped to it. This removes both the need to use CAST or CONVERT in the SQL Server query or TTOD in VFP.

It might at first be hard to convert to cursoradapter but this isn't the only advantage. If you refrain from that for the moment, you can do something else:

If the cursor you query isn't bound to a database server table or you query from a DBF with NOFILTER or READWRITE, you can use ALTER TABLE on the cursor. ALTER TABLE of a cursor can fail when it has long field names in any field, also uninvolved from the alteration. And long field names are generally the case when you query a database server.

Then you can use AFIELDS, modify the T to a D field in the right array element and CREATE another cursor from that array, then fill in the data there with append or insert from select-sql. ALTER TABLE does the same steps, so this also doesn't cost more time than ALTER TABLE.

Chriss

 

[MathiasB]

Thanks everyone

Griff - I did that at some point and yes it works but you just shift the workload to the UI / data biding. Unfortunately it's an existing application and 1000's controls bound to dates. I closed that door.

Mike - you're right the two digits 00 are part of the time and yes just truncated. As stated I would like a solution that solves the display issue without breaking every form that's got a date control bound to one of these.

Chriss - I like the sound of that. Thank you I will look into that.

K R

Mathias

 

[Mike Lewis]

Mathias, I take your point about not wanting to alter thousands of texboxes, and therefore to do the ocnversion on the server. This would be a simple matter of changing your query (the one that retrieves the data from the server):

SELECT [highlight #FCE94F]CONVERT(DATE, MyDateTimeField)[/highlight], .... etc, FROM ....

which is more or less what Griff suggested.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[GriffMG]

Ought to be a find and replace - and perhaps it would be more supportable across SQL versions (maybe talking rubbish)

Regards

Griff
Keep [Smile]ing

There are 10 kinds of people in the world, those who understand binary and those who don't.

I'm trying to cut down on the use of shrieks (exclamation marks), I'm told they are !good for you. ​

 

[MathiasB]

I did Mike,

But the ODBC (I am using version 13) converts that column to Char, and yes the data is correct but I 'lose' the dates.

I will look into the cursorAdaptor suggestion. Sounds very promising.

Cheers