Re: Date format question
Is it possible in mysql to create a date field that stores year and month only (-MM) without having to zero out the day or use varchar type fields Best here is to just use a DATE field, then use DATE_FORMAT when you want to pull up the customized date. It will get stored as a timestamp (integer), so you really won't notice that much of a storage difference. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Date format question
Thanks... My issue is not storage, it is confidentiality. I am not allowed to store the day of birth as it is considered identifying information (in medical records). I do not even have the day, I want to pass a date in format (-MM) to a date field if possible. On 1/15/07 11:37 AM, Chris White [EMAIL PROTECTED] wrote: Is it possible in mysql to create a date field that stores year and month only (-MM) without having to zero out the day or use varchar type fields Best here is to just use a DATE field, then use DATE_FORMAT when you want to pull up the customized date. It will get stored as a timestamp (integer), so you really won't notice that much of a storage difference. - Olaf Stein DBA Center for Quantitative and Computational Biology Columbus Children's Research Institute 700 Children's Drive phone: 1-614-355-5685 cell: 1-614-843-0432 email: [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Date format question
Olaf Stein wrote: Thanks... My issue is not storage, it is confidentiality. I am not allowed to store the day of birth as it is considered identifying information (in medical records). I do not even have the day, I want to pass a date in format (-MM) to a date field if possible. Pass the date in format (-MM-00). -- Gerald L. Clark Supplier Systems Corporation -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Date format question
Assign all dates to have a day of 01 Store in a date field, use DATE_FORMAT to just extract the MM and . As you don't have the real day information it doesn't matter what day is used, so long as it present in all months. Hope this helps Robert Gehrig Webmaster at www.gdbarri.com e-mail: [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: date format problem
Matt Tucker [EMAIL PROTECTED] wrote: I'm moving a JavaServlet app over from Tomcat on Win2K with a MS SQL 7 DB to Tomcat on Red Hat Linux with mySQL. Of course, there's about a hundred queries that use dates and of course, they're all in the format mm-dd-. is there a way to format the date column in my mysql tables to accept a date in this format or do i really have to go through every sql statement and parse the date and rebuild it to be -mm-dd? thanks so much. If you want to store values in the DATE columns you shoul convert all data to the -mm-dd format. Then you can use DATE_FORMAT() function to retrieve data in various formats: http://dev.mysql.com/doc/mysql/en/Date_and_time_functions.html -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: date format
mysql select date_sub(curdate(), interval 1 day) as wibble; ++ | wibble | ++ | 2003-06-30 | ++ 1 row in set (0.00 sec) -Original Message- From: Fabio Bernardo [mailto:[EMAIL PROTECTED] Sent: Tuesday 01 July 2003 15:32 To: Mysql (E-mail) Subject: date format today´s date: 2003-07-01 Hi there,,, I wrote down this statement select (current_date - 1 ) and the result was: (Currentdate -1) --- 20030700 when i write select (current_date ) I have: (Currentdate) --- 2003-07-01 but actually, I wanna a result like that select (current_date - 1 ) as result (Currentdate -1) --- 2003-06-30 can you help me, please? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: date format
On 1 Jul 2003 at 11:31, Fabio Bernardo wrote: select (current_date - 1 ) and the result was: (Currentdate -1) --- 20030700 Use this instead: SELECT CURRENT_DATE() - INTERVAL 1 DAY; The documentation for DATE_ADD() and other date and time functions is here: http://www.mysql.com/doc/en/Date_and_time_functions.html -- Keith C. Ivey [EMAIL PROTECTED] Tobacco Documents Online http://tobaccodocuments.org -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: [ date format ]
Em Ter 16 Jul 2002 18:19, Elby Vaz escreveu: Hi, the date format of MySql is /mm/dd (2002/07/16) right? Wrong. The date format of MySQL is -mm-dd. How I do to insert the date typing in my form dd/mm/ (16/07/2002)? It is possible? You have to format your date to insert into MySQL. Are you using PHP? If yes, you can try this line into your program: $mysql_date = substr($your_date, 6, 4) . - . substr($your_date, 3, 2) . - . substr($your_date, 0, 2) Hope this can help you. Anderson _ MSN Photos é a maneira mais fácil e prática de editar e compartilhar sua fotos: http://photos.msn.com.br - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Date format
Chuck, Friday, June 14, 2002, 3:01:58 AM, you wrote: CPP If you have mutli-date fields, can you set one date format in mysql sql CPP statement for all fields? I have 9 date fields, that like to have the same CPP format. What do you mean one date format? MySQL stores data in certain format, f.e. '-MM-DD' for DATE column. If you want to display data in the same format, use DATE_FORMAT() function for each column. CPP Also I don't want it to work fields that have -00-, can I CPP tell not to show those? SELECT * FROM table_name WHERE date_column'-00-00 00:00:00'; -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Date Format
Trevor, Wednesday, May 29, 2002, 3:29:43 AM, you wrote: TF Running MYSQL 3.23 can the date mmdd be reversed when using Access or VB TF frontend so that users can enter it as they are used to eg. ddmm. MySQL stores data in mmdd format, but you can use DATE_FORMAT() function to display data in different format: http://www.mysql.com/doc/D/a/Date_and_time_functions.html TF Also I TF tried creating a new field for date using DATE(8) BUT the sql statement came TF back with an error. I tried changing the format of the date field to 8 TF characters but the change did not work I still get mmddhhmmss. Any TF ideas? It's not a recognized format for DATE column type, look at: http://www.mysql.com/doc/D/a/Date_and_time_types.html TF regards TF Trevor -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: date format
Hi, My opinion: You can create char field type on table (i.e. char(10)) and when you wish to do some date comparison you can use the next mySQL functions:LEFT,MID,RIGHT,TRIM (is not only in VB) I.E: mydate-field in a_table:char(10); date on this field: '10.01.2001','30.01.2000',...etc. select * from a_table where RIGHT(mydate,4)'2002'; select * from a_table where MID(mydate,4,2)'01'; ...etc Good luck, Gelu G.NET SOFTWARE COMPANY - Original Message - From: razvan [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, March 05, 2002 7:46 AM Subject: date format Hi, please help me with this one! I now the date_format function, it converts the standard format sql (-mm-dd) to any format, but my problem is that i want to convert from another format (eg. dd/mm/) to the standard format, in this way i can use insert with my format. Can I use another format for date when creating a table? thanks! _ Do You Yahoo!? Get your free @yahoo.com address at http://mail.yahoo.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Date Format Query
Hi,
Thanks a lot for the speeedy response.
I have tried this. However, I am likely to receive the date in the DD-MON-YY
format from the frontend. And the MySql Manual doesnt mention any function
which will format a given string and convert it to date. Hence, the query.
Incase there is no go, then I shall just proceed to check whether I can do
the required formatting in the frontend before passing it to MySql.
Vaishali
-Original Message-
From: Almar van Pel [SMTP:[EMAIL PROTECTED]]
Sent: Thursday, December 20, 2001 4:46 PM
To: Vaishali Vaidya
Subject: RE: Date Format Query
Hi,
Try this:
Select date_format('2001-12-11', '%d %m %Y') Mysql stores dates in MM
DD format.
I suggest you take a look @ the manual:
http://www.mysql.com/doc/D/a/Date_and_time_functions.html
Regards,
Almar van Pel
-Oorspronkelijk bericht-
Van: Vaishali Vaidya [mailto:[EMAIL PROTECTED]]
Verzonden: donderdag, december 20, 2001 11.44
Aan: [EMAIL PROTECTED]
Onderwerp: Date Format Query
Urgentie: Hoog
Hi,
I am a new user to MySql, and have recently installed version
3.23.25-beta.
I have a date string of the type 'DD-MON-YY' (for Oracle Users) (Eg.
20-DEC-01')
If I try to insert this into a date field in the table, either as it is,
or
via the DATE_FORMAT function, it inserts a NULL
Even when I try to Select DATE_FORMAT('20-DEC-01', '%d-%m-%Y') it
returns
a Null.
If I convert this as 20-12-01, it returns a 01-12-2020.
Please let me know how to interpret such date strings for inserting into
the
database, as also whether there is an equivalent of TO_DATE() from Oracle
in
MySql.
-Vaishali
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail
[EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Date Format in MySQL
On Thursday 26 July 2001 11:03, Claudemir F. Martins wrote: Hello Friends Is possible to change the MysQL Date fromat from -mm-dd to dd-mm- I suppose, by changing the source code, and reompiling, which would break several other things. Before posting, please check: http://www.mysql.com/manual.php (the manual) Look in the manual for the function called, I believe, FORMAT(). It will change the date around how you want. j- k- -- Joshua Kugler, Information Services Director Associated Students of the University of Alaska Fairbanks [EMAIL PROTECTED], 907-474-7601 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Date Format in MySQL
Date |Thu, 26 Jul 2001 15:03:59 -0400 From |Claudemir F. Martins [EMAIL PROTECTED] Hello! CFM Hello Friends CFM Is possible to change the MysQL Date fromat from -mm-dd to dd-mm- ? CFM Thanks CFM Claudemir F. Martins You can use DATE_FORMAT function for change output date format. Read this: http://www.mysql.com/doc/D/a/Date_and_time_functions.html ___ For technical support contracts, visit https://order.mysql.com/ This email is sponsored by SWSoft, http://www.asplinux.ru/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Grigory Bakunov [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB / SWSoft /_/ /_/\_, /___/\___\_\___/ ___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Date Format in MySQL
On 26-Jul-2001 Claudemir F. Martins wrote: Hello Friends Is possible to change the MysQL Date fromat from -mm-dd to dd-mm- ? Check for DATE_FORMAT() in the manual. Regards, -- Don Read [EMAIL PROTECTED] -- It's always darkest before the dawn. So if you are going to steal the neighbor's newspaper, that's the time to do it. (53kr33t w0rdz: sql table query) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Date Format in MySQL
Claudemir F. Martins schrieb am Donnerstag, 26. Juli 2001, 21:03:59: Is possible to change the MysQL Date fromat from -mm-dd to dd-mm- ? No. This is the standard format. You can, however, reformat the result sets according to your needs. This format allows easy manipulation of date data in contrast to many of those accustomed to by local cultures. -- Herzlich Werner Stuerenburg _ ISIS Verlag, Teut 3, D-32683 Barntrup-Alverdissen Tel 0(049) 5224-997 407 · Fax 0(049) 5224-997 409 http://pferdezeitung.de - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Date Format in MySQL
Dear Claudemir, No. This is the standard format. You can, however, reformat the result sets according to your needs. The function is DATE_FORMAT, and a SELECT statement will look like this: SELECT DATE_FORMAT(time_field, %d-%m-%Y) FROM tbl This will output 2001-07-27 as 27-07-2001. Have a deep look into http://www.mysql.com/doc/D/a/Date_and_time_functions.html for details. Regards, -- Stefan Hinz Geschäftsführer / CEO iConnect e-commerce solutions GmbH # www.js-webShop.com www.iConnect.de # Gustav-Meyer-Allee 25, 13355 Berlin # Tel: +49-30-46307-382 Fax: +49-30-46307-388 - Original Message - From: Werner Stuerenburg [EMAIL PROTECTED] To: Claudemir F. Martins [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Friday, July 27, 2001 9:49 AM Subject: Re: Date Format in MySQL Claudemir F. Martins schrieb am Donnerstag, 26. Juli 2001, 21:03:59: Is possible to change the MysQL Date fromat from -mm-dd to dd-mm- ? No. This is the standard format. You can, however, reformat the result sets according to your needs. This format allows easy manipulation of date data in contrast to many of those accustomed to by local cultures. -- Herzlich Werner Stuerenburg _ ISIS Verlag, Teut 3, D-32683 Barntrup-Alverdissen Tel 0(049) 5224-997 407 · Fax 0(049) 5224-997 409 http://pferdezeitung.de - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Date format problem
On Thu, 28 Jun 2001 15:30:14 -0300, Alejandro Ralla [EMAIL PROTECTED] wrote: You would need to reformat the input date first, within your middleware, like Perl or PHP. Then you would be able to enter it into your db. Hi guys, does anyone help me with this simple thing ?? I have a date format problem, I need a spanish format please read the following lines: Receive from input 28/06/2001 Want to change it to 2001-06-28 to write the Database Use to do it DATE_FORMAT (inputdate, %Y-%m-%d) It doesn't work fine Thanks a lot for your time Lic. Alejandro Ralla Districomp S.A Magallanes 1688 Montevideo 11200 Uruguay (5982)400-1115 int 119 [EMAIL PROTECTED] www.districomp.com Mike(mickalo)Blezien Thunder Rain Internet Publishing Providing Internet Solutions that work! http://www.thunder-rain.com Tel: 1(225) 686-2002 = - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Date format problem
What programming language are you using? Hi guys, does anyone help me with this simple thing ?? I have a date format problem, I need a spanish format please read the following lines: Receive from input 28/06/2001 Want to change it to 2001-06-28 to write the Database Use to do it DATE_FORMAT (inputdate, %Y-%m-%d) It doesn't work fine Thanks a lot for your time - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Date format problem
On Thu, 28 Jun 2001 21:49:57 +0200, Sebastiaan J.A. Kamp [EMAIL PROTECTED]
wrote:
if your using perl, heres a real simple way to format your date:
my $date = 28/06/2001;
my($mn,$day,$yr) = split '/',$date;
my $db_date = join('-',$yr,$mn,$day);
# Your output should now be $db_date = 2001-06-28
Hope this helps ;)
What programming language are you using?
Hi guys, does anyone help me with this simple thing ??
I have a date format problem, I need a spanish format please read the
following lines:
Receive from input 28/06/2001
Want to change it to 2001-06-28 to write the Database
Use to do it DATE_FORMAT (inputdate, %Y-%m-%d)
It doesn't work fine
Thanks a lot for your time
Mike(mickalo)Blezien
Thunder Rain Internet Publishing
Providing Internet Solutions that work!
http://www.thunder-rain.com
Tel: 1(225) 686-2002
=
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
