[SOLVED]Conversion failed when converting date and/or time from character string

[kc9400]

I'm trying to display the latest signed months of data, I've selected the MAX months and then I need to display all the months that are

<= the selected month in the prompt.

When running the report I am getting a Conversion failed as shown in the subject, the prompt loads fine but when actually getting to the data this happens.

I've tried a couple of converts and casts but to no joy. Also this is in a nested query, I don't know if I'm just trying to do something that isn't possible or if I'm just lacking knowledge or both  ???

'AND tblsomething.Month <= (#prompt('DatePicked')#)'  was the initial entry

This is one I tried but no joy 'AND CAST (tblsomething.Month AS DATETIME) <= (#prompt('DatePicked')#)' 

Any help, tips or advice would be appreciated.

[Lynn]

What is the contents of your tblsomething.Month column? Is it a string containing the month name or an integer containing the month number? If it contains something like "January" or "01" then there is no way to convert that to a date.

[kc9400]

The Month is a DATETIME column.

[Lynn]

Then you don't need to cast it.

[kc9400]

Yeah that's what I thought, but with or without the cast I'm running into the conversion failed which I can't understand why.

I've now put in a Date Prompt object which is doing what I want it to do but it'd be nice to know why my initial method of doing it wouldn't work.

Apologies if it's too vague.

[Lynn]

My guess would be that the problem originated with the prompt side of things, so using the right prompt control is a good way to go.

[kc9400]

Will have to play around with it, recreate the problem and see if I can make it work another way  :)


Thanks for your help and advice Lynn

[Lynn]

What type of prompt control were you using originally? If it was a text box prompt then I would hazard a guess that the way the date was entered might affect the success of the cast.

A couple things to consider:

• You can specify the datatype as part of the prompt macro
• Understanding your database's default date format might be relevant. For example, the flavor of DB2 I am working with wants a string formatted as YYYY-MM-DD to cast to date. In previous years working with clients using Oracle the default string format for date conversion was YYYY-MON-DD (or something vaguely like if memory serves)
• Using the date control is a good way to make it foolproof for your users so they can specify the desired date without running into conversion issues

Good luck!