Working With User-Defined Field Dates
Back to Query Directory
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
rv_patients
WHERE
"Back/OK" BETWEEN '2007-05-01' AND '2007-05-31'
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
rv_patients
WHERE
"Back/OK" BETWEEN '2007-05-01' AND '2007-05-31'
CategoryQueries
