Question

Retrieve data by Date only

1

How do I retrieve data from the DB by constraining a date value based only on the date part of a datetime variable. In other words, ignoring the time part. My first attempt is to use the xxx-from-dateTime functions using the following code: [ (day-from-dateTime(HolidayDate) = day-from-dateTime($DateIn)) and (month-from-dateTime(HolidayDate) = month-from-dateTime($DateIn)), and(year-from-dateTime(HolidayDate) = year-from-dateTime($DateIn)), ] delivers an exception: "Arithmetic overflow error converting expression to data type datetime."

asked 2011-03-09

Herman Geldenhuys

3 answers

Sebastiaan van den Broek · Answer 1 · 2011-03-09

I don't see any immediate problems with that code, other than the extra comma's that should give you an error anyway, so this could be a bug. I suggest filing a ticket.

If you don't care about the time parts of those attributes at all, you could save and retrieve those dates using trim-to functions

Herman Geldenhuys · Answer 2 · 2011-03-09

Yup, sorry, the commas weren't suppose to be in there. I'll use the trimToDay function to create a variable and a second one with trimToDays and addDays function and use:

$DateStart <= DateTimeAttr < $NextDate

Sebastiaan van den Broek · Answer 3 · 2011-03-09

You could use the trim-to functions found here