Member Avatar for knitex

So i have this problem with a database. Under a section Date of Birth there are about 110000 entries. There are some that just have the year, for example 1978, I'm wondering if there is a way where it can automatically put in /00/00 after the 1978? so it would come out to be 1978/00/00. Anybody have any ideas? This is for Microsoft Access

  • 3 Contributors
  • 2 Replies
  • 300 Views
  • 15 Hours Discussion Span
  • Latest Post Latest Post by chuckc

Dani AI

Generated

Quick summary: has a large DOB column where some rows only contain a year (example: "1978") and wants those converted to a visible "1978/00/00" form. The replies from and correctly point out datatype matters and the limits of native date types. The most practical, maintainable solution is to separate storage (structured data for queries) from display (the "1978/00/00" text).

Recommended schema and migration approach: add small fields to record components and confidence — for example DOBYear (integer), DOBMonth (nullable integer), DOBDay (nullable integer) and a DOBPrecision flag/enum (values like Y, YM, YMD). Keep a RawDOB text field to preserve the original import. For rows where only a year is present, populate DOBYear and set DOBPrecision=Y; for full dates populate the date field and set DOBPrecision=YMD. Do the conversion in small batches, validate results, and keep a backup before changing data.

How to use the data safely: keep true Date/Time values only when the day/month are known so you can use built-in date math. For year-only records treat them as a year-range when filtering (i.e., the whole year) and as an approximate age when displayed in the UI. Make the UI show "1978 (year only)" or render the requested "1978/00/00" purely as a formatted string built from the component fields — do not write "00" into a real Date column, because that breaks date semantics and arithmetic.

Operational notes: add indexes on DOBYear and DOBPrecision for performance on large tables, and add checks or constraints to prevent invalid combinations. This approach preserves original data, keeps accurate queries possible, and gives you the exact/approximate semantics that and were pointing toward without forcing misleading values into a native date field.

Recommended Answers

All 2 Replies

Member Avatar for BitBlt

What's the datatype of the Date of Birth column? If it is text, you can just use some string manipulation functions (mid(), format(), etc.) to break the string apart and re-assemble it however you want.

If it's date/time it becomes a little trickier, as you have to use the datepart() functions to pick your data apart.

If it's numeric then you have to convert it to a string using Str(), then use the mid() and format() functions to break it apart and re-assemble.

Here's some sample MSAccess SQL that might get you going:

SELECT Table1.StringDate, 
Right([Table1]![StringDate],4) + "/0/0" AS StringYear,
Table1.DateTimeDate, 
CStr(Year([Table1]![DateTimeDate])) + "/0/0" AS DateTimeYear,
CStr(DatePart("yyyy",[Table1]![DateTimeDate])) + "/0/0" AS AlternateDateTimeYear, 
Table1.NumericDate, 
Mid(CStr([Table1]![NumericDate]),1,4) + "/0/0" AS NumericYear 
FROM Table1

I think you can figure out the assumptions about datatype and how the dates are stored.

Hope this helps!

Edited by BitBlt because: n/a
Member Avatar for chuckc

With datatype date/time there is really no way to store an unknown month or day. You could default to say (for example) 1978/01/01, but then there would be no way to know if the actual date was 1-Jan-1978 vs. unknown month or day. If you prefer to store the date using numeric or string datatype then you can store the date as "19780000", but you lose the ability to easily do arithmetic with the data, that is, compute or query the person's age. I suppose the choice depends on the application.
Good luck!

Edited by chuckc because: n/a
commented: Yep, good comment. +3
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.