Rob-

The database is MS Sql 2005 and says BidDate(datetime,Null).  When it comes into the reporting tool it looks to still be a date field.

"Kenneth Sewell" <kennethsewell@gmail.com> wrote in message
news:18C2720B-2F83-471F-A6DF-1972D26C4568@news.elevatesoft.com...
> Rob-
>
> The database is MS Sql 2005 and says BidDate(datetime,Null).  When it
> comes into the reporting tool it looks to still be a date field.
>

Hmmm, and why are we discussing this in a DBISAM group?

Robert

Kenneth

>The database is MS Sql 2005 and says BidDate(datetime,Null). When it comes into the reporting tool it looks to still be a date field.

Which, therefore, means that its not a DATE. Check out this http://www.sqlhacks.com/index.php/Dates/DateSelection

I don't know just what BidDate(datetime,Null) converts to in DBISAM speak but, as Robert says, it isn't a date. The error message indicates its coming across as a string field of some sort so you'll need to make sure the format is correct for DBISAM (I think it went to YYYY-MM-DD but I've been using ElevateDB for a while and can't remember) and CAST it to a DATE.



Roy Lambert [Team Elevate]

Thanks Roy,

The reporting tool I am using pulls its data from sql but its internal sql engine is DBISAM.  So I am limited to those sql commands and what it
presents me.  Is their a command to cast that string to a date field myself in the select statement?  If so, please let me know

Thanks

"Kenneth Sewell" <kennethsewell@gmail.com> wrote in message
news:6151CD9B-923C-41C7-AF9A-AA4C3CDA3AB2@news.elevatesoft.com...
> Thanks Roy,
>
> The reporting tool I am using pulls its data from sql but its internal sql
> engine is DBISAM.  So I am limited to those sql commands and what it
> presents me.  Is their a command to cast that string to a date field
> myself in the select statement?  If so, please let me know
>

Let's say the string comes in as "DD/MM/YYYY" in a field named Field1 then
it is

select cast(substring (field1 from 7 for 4) + '-' + substring (field1 from 4
for 2)
+ '-' + substring(field1 from 1 for 2) as date) mydate from mytable

If they do zero suppression (Jan 1 is 1/1/2008 and Dec 1 is 1/12/2008), it
gets a bit more complicated, you will have to look for the position of the
separators. In either case, you want for the intermediate string before the
cast to be in the format YYYY-MM-DD.

Robert

Getting close-  This allows me to save the query and doesn't give me any error messages, but the results shows the column MYDATE but all the
results are blank.

Any more ideas?

Kenneth


What would be helpful at this stage is a copy of the data Can you do a SELECT * INTO tbl FROM reportingtool and post the result into the binaries?

That way we can actually see what you're trying to deal with.

Roy Lambert [Team Elevate]