Still Access SQL vs SQL 2005

[kelbantaemi]

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

[Genx]

... 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)

)

[kelbantaemi]

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.

[Genx]

... ODBC is ODBC

[Genx]

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")

[kelbantaemi]

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, 2008 by Guest

[Genx]

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)

)

 

[kelbantaemi]

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.

[kelbantaemi]

YES!...YES... YES!

Works like a champ.

Thank you so much.