Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

CASTing or date formatting in ODBC->Access 97

Status
Not open for further replies.

linker3000

IS-IT--Management
May 11, 2007
28
GB
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

 



Hi,

What value IS it returning, if not a date?




Skip,
[sub]
[glasses]Just traded in my old subtlety...
for a brand NUANCE![tongue][/sub]
 
Hi Skip - I'm getting floating point numbers eg 184.xxxxx

 



Well Date/Time values are numbers.

You might want to...
Code:
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]
[glasses]Just traded in my old subtlety...
for a brand NUANCE![tongue][/sub]
 
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.
 




Is there a question in there?

Skip,
[sub]
[glasses]Just traded in my old subtlety...
for a brand NUANCE![tongue][/sub]
 
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.
 




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


Skip,
[sub]
[glasses]Just traded in my old subtlety...
for a brand NUANCE![tongue][/sub]
 
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.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top