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))

wp_auambiguous_dates_01

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.

Advertisements