Hi Rafael,
you can avoid all of the problems by using a parameter rather than
passing the date as a string:
PRIVATE MyDate
MyDate = DATE() && or DATETIME()
TEXT TO m.lcSQL TEXTMERGE NOSHOW
SELECT invdate
FROM Invoices
WHERE invdate = ?m.MyDate
ENDTEXT
SQLEXEC(thisform.nHandle,m.lcSQL,'curInvoices')
Frank.
Frank Cazabon
On 15/12/2011 05:11 PM, Rafael Copquin wrote:
> My app is client-server (VFP9SP2/SQL SERVER 2008 EXPRESS)
>
> A typical select statement where dates are involved is:
>
> local cCmd
>
> cCmd = [select invdate from invoices where invdate=]+[']+dtos(date())+[']
> sqlexec(thisform.nHandle,cCmd,'curInvoices')
>
>
> Now, one of my clients has a SQL Server 2005 Express installed with an
> application developed by someone else. I am not allowed to install a
> 2008 version, so what I did is install my database in that server.
>
> That is a PITA, since all date fields are datetime, however I managed to
> select the invoices with :
>
> local cCmd,cDate
>
> cDate = dtot(date())
>
> cCmd= [ select convert(char(10),invdate,103) as invdate from invoices
> where invdate = ']+cDate+[']
>
> sqlexec(thisform.nHandle,cCmd,'curInvoices')
>
> My problem is the following:
>
> In this country dates are expressed the British way, namely 'DD-MM-YYYY'
>
> So I SET DATE BRITISH (in VFP) and all is well, since all the SQL Server
> 2008 installed at my clients's servers are in Spanish and their Windows
> servers have the regional configuration in Spanish, with the dates as
> 'DD-MM-YYYY'
>
> But this particular SQL Server 2005 is installed in a Windows Server
> 2003 with the Default language as English and the regional configuration
> set to the US, therefore the dates are american ( 'MM-DD-YYYY')
>
> If I SET DATE AMERICAN (in VFP) before sending the query all is well,
> however if I do not, nothing comes back from the server.
> I need to create a function that would detect the regional configuration
> of the server, so as to determine if the dates are american or british,
> otherwise all the sql sentences dealing with dates in my app will fail
> for this particular client.
>
> Please advise
>
> Rafael Copquin
>
>
>
>
>
[excessive quoting removed by server]
_______________________________________________
Post Messages to: [email protected]
Subscription Maintenance: http://leafe.com/mailman/listinfo/profox
OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message: http://leafe.com/archives/byMID/profox/[email protected]
** All postings, unless explicitly stated otherwise, are the opinions of the
author, and do not constitute legal or medical advice. This statement is added
to the messages for those lawyers who are too stupid to see the obvious.
