I am using MySql and I wonder how do I change the length date and timestamp datatype. Eg, timestamp, when I add a record, I got 20030725104025 but I just want 20030725.
Secondly is how do I format 20030725 to 2003/07/25? I am using ASP as my server-side scripting language. Lot of thanks.In MySQL the length of the timestamp you want to store is denoted through the size of the column you create. Valid examples are:TIMESTAMP(14) YYYYMMDDHHMMSS
TIMESTAMP(12) YYMMDDHHMMSS
TIMESTAMP(10) YYMMDDHHMM
TIMESTAMP(8) YYYYMMDD
TIMESTAMP(6) YYMMDD
TIMESTAMP(4) YYMM
TIMESTAMP(2) YYIt defaults to TIMESTAMP(14) which is why you have the whole TIMESTAMP string stored in your column. You can also use DATETIME and DATE data-types, these will store the data as YYYY-MM-DD HH:MM:SS and YYYY-MM-DD respectively.
To format dates you can use MySQL's DATE_FORMAT function. SELECT DATE_FORMAT(date_column, '%Y/%m/%d') FROM table_name;MySQL has some very powerful date and time functions, such as DATE_ADD(), DATE_SUB(), FROM_DAYS(), TO_DAYS(), EXTRACT_DATE(), DAYNAME(), MONTHNAME(), and many others. It's worth becoming acquainted with them as they can often save you a few lines of code here and there.Awesome reply.
Thanks.
Secondly is how do I format 20030725 to 2003/07/25? I am using ASP as my server-side scripting language. Lot of thanks.In MySQL the length of the timestamp you want to store is denoted through the size of the column you create. Valid examples are:TIMESTAMP(14) YYYYMMDDHHMMSS
TIMESTAMP(12) YYMMDDHHMMSS
TIMESTAMP(10) YYMMDDHHMM
TIMESTAMP(8) YYYYMMDD
TIMESTAMP(6) YYMMDD
TIMESTAMP(4) YYMM
TIMESTAMP(2) YYIt defaults to TIMESTAMP(14) which is why you have the whole TIMESTAMP string stored in your column. You can also use DATETIME and DATE data-types, these will store the data as YYYY-MM-DD HH:MM:SS and YYYY-MM-DD respectively.
To format dates you can use MySQL's DATE_FORMAT function. SELECT DATE_FORMAT(date_column, '%Y/%m/%d') FROM table_name;MySQL has some very powerful date and time functions, such as DATE_ADD(), DATE_SUB(), FROM_DAYS(), TO_DAYS(), EXTRACT_DATE(), DAYNAME(), MONTHNAME(), and many others. It's worth becoming acquainted with them as they can often save you a few lines of code here and there.Awesome reply.
Thanks.