FoxPro 9 SP2 and SQL Server dates 1

[seckner]

I'm really confused over dates between FoxPro and SQL Server. I'm trying to use FoxPro to pull back records from our SLQ Server 2008 - I need all records between 1/03/2011 and 1/07/2011 from the server to a FoxPro cursor. This part works well but it's very manual using

"between convert(datetime,'2011-01-03') and convert(datetime,'2011-01-07')"

SO now I want to automate pulling in the Monday and Friday dates. Using some code I found here gives me Monday and Friday perfectly:

someDate = DATE()
lastMonday = m.someDate - DOW(m.someDate,1) - 5
lastFriday = m.someDate - DOW(m.someDate,1) - 1

but the dates won't work in the query as FoxPro returns them OR I just don't know how to make it work - the cursor is empty every time. How can I format the lastMonday and lastFriday dates so they will work in the query of the SQL Server?

 

[IlyaRabyy]

Since you already have the dates you need, in the xBase date format (differs from MS SQL Server's), you may try

someDate = DATE()
cLastMonday = DTOC(m.someDate - DOW(m.someDate,1) - 5
cLastFriday = DTOC(m.someDate - DOW(m.someDate,1) - 1)
** and then use it the same way as you have shown here:
between convert(datetime,cLastMonday) and convert(datetime,cLastFriday)

HTH.

Regards,

Ilya

 

[seckner]

Thanks for your quick reply! I really do appreciate it.

I made the changes you suggested and ran it - it still brings back an empty cursor - if I type the values in there are 3350 records returned. I noticed something though - running it through the debugger it shows your cLastMonday value to be "01-03-2011" and the same format for Friday - notice the double quotes - if I type in double quotes the query will also fail **BUT** if I type them in using a single quote ' it works every time.

I guess that's why I'm so confused - what's the difference between single and double quotes? Is there a way to get the single quotes?

 

[Mike Lewis]

Seckner,

I think you're confused by the differences between FoxPro and Transact-SQL syntax.

In SQL Server, to get dates that are a particular day of the week, you use DATEPART().

For example, the following query will get all records where Start_Date is a Monday:

SELECT * FROM MyTable WHERE DATEPART(dw, Start_Date) = 2

That's the query that you need to send to the server. You can't execute it on the result set coming back.

By the way, the value of "2" in the above example assumes that the first day of the week is a Sunday. That's the default, but it might have been changed by SET DATEFIRST.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy

 

[JRB-Bldr]

dSomeDate   = DATE()
dLastMonday = dSomeDate - DOW(dSomeDate,1) - 5
dLastFriday = dSomeDate - DOW(dSomeDate,1) - 1

That code will give you Date values.

In order to use them to get date sensitive data from a SQL Server table they have to be converted into a String which is formatted in a SQL 'friendly' manner.

One way of the many ways to use them in your SQLEXEC() to get data from your SQL Server data tables might be...

sLastMonday = DTOS(dLastMonday)
* --- Create SQL 'friendly' String ---
cSQLMonday = LEFT(sLastMonday,4) + '-' + SUBSTR(sLastMonday,5,2) + '-' + RIGHT(sLastMonday,2)

sLastFriday = DTOS(dLastFriday)
* --- Create SQL 'friendly' String ---
cSQLFriday = LEFT(sLastFriday,4) + '-' + SUBSTR(sLastFriday,5,2) + '-' + RIGHT(sLastFriday,2)

* --- So now one way you can use them is as follows ---
cSQLCommand = "SELECT * FROM SQLTable WHERE DateTimeFld BETWEEN '" + cSQLMonday " 00:01:00' AND '" + cSQLFriday + " 23:59:00'"
nRet = SQLEXEC(nConnectionHandle, cSQLCommand, 'Results')
IF nRet = 1
   * --- Use your resultant VFP Cursor ---
   SELECT Results
ENDIF

Somewhat cumbersome compared to how easy it is to get from VFP tables, but it will work.

But remember that SQLEXEC() commands need to use SQL Server syntax, not VFP syntax.

Then if you only want Date values, since the Date related fields in the SQL Server table were DateTime, you will need to convert them in your VFP cursor.

Good Luck,
JRB-Bldr

 

[IlyaRabyy]

When this DATEPART() was introduced in Sequel, in what version? (Can't recall seing it in Sequel 2005...)

Regards,

Ilya

 

[Mike Lewis]

Ilya,

Not sure what you mean by "Sequel 2005".

DATEPART() is not part of the SQL language. It is specific to Transact-SQL, which is the extension of SQL used by Microsoft SQL Server (which is what Seckner was asking about).

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy

 

[Olaf Doschke]

Actually jrbbldrs answer is the most comprehensive one.

You can also pass in foxpro variables to SQL in SQLEXEC() as simple as this:

dSomeDate = DATE()
dLastMonday = dSomeDate - DOW(dSomeDate,1) - 5
dLastFriday = dSomeDate - DOW(dSomeDate,1) - 1

SQLExec(nConnectionHandle,"Select * from dbo.Tablename Where Fieldname between ?dLastMonday and ?dLastFriday","curResult")

And you need to take care, if you store datetime values in sql server the friday date is actually friday 0:00 am, so all datetimes during friday having a time portion bigger than 0:00 am are past that datetime and won't appear in the result, so you better query between monday 0:00 am and saturday 0:00 am to get all data of friday too.

The only way it's okay, if you do store in date fields (which are actually new in sql2008) or store in datetime fields letting the time portion being 0:00 am always, then friday data is included.

jrbbldr has thought of this by adding 23:59:00 as the time portion to friday. Just slips on data of the last 10 seconds, even 23:59:59 would fail on a second, though that may not make any difference as noone works in this time on the data. In conjunction with online data you got to be more precise, as then there is every clock time across the whole world at the same time and turned to the local time you can expect data around midnight too, of course.

Bye, Olaf.

 

[IlyaRabyy]

That's what I meant, Mike: MS SQL Server 2005 (which is nick-named, at least in our area - Los Angeles/Orange County, - "[MS] Sequel XXXX"). And - yes, I am well aware that DATEPART() ain't not a part of ANSI SQL DDL/DML! ;-)
With this in mind, let me re-iterate my question, kind Sir:

In what version of its SQL Server Microsoft introduced the beforesaid DATEPART() function? I found that it was, indeed, already present in the 2005 release (although I never knew it), but that article on the MSDN (

http://msdn.microsoft.com/en-us/library/ms174420(SQL.90).aspx)

does not go back beyond 2005.

Regards,

Ilya

 

[Mike Lewis]

Ilya,

In what version of its SQL Server Microsoft introduced the beforesaid DATEPART() function?

Don't know for sure. I can only say that it was definitely in 2000, and I'm pretty sure it was in 7.0, and I'm fairly sure it was in 6.5.

Why do you ask?

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy

 

[IlyaRabyy]

Couse I never saw it there, Mike.
Thank you, colleague!


Regards,

Ilya