Tired of finding the right style convertion for your datetime-input? Try using the ambiguous date- and time format.
Dates in the format yyyy/mm/dd or yyyy-mm-dd are always recognized unambiguously as dates, regardless of the DATE_ORDER setting. In other words, this is a platform independent solution.
Even other characters can be used as separators instead of ” /” or ” – “; for example, ” ? “, a space character, or ” , “. Also, a string of the form hh : mi : ss . sss is interpreted unambiguously as a time.
For combinations of dates and times, any unambiguous date and any unambiguous time yield an unambiguous date-time value.
Also, the form yyyy/mm/dd hh:mi:ss.sss is an unambiguous date-time value.
Having this information, ask your end-user for a date time-value with a character prompt in a predefined filter, and use the convert-function to convert the date/time-character value to a valid timestamp, without the style-conversion-parameter!
@Select(On The Fly Timestamp Table\Date (as Timestamp-type))=convert(datetime,@Prompt(‘Enter user defined date/time (format:yyyy/mm/dd hh:mi:ss.sss)’,‘A’,,MONO,FREE,PERSISTENT))
It still works even if you omit a part or the complete time-value ‘hh:mm:ss.sss’ ! So get rid of the BO date-picker, and use the above platform-independant solution.
And now the weird part. Do you need the convert-function? No, you don’t need the conversion-function! In other words
@Select(On The Fly Timestamp Table\Date (as Timestamp-type))=@Prompt(‘Enter user defined date/time (format:yyyy/mm/dd hh:mi:ss.sss)’,‘A’,,MONO,FREE,PERSISTENT)
is also correct!
Why? Comparing =’yyyy/mm/dd hh:mi:ss.ssss’ is comparing a date-type with a string-literal. And this is impossible, so, the SQL compiler does an implicite type-casting from the literal ‘yyyy/mm/dd hh:mi:ss.sss’ to a timestamp. In fact the convert-function has been used behind the scenes.
Do you need more datetime-solutions? Just wait a few days, at the end of the month, I’ll publish the ‘Ultimate Generic Date/Time framework’ for the Adaptive Server Enterprise.