Robeen_S


Hi,

I'm using this SQL in FoxPro:

Select field1, field2, field3, substr(DTOC(datetimefield_1),1,10), substr(DTOC(datetimefield_2),1,10) from mytable.dbf where field1 = "searchvalue"

It works.

But I would like to specify that I want the datefields to be displayed in mm/dd/yy format rather than use the substr() function to truncate the datetime at the end of the yyyy. It seems like that would be more elegant than having to hack off the time segments of the datetime fields but I can't find how to do it.

In SQL Server & Access sql - I've used either, Cast or Format type functions to do this.

I'd appreciate any help.

Thanks!

Robeen




Re: FoxPro SQL help for Date Formats.

Garrett Fitzgerald


Does it work if you try:

lcOldCentury = SET("CENTURY")

lcOldDate = SET("DATE")

SET CENTURY OFF

SET DATE MDY

Select field1, field2, field3, ;

substr(DTOC(datetimefield_1),1,10), substr(DTOC(datetimefield_2),1,10)

from mytable.dbf ;

where field1 = "searchvalue"

SET CENTURY &lcOldCentury.

SET DATE &lcOldDate.






Re: FoxPro SQL help for Date Formats.

Naomi Nosonovsky

Check TTOD function in Help.




Re: FoxPro SQL help for Date Formats.

CetinBasoz

Cast is available in VFP too. But in VFP we generally use ttod() as Naomi suggested. ie:

Select field1, field2, field3, ttod(datetimefield_1) as datetimefield_1, ttod(datetimefield_2) as datetimefield_2 from mytable where field1 == "searchvalue"

For displaying in mm/dd/yyyy, dd/mm/yyyy etc use set commands.

set date to mdy

set century on

or match with regional settings:

set sysformats on





Re: FoxPro SQL help for Date Formats.

Naomi Nosonovsky

CetinBasoz wrote:

Cast is available in VFP too. But in VFP we generally use ttod() as Naomi suggested. ie:

Select field1, field2, field3, ttod(datetimefield_1) as datetimefield_1, ttod(datetimefield_2) as datetimefield_2 from mytable where field1 == "searchvalue"

Should not it be

Code Snippet

Select field1, field2, field3, ttod(datetimefield_1) as datefield_1, ttod(datetimefield_2) as datefield_2 from mytable where field1 = "searchvalue"

Just simply can not resist Smile




Re: FoxPro SQL help for Date Formats.

CetinBasoz

Naomi,

Maybe my fontsize is very small that I can't see the actual change you made. If I'm reading it right you only changed "==" to "=". If that is the case then no, it would be "==".

Poster asking the question, along saying how he did it in MSSQL. If he would expect things work like in MSSQL (which is ANSI SQL compatible) then he should use "==". We don't want him to be surprised that he gets results that he should get using 'like "searchvalue%", right

However with this opportunity I would add that unlike MSSQL VFP is case sensitive and you can't change that by "SET" like in MSSQL. So it'd look like:

lower(field1) == "searchvalue"





Re: FoxPro SQL help for Date Formats.

Naomi Nosonovsky

Good point, Cetin. I was thinking that the poster may want the partial match.

But the change I made (jokingly) was to change the field names from

as datetimefield_1 to datefield_1 since we're now getting date fields and not datetime.





Re: FoxPro SQL help for Date Formats.

Robeen_S

Hi,

This:

Select field1, field2, field3, ttod(datetimefield_1) as datetimefield_1, ttod(datetimefield_2) as datetimefield_2 from mytable where field1 == "searchvalue"

. . . did not work for me.

I got the following error message:

"Function argument value, type, or t is invalid."

I looked at the table designer & the fields are 'Date' type - not 'DateTime'. I'm assuming that that is why TTOD() errors.

My SQL with the "substr(DTOC(datetimefield_1),1,10) etc works - so I'm not worried.

I probably should have mentioned that I am using the SQL in ASP.Net (VB) to access FoxPro data through Visual Studio 2005.

So far, that is working as well with the substr & dtoc.

When I Viewed the page in the Browser before I used substr(dtoc()) - I was getting the date [eg: 06/26/2007], plus 12:00:00 for each of the date fields.

I don't want the 12:00:00 & I don't know where it's coming from.

When I query the dbfs using FosPro - I don't see the 12:00:00 - just the mm/dd/yyyy.

I really appreciate your help.





Re: FoxPro SQL help for Date Formats.

Naomi Nosonovsky

I'm not sure I understand your question - if you have date fields in VFP table, when selecting them directly (without any extra functions) should bring them as dates, right Where the conversion from date to datetime is coming



Re: FoxPro SQL help for Date Formats.

Robeen_S

Hi Naomi,

When I do a simple 'Select' in FoxPro - I only get mm/dd/yyyy. No problem.

When I run the same select as the DataSource for a gridview control on a web page [Visual Studio 2005] that is accessing the same table - I am getting the mm/dd/yyyy plus 12:00:00 in the gridview control on the web page.

I do not know why ASP.Net is returning the hh:mmTongue Tieds as well as the mm/dd/yyyy.

I hope that answers your question.

Thanks.





Re: FoxPro SQL help for Date Formats.

CetinBasoz

Oh if it was already a date then no conversion needed.

select field1,.... datetimefield_1 ...

However, in .Net the corresponding type is a datetime, there is no date only. 12:00:00AM comes from casting to a datetime.

In your dataformatstring you could control it with say {0Big Smile}. Or you could explicitly use ToString() with any custom format. I wouldn't play with the data type itself.





Re: FoxPro SQL help for Date Formats.

CetinBasoz

I don't know how I'd remove this smiley's (why they even do exist on technical sites I don't know).

I meant { 0 : d } - leftbrace, zero, colon, d, rightbrace. You can set that in grid boundcolumn properties.