CASTing or date formatting in ODBC->Access 97

[linker3000]

Hello,

I am back again! Long time no see!

Once again I am using the Sun JDBC-ODBC module, connecting to an Access 97 database within iReport (Jasper Reports).

I am trying to do some date calculations but seem to be ending up with text fields and variants on CONVERT and CAST seem to not be implemented. Ideally, I'd like a date returned from:

IIF (tbl_PET_Pet.PET_CV2 >= now()+180, tbl_PET_Pet.PET_CV2 - 180, '' ) as vacdate2


In other words, if the vaccination date for a pet is >= 180 days in the future, return vacdate2 as that date - 180 days (as an approximation for a 6 monthly reminder).

I can;t seem to make this return a date value.

Thanks

 

[SkipVought]

Hi,

What value IS it returning, if not a date?




Skip,
[sub]
Just traded in my old subtlety...
for a brand NUANCE![/sub]

 

[linker3000]

Hi Skip - I'm getting floating point numbers eg 184.xxxxx

 

[SkipVought]

Well Date/Time values are numbers.

You might want to...

Select *
From tbl_PET_Pet
Where PET_CV2 Between 
DateSerial(Year(Date()+180),Month(Date()+180),1) and 
DateSerial(Year(Date()+180),Month(Date()+180)+1,0)

as your subset of reminders for the month.



Skip,
[sub]
Just traded in my old subtlety...
for a brand NUANCE![/sub]

 

[linker3000]

Thanks - what I was hoping to do was find clients with vaccinations due in 6 months time (eg: in June find clients with December dates) and then work out their actual reminder date as 6 months back from December eg: if next date is 12th Dec, return 12th June as their 6 monthly reminder date. I suppose I could cheat by just 'knowing' that Dec translates back to June etc without doing a specific calculation.

 

[SkipVought]

Is there a question in there?

Skip,
[sub]
Just traded in my old subtlety...
for a brand NUANCE![/sub]

 

[linker3000]

Sure - the SELECT is working fine, but I was hoping to be able to generate a date (as per my original posting) so that the customer's message would end up as:

Your reminder date is 'vacdate2'

but since PET_CV2 - 180 ends up as a floating number, the customer would get:

Your reminder date is 184.7765

I will have a play around with the date functions but some seem not to be available within the query engine.

Thanks for the feedback so far.

 

[SkipVought]

Select Format(PET_CV2 - 180,"yyyy/mm/dd") As VacDate2

Skip,
[sub]
Just traded in my old subtlety...
for a brand NUANCE![/sub]

 

[linker3000]

Thanks Skip - I'll try that.

I have also found that bringing in your suggestion of dateserial() has made the ireport realise the results need to be in date format too so I am sorted.