Working With User-Defined Field Dates

Dates in UD fields that are configured to use date entry pictures (such as “@D2”) are stored as numbers that represent the number of days since 12/28/1800. So if you query the value in such a field, you will output something like “75899” rather than the date you expect. In addition, if your WHERE clause makes comparisons that depend on dates in those fields, you won’t get anything in your result set. To convert those values in your query to dates that you can work with, you must add the value stored in the UD field to the 12/28/1800 base date. Here is an example:

SELECT
  lastname,
  firstname,
  dob,
  id,
  DATEADD(day,fld1,'1800-12-28') AS "Closed chart",
  DATEADD(day,fld2,'1800-12-28') AS "RT to Therapist",
  DATEADD(day,fld3,'1800-12-28') AS "Back/OK",
  DATEADD(day,fld5,'1800-12-28') AS "To scan",
  DATEADD(day,fld6,'1800-12-28') AS "Last scan/reopen"
FROM
  sos.rv_patients
WHERE
  "Back/OK" BETWEEN '2007-05-01' AND '2007-05-31'

Leave a Reply

Your email address will not be published.

You may use these HTML tags and attributes:

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

This site uses Akismet to reduce spam. Learn how your comment data is processed.