New issue now involving SQL 2005 data format. Im importing data from a Sql 2005 database based on a time stamp field. If I duplicate a simple query in crystal 9 the WHERE clause reads ("Material_Trans"."Material_Trans_Date">={ts '2008-10-01 00:00:00'} AND "Material_Trans"."Material_Trans_Date"<{ts '2008-10-01 00:00:01'}) Which gives me all records with a date of 10/01/08. I can not find a way to pass this date in the given Sql format to make the query work.

I have been able to make it work in an access database by formatting my Filemaker field like this #10/01/2008# and passing this to my Sql statement

I am really under the gun now all our mission critical reports will not run since converting from an access database to Sql 2005

... have you tried just passing in YYYY-MM-DD?

//ydf is "YourDateField"

e.g.

Let([

yesterday = ydf-1;

tommorow = ydf+1];

"WHERE Material_Trans.Material_Trans_Date > " & 

Year(yesterday) & "-" & Month(yesterday) & "-" & Day(yesterday) & " AND Material_Trans.Material_Trans_Date < " & 

Year(tommorow) & "-" & Month(tommorow) & "-" & Day(tommorow)

)

Yes, I tried that but kept getting a "ODBC missing operator" error. But after closer scrutiny I think it has to do with Sql is looking for 2 digits on the month(mdf) and Day(mdf). Sql wants to see {ts '2008-10-02 00:00:00'} but my expression Year(mdf)&"-"& Month(mdf)&"-"& Day(mdf)" " "&"00:00:00 is returning {ts '2008-10-2 00:00:00'}. If this is the issue how do I now control for Day and Month place holders.

Am I making this more complicated then it needs to be. Or is this always sticky when it comes to ODBC, Filemaker and comunicating to Sql.

... ODBC is ODBC

If it does actually need 2 digits which it probably will, use this CF to format the date: http://www.briandunning.com/cf/893

date.format( YDF ; "yyyy-mm-dd")

Genx,

I do not have FileMaker Dev/Adv, only have FM 7 Standard version. so do not have luxury of using custom functions I believe.

Edited October 2, 200817 yr by Guest

Let([

yesterday = ydf-1;

tommorow = ydf+1];

"WHERE Material_Trans.Material_Trans_Date > " & 

Year(yesterday) & "-" & Right("0" & Month(yesterday);2) & "-" & Right("0" & Day(yesterday);2) & " AND Material_Trans.Material_Trans_Date < " & 

Year(tommorow) & "-" & Right("0" & Month(tommorow); 2) & "-" & Right("0" & Day(tommorow); 2)

)

 

Genx,

YOU ARE THE MAN!

I think that will work, have not had the opertunity to test the completed SQL statement and run a query, but the FM Day and Month conversions work. Will fully implement this in a script shortly, and let you know.

YES!...YES... YES!

Works like a champ.

Thank you so much.