SEO

#1 (**permalink**) 03-17-2008, 06:13 AM

Hello!

In a database I would like to code a type of event, for which I
sometimes have a full date for, and at other times only the year.

Is there a way to insert a year into a MySQL date column, in a normal
date format and that, in a standard way, expresses that "for this
specific event I only have the year"?

Thanks

Michael

#2 (**permalink**) 03-17-2008, 06:13 AM

Michaelp wrote:
> Hello!
>
> In a database I would like to code a type of event, for which I
> sometimes have a full date for, and at other times only the year.
>
> Is there a way to insert a year into a MySQL date column, in a normal
> date format and that, in a standard way, expresses that "for this
> specific event I only have the year"?
>
> Thanks
>
> Michael

http://dev.mysql.com/doc/refman/5.0/...functions.html

mysql> select year(cast('2008-2-2' as datetime))as myYEAR,
cast('2008-2-2' as date) as fullDATE;
+--------+------------+
| myYEAR | fullDATE |
+--------+------------+
| 2008 | 2008-02-02 |
+--------+------------+
1 row in set (0.02 sec)

#3 (**permalink**) 03-17-2008, 06:13 AM

Michael Austin wrote:
> Michaelp wrote:
>> Hello!
>>
>> In a database I would like to code a type of event, for which I
>> sometimes have a full date for, and at other times only the year.
>>
>> Is there a way to insert a year into a MySQL date column, in a normal
>> date format and that, in a standard way, expresses that "for this
>> specific event I only have the year"?
>>
>> Thanks
>>
>> Michael
>
> http://dev.mysql.com/doc/refman/5.0/...functions.html
>
> mysql> select year(cast('2008-2-2' as datetime))as myYEAR,
> cast('2008-2-2' as date) as fullDATE;
> +--------+------------+
> | myYEAR | fullDATE |
> +--------+------------+
> | 2008 | 2008-02-02 |
> +--------+------------+
> 1 row in set (0.02 sec)
>
hit send too soon...

since there is no such date as 0/0/2008 - no. You would need to pick a
default date like 1/1/20xx or 12/31/20xx.
Insert your date and use date functions to return only the "usable"
portion - or just store the YEAR in a YEAR column.

#4 (**permalink**) 03-17-2008, 06:13 AM

On Tue, 11 Mar 2008 07:08:05 -0700 (PDT), Michaelp wrote:
> Hello!
>
> In a database I would like to code a type of event, for which I
> sometimes have a full date for, and at other times only the year.
>
> Is there a way to insert a year into a MySQL date column, in a normal
> date format and that, in a standard way, expresses that "for this
> specific event I only have the year"?

If you set ALLOW_INVALID_DATE in a recent MySQL server, or use a version
prior to 5.0.2, you can store values like '2009-00-00'. This does have a
gotcha in that partial dates can't have INTERVAL calculations done
with them at all, and you get NULL back, in addition to the obvious
issue with now being able to schedule things on February 30, as far as
MySQL is concerned.

http://dev.mysql.com/doc/refman/5.0/en/datetime.html

--
The pluses in my current job include laughing in the face of Nobel
laureates who have just lost the only copy of their data. (Hey,
I'm still a BOFH).
-- Bob Dowling

#5 (**permalink**) 03-17-2008, 06:13 AM

Michaelp wrote:
> Hello!
>
> In a database I would like to code a type of event, for which I
> sometimes have a full date for, and at other times only the year.
>
> Is there a way to insert a year into a MySQL date column, in a normal
> date format and that, in a standard way, expresses that "for this
> specific event I only have the year"?
>
> Thanks
>
> Michael

This problem often arises with genealogical data, particularly with
dates from memorials where either the full date is not given or the
passing of years has made some characters illegible.

In this case, I would probably use a separate column to indicate the
precision of the date. In the genealogical context you might treat day,
month and year as separate char columns with a separate 'sort date' column.

Chad

--
Chad Hanna
Systems Developer FamilyHistoryOnline www.familyhistoryonline.net
FreeBSD Apache MySQL Perl mod_perl PHP

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

SEO

Date coding problem