Re: MySQL - Perl Date formating ! - Rajeev Rumale - com.mysql.lists.perl

5 messages in com.mysql.lists.perlRe: MySQL - Perl Date formating !

From	Sent On	Attachments
Jochen Wiedmann	16 Aug 2001 00:05
Rajeev Rumale	16 Aug 2001 01:43
Robert Goff	16 Aug 2001 08:41
Sean Cannon	16 Aug 2001 10:46
Rajeev Rumale	17 Aug 2001 02:33

Subject:	Re: MySQL - Perl Date formating !
From:	Rajeev Rumale (raj...@myangel.com)
Date:	08/17/2001 02:33:20 AM
List:	com.mysql.lists.perl

I got it now Thanks your every body for help. The example were very helpful. Thanks for such a nice explaination.

with regards

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ When you go to bed, don't be "somebody" and sleep. Be a cloud of light, a glow of a candle, full of life, alive. ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

-----Original Message----- From: Robert Goff [mailto:go...@aisling.cc] Sent: Thursday, August 16, 2001 11:42 AM To: Rajeev Rumale; Jochen Wiedmann; Rich Glosson Cc: msql...@lists.mysql.com Subject: RE: MySQL - Perl Date formating !

-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1

I have am new to My SQL and Perl.. Can any one quide me in using the Date type in MySql using Perl. I need to store date in the format of dd/mm/yyyy, I need to know if i can change the format in which mysql stores the dates.

You can't change the date format that's actually stored, but you can control the format as it goes in and out. The easiest way is to use the unix_timestamp() and from_unixtime() functions to convert between MySQL format and Unix timestamp format.

No, the easiest way is to use the function provided specifically for this purpose: (and I quote)

DATE_FORMAT(date,format) Formats the date value according to the format string. The following specifiers may be used in the format string: %M Month name (January..December) %W Weekday name (Sunday..Saturday) %D Day of the month with English suffix (1st, 2nd, 3rd, etc.) %Y Year, numeric, 4 digits %y Year, numeric, 2 digits %X Year for the week where Sunday is the first day of the week, numeric, 4 digits, used with '%V' %x Year for the week, where Monday is the first day of the week, numeric, 4 digits, used with '%v' %a Abbreviated weekday name (Sun..Sat) %d Day of the month, numeric (00..31) %e Day of the month, numeric (0..31) %m Month, numeric (01..12) %c Month, numeric (1..12) %b Abbreviated month name (Jan..Dec) %j Day of year (001..366) %H Hour (00..23) %k Hour (0..23) %h Hour (01..12) %I Hour (01..12) %l Hour (1..12) %i Minutes, numeric (00..59) %r Time, 12-hour (hh:mm:ss [AP]M) %T Time, 24-hour (hh:mm:ss) %S Seconds (00..59) %s Seconds (00..59) %p AM or PM %w Day of the week (0=Sunday..6=Saturday) %U Week (0..53), where Sunday is the first day of the week %u Week (0..53), where Monday is the first day of the week %V Week (1..53), where Sunday is the first day of the week. Used with '%X' %v Week (1..53), where Monday is the first day of the week. Used with '%x' %% A literal `%'. All other characters are just copied to the result without interpretation: mysql> select DATE_FORMAT('1997-10-04 22:23:00', '%W %M %Y'); -> 'Saturday October 1997' mysql> select DATE_FORMAT('1997-10-04 22:23:00', '%H:%i:%s'); -> '22:23:00' mysql> select DATE_FORMAT('1997-10-04 22:23:00', '%D %y %a %d %m %b %j'); -> '4th 97 Sat 04 10 Oct 277' mysql> select DATE_FORMAT('1997-10-04 22:23:00', '%H %k %I %r %T %S %w'); -> '22 22 10 10:23:00 PM 22:23:00 00 6' mysql> select DATE_FORMAT('1999-01-01', '%X %V'); -> '1998 52'

As of MySQL Version 3.23, the `%' character is required before format specifier characters. In earlier versions of MySQL, `%' was optional. TIME_FORMAT(time,format) This is used like the DATE_FORMAT() function above, but the format string may contain only those format specifiers that handle hours, minutes, and seconds. Other specifiers produce a NULL value or 0.