On Tue, 2003-11-25 at 13:15, Diana Cristina Neves Soares wrote:
> I think it is not possible to have things as you stated, but you could have an
> auto_increment field and a date field in your table.
> Every time you insert a record, you set the date field and the other field will
> auto_increment himself.
>
> Then, when you want to do a select, you may do as:
>
> SELECT CONCAT(id_field,'-',date_field) AS ID, ....
> .....
>
To expand a little on this, here is something that I would use. It
contains the concat above as well as a date_format routine to put it in
mmddyy format.
CREATE TABLE foo (
iFooID INT UNSIGNED NOT NULL AUTO_INCREMENT,
tFooDate DATETIME NOT NULL,
cSomeText VARCHAR(40),
PRIMARY KEY (iFooID)
);
INSERT INTO foo (tFooDate, cSomeText) VALUES (now(),'Hello');
INSERT INTO foo (tFooDate, cSomeText) VALUES (now(),'World');
SELECT CONCAT(iFooID, "-", DATE_FORMAT(tFooDate,"%m%d%y")) AS cFooID,
cSomeText FROM foo;
Results:
+----------+-----------+
| cFooID | cSomeText |
+----------+-----------+
| 1-112503 | Hello |
| 2-112503 | World |
+----------+-----------+
2 rows in set (0.00 sec)
References:
CREATE TABLE Syntax
http://www.mysql.com/doc/en/CREATE_TABLE.html
Date and Time Functions
http://www.mysql.com/doc/en/Date_and_time_functions.html
String Functions
http://www.mysql.com/doc/en/String_functions.html
Added Notes:
Since the primary key (iFooID) has to be an integer (AUTO_INCREMENT
restrictions), it can't hold the date. However, you can create the date
field which you can increment on your own.
In the CREATE_TABLE.html file, it talks about setting DEFAULT values for
columns, which is nice, however you must use a constant value:
"Default values must be constants. This means, for
example, that you cannot set the default for a date
column to be the value of a function such as NOW() or
CURRENT_DATE."
Which is why you can't do a default now() kind of thing. So, this is why
above we added the tFooDate to the CREATE TABLE definitions as well as
the now() statements in the INSERT statements.
To extract this information in the desired format, we use 2 routines:
CONCAT
DATE_FORMAT
The CONCAT routine will concatenate the values: iFooID, '-' and
tFooDate.
The DATE_FORMAT will format the date field into the mmddyy (or any other
common format you like).
Hopefully this has been a little enlightening. Again, its basically the
same thing that was previously posted, just some descriptions and
references to go along with it.
Thanks,
Ryan Yagatich
>
> --
> Diana Soares
>
>
<snip>
> I was wondering if its possible to have a
> AUTO_INCREMENT ID
> number also have the format mmddyy.
>
> So when you query the table it would be the PRIMARY KEY AUTO_INCREMENT ID
> field:
>
> select * from table:
>
> name email phone ID
> Bugs [EMAIL PROTECTED] 5554321 1-112403
> Bunny [EMAIL PROTECTED] 5554331 2-112403
> name [EMAIL PROTECTED] 5554341 3-112403
> bbbb [EMAIL PROTECTED] 5554351 4-112503
>
<snip>
--
,_____________________________________________________,
\ Ryan Yagatich [EMAIL PROTECTED] \
/ Pantek Incorporated (877) LINUX-FIX /
\ http://www.pantek.com/security (440) 519-1802 \
/ Are your networks secure? Are you certain? /
\___A9062F5C3EAE81D54A28A8C1289943D9EE43015BD8BC03F1___\
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
