Right Date format mask
I'm pulling in a date with the following format 9/14/2009 2:12:48 PM And using this mask to convert it using the str_to_date() function: %e %m %Y %r but it keeps giving me an error. Do I have the right mask? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Right Date format mask
In the last episode (Sep 14), John Meyer said: I'm pulling in a date with the following format 9/14/2009 2:12:48 PM And using this mask to convert it using the str_to_date() function: %e %m %Y %r but it keeps giving me an error. Do I have the right mask? Nope. Assuming your input string is in mm/dd/yyy format, you would want a format string of %m/%e/%Y %r. -- Dan Nelson dnel...@allantgroup.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Right Date format mask
Dan Nelson wrote: In the last episode (Sep 14), John Meyer said: I'm pulling in a date with the following format 9/14/2009 2:12:48 PM And using this mask to convert it using the str_to_date() function: %e %m %Y %r but it keeps giving me an error. Do I have the right mask? Nope. Assuming your input string is in mm/dd/yyy format, you would want a format string of %m/%e/%Y %r. To quote the great sage and eminent drunkie Homer Simpson Doh! Thanks. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Date format question
Hi All 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 Thanks Olaf -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
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]
setting date format
Hi Friends,
I want to set the default date format as dd-mon-. How do i do this in
mysql.
I tried this, set global date_format='%d-%m-%y';. The command executed and
when i did show variable it show the new format.
But when i tried the below test, i got wrong results. Can u please help.
mysql create table mytest(doj date);
Query OK, 0 rows affected (0.05 sec)
mysql insert into mytest values('21-01-2005');
Query OK, 1 row affected, 1 warning (0.00 sec)
mysql select * from mytest;
++
| doj |
++
| -00-00 |
++
please help.
regards
anandkl
Re: setting date format
Ananda Kumar wrote: Hi Friends, I want to set the default date format as dd-mon-. How do i do this in mysql. The short answer: not. http://dev.mysql.com/doc/refman/5.0/en/dynamic-system-variables.html lists the variables you can change by the SET GLOBAL or SET SESSION command. 'date_format' is not listed there. Also http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html states: # date_format This variable is not implemented. # datetime_format This variable is not implemented. It's probably listed there for future use. Kind regards, Jigal. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
INSERT DATE using USA Date Format
I have some data where the date is already in USA format (MM-DD-).
I would like to be able to insert this data without having to massage
the date in the code.
Is there a way to specify the Date Format for an INSERT statement?
Something like:
INSERT INTO tblMyTable(myDate) VALUES(DATE_FORMAT('USA', '12-31-2004');
Thanks,
MIke
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: INSERT DATE using USA Date Format
In the last episode (May 04), Michael J. Pawlowsky said:
I have some data where the date is already in USA format
(MM-DD-). I would like to be able to insert this data without
having to massage the date in the code.
MySQL seems to think that USA format is MM.DD., which I've never
seen before :)
Is there a way to specify the Date Format for an INSERT statement?
Something like:
INSERT INTO tblMyTable(myDate) VALUES(DATE_FORMAT('USA', '12-31-2004');
Try the STR_TO_DATE function:
SELECT STR_TO_DATE('12-31-2004','%m-%d-%Y');
2004-12-31
--
Dan Nelson
[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: INSERT DATE using USA Date Format SOLUTION
Dan Nelson wrote:
Is there a way to specify the Date Format for an INSERT statement?
Something like:
INSERT INTO tblMyTable(myDate) VALUES(DATE_FORMAT('USA', '12-31-2004');
Try the STR_TO_DATE function:
SELECT STR_TO_DATE('12-31-2004','%m-%d-%Y');
2004-12-31
Yup.. that works... Thanks.
INSERT INTO `test` (`mydate`) VALUES(STR_TO_DATE('12-31-2004', '%m-%d-%Y'));
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Default Date Format
Hello. This is a frequently asked question. For example read these threads: http://lists.mysql.com/mysql/175324 http://lists.mysql.com/mysql/177730 Shuan [EMAIL PROTECTED] wrote: Dear all, How to change the Default Date/DateTime Format of MySQL server? I'm using MySQL 4.1.10-nt on WinXP Pro. Thanks. Shuan -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [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: changing default date format on server
You can change it on the command line by mysql set date_format = '%m-%d-%Y'; However, this may be a client-only view, as I am still trying to get the global variable to change. J.R. PS - Sorry it took me so long Mark, was busy and AFK -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Thursday, March 24, 2005 10:57 AM To: Mark Mchugh Cc: mysql list Subject: Re: changing default date format on server Mark Mchugh [EMAIL PROTECTED] wrote on 03/24/2005 10:26:44 AM: hi, How can i change the default date field to european format, i.e. dd/mm/ ? thanks MArk __ Do you Yahoo!? Yahoo! Mail - 250MB free storage. Do more. Manage less. http://info.mail.yahoo.com/mail_250 You don't. The server's internal representation of dates is not something you can modify. However, your _client_ may have many ways to format date data for viewing. How you change _what_you_see_ depends entirely on which tool you are using to get data from the server and present it for viewing or other operations. Refer to the documentation for the client you are using for details on how to get it to show you dates in the format you want to see. Shawn Green Database Administrator Unimin Corporation - Spruce Pine smime.p7s Description: S/MIME cryptographic signature
RE: changing default date format on server
On Thursday, March 24, 2005 10:06, J.R. Bullington wrote: You can change it on the command line by mysql set date_format = '%m-%d-%Y'; However, this may be a client-only view, as I am still trying to get the global variable to change. [shell] #mysqld -v --help | grep date_format --date_format=name The DATE format (For future). date_format (No default value) [/shell] This has been said before, but date_format is not implemented on the server. You can set it, but I don't think it does anything. J.R. PS - Sorry it took me so long Mark, was busy and AFK -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Thursday, March 24, 2005 10:57 AM To: Mark Mchugh Cc: mysql list Subject: Re: changing default date format on server Mark Mchugh [EMAIL PROTECTED] wrote on 03/24/2005 10:26:44 AM: hi, How can i change the default date field to european format, i.e. dd/mm/ ? thanks MArk However, your _client_ may have many ways to format date data for viewing. How you change _what_you_see_ depends entirely on which tool you are using to get data from the server and present it for viewing or other operations. Refer to the documentation for the client you are using for details on how to get it to show you dates in the format you want to see. Shawn Green Database Administrator Unimin Corporation - Spruce Pine -- Tom Crimmins Interface Specialist Pottawattamie County, Iowa -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Default Date Format
Dear all, How to change the Default Date/DateTime Format of MySQL server? I'm using MySQL 4.1.10-nt on WinXP Pro. Thanks. Shuan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Default date format
Hello! Is there a way how to change the date and time format accepted and returned by MySQL from '-mm-dd' to something different? I've seen several posts here telling it's impossible but they are quite old. Is it still true in recent MySQL versions? I can see date_format, datetime_format and time_format in SHOW VARIABLES, it's even documented somewhere (e.g. http://mysqld.active-venture.com/SET_OPTION.html ) but it seems it doesn't do anything in MySQL 4.1.7. -- Jakub Vrana -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Default date format
Is there a way how to change the date and time format accepted and returned by MySQL from '-mm-dd' to something different? I've seen several posts here telling it's impossible but they are quite old. Is it still true in recent MySQL versions? I can see date_format, datetime_format and time_format in SHOW VARIABLES, it's even documented somewhere (e.g. http://mysqld.active-venture.com/SET_OPTION.html ) but it seems it doesn't do anything in MySQL 4.1.7. `mysqld.exe --help --verbose | grep _format` says: --date_format=name The DATE format (For future). So I guess the variable is prepared but not used for anything yet. -- Jakub Vrana -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
converting timestamps to US Date format
Hi there,
I am having trouble converting a timestamp from mySQL to the US Format mm/dd/.
Can someone please help. I am also having trouble converting -mm-dd into a normal
mm/dd/ format as well.
I am using the following line of code:
date('m/d/Y', $row10['modified']);
$row10['modified'] is the timestamp from the mySQL database I want to convert from.
Thanks in advance.
Gary
Re: converting timestamps to US Date format
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
On Monday 05 July 2004 06:07 pm, Gary Mack wrote:
Hi there,
I am having trouble converting a timestamp from mySQL to the US Format
mm/dd/. Can someone please help. I am also having trouble converting
-mm-dd into a normal mm/dd/ format as well.
I am using the following line of code:
date('m/d/Y', $row10['modified']);
$row10['modified'] is the timestamp from the mySQL database I want to
convert from. Thanks in advance.
http://dev.mysql.com/doc/mysql/en/Date_and_time_functions.html
- --
===
Jabber: tradergt@(smelser.org|jabber.org)
Quote: Give a man a fish and he will eat for a day. Teach him to
fish and he'll sit in a boat and drink beer all day.
===
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.2.4 (GNU/Linux)
iD8DBQFA6d/8ld4MRA3gEwYRApXAAJ4oqjY6uGH/3ow7nO8VXcfpu1SQHwCfXKJS
BlS+89WOMdEGD7DMBROj+XU=
=7vN6
-END PGP SIGNATURE-
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: converting timestamps to US Date format
Gary Mack wrote:
Hi there,
I am having trouble converting a timestamp from mySQL to the US Format mm/dd/. Can someone please help. I am also having trouble converting -mm-dd into a normal mm/dd/ format as well.
Normal format *is* -mm-dd.
I am using the following line of code:
date('m/d/Y', $row10['modified']);
$row10['modified'] is the timestamp from the mySQL database I want to convert from. Thanks in advance.
You want to use MySQL's date_format, eg:
date_format(modified, '%m/%d/%Y')
From memory anyway. Check the documentation.
--
Daniel Kasak
IT Developer
NUS Consulting Group
Level 5, 77 Pacific Highway
North Sydney, NSW, Australia 2060
T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989
email: [EMAIL PROTECTED]
website: http://www.nusconsulting.com.au
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: converting timestamps to US Date format
Gary:
Am I looking at PHP's date() function? You are taking a timestamp
from a MySQL table and wanting to convert it into a US format. Is the
timestamp stored in your MySQL table a Unix timestamp or a MySQL
timestamp?
It's important to note that you can't take a MySQL timestamp and use
PHP's date functions on it, because PHP is expecting a Unix timestamp.
You can either, as Daniel suggested, format the date with MySQL's
date_format() function, or when selecting the date field in your MySQL
query, use MySQL's UNIX_TIMESTAMP() function.
MySQL timestamp format:
http://dev.mysql.com/doc/mysql/en/TIMESTAMP_pre-4.1.html
PHP timestamp format: http://us2.php.net/manual/en/function.time.php
Wes
On Jul 5, 2004, at 7:07 PM, Gary Mack wrote:
Hi there,
I am having trouble converting a timestamp from mySQL to the US Format
mm/dd/. Can someone please help. I am also having trouble
converting -mm-dd into a normal mm/dd/ format as well.
I am using the following line of code:
date('m/d/Y', $row10['modified']);
$row10['modified'] is the timestamp from the mySQL database I want to
convert from. Thanks in advance.
Gary
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: converting timestamps to US Date format
Hi there, I am having trouble converting a timestamp from mySQL to the US Format mm/dd/. Can someone please help. I am also having trouble converting -mm-dd into a normal mm/dd/ format as well. A bit off-topic but, if you are able to use Perl anytime during your processing, ie. if you're into processing a flat-file... there's a very powerful module called Date::Manip, http://search.cpan.org/~sbeck/DateManip-5.42a/Manip.pod take a look at the UnixDate function. ...snip -- 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]
date format problem
Hi, 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. deadline is fast approaching. Matt Tucker thoughtbot
Re: Change the date format.
David Carlos Brunstein [EMAIL PROTECTED] wrote: Hi Egor. Thanks for your answer. I'm relatively new to MySQL. Isn't any way -like an environment variable or configuration file setting or something like that- to change the date format at the MySQL side, not at client side ? No. You can change format only with DATE_FORMAT() function for the DATE and TIME column types. -Original Message- From: Egor Egorov [mailto:[EMAIL PROTECTED] Sent: Saturday, April 10, 2004 8:53 AM To: [EMAIL PROTECTED] Subject: Re: Change the date format. David Carlos Brunstein [EMAIL PROTECTED] wrote: By these days I'm facing a system DB migration to MySQL (the last production version) and I have the followin problem. All the SELECTs sentences are all over the application, and for date/datetime filters are using the dd/mm/ format in the WHERE clause. Is the same in the INSERT/UPDATE statementes. I've studied with MySQL documentation, that use the -mm-dd format for the internal storage and for the data interchange with the clients. So the questions are: Is there any way to set the date/datetime format to MySQL engine, so the client can see it as dd/mm/? Is there any solution with no change to the client application code? Use DATE_FORMAT() function to retrieve dates in the specific format: http://dev.mysql.com/doc/mysql/en/Date_and_time_functions.html But in this case you should rewrite your queries. -- 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: Change the date format.
David Carlos Brunstein [EMAIL PROTECTED] wrote: By these days I'm facing a system DB migration to MySQL (the last production version) and I have the followin problem. All the SELECTs sentences are all over the application, and for date/datetime filters are using the dd/mm/ format in the WHERE clause. Is the same in the INSERT/UPDATE statementes. I've studied with MySQL documentation, that use the -mm-dd format for the internal storage and for the data interchange with the clients. So the questions are: Is there any way to set the date/datetime format to MySQL engine, so the client can see it as dd/mm/? Is there any solution with no change to the client application code? Use DATE_FORMAT() function to retrieve dates in the specific format: http://dev.mysql.com/doc/mysql/en/Date_and_time_functions.html But in this case you should rewrite your queries. -- 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: Change the date format.
Hi Egor. Thanks for your answer. I'm relatively new to MySQL. Isn't any way -like an environment variable or configuration file setting or something like that- to change the date format at the MySQL side, not at client side ? Regards, David. == David Carlos Brunstein System Analyst / Software Developer Buenos Aires, Argentina Mail to: David _ Brunstein @ Yahoo . Com . ar IM: DavidBrunstein @ Hotmail . Com -Original Message- From: Egor Egorov [mailto:[EMAIL PROTECTED] Sent: Saturday, April 10, 2004 8:53 AM To: [EMAIL PROTECTED] Subject: Re: Change the date format. David Carlos Brunstein [EMAIL PROTECTED] wrote: By these days I'm facing a system DB migration to MySQL (the last production version) and I have the followin problem. All the SELECTs sentences are all over the application, and for date/datetime filters are using the dd/mm/ format in the WHERE clause. Is the same in the INSERT/UPDATE statementes. I've studied with MySQL documentation, that use the -mm-dd format for the internal storage and for the data interchange with the clients. So the questions are: Is there any way to set the date/datetime format to MySQL engine, so the client can see it as dd/mm/? Is there any solution with no change to the client application code? Use DATE_FORMAT() function to retrieve dates in the specific format: http://dev.mysql.com/doc/mysql/en/Date_and_time_functions.html But in this case you should rewrite your queries. -- 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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Change the date format.
Hi, I'm new to the list. By these days I'm facing a system DB migration to MySQL (the last production version) and I have the followin problem. All the SELECTs sentences are all over the application, and for date/datetime filters are using the dd/mm/ format in the WHERE clause. Is the same in the INSERT/UPDATE statementes. I've studied with MySQL documentation, that use the -mm-dd format for the internal storage and for the data interchange with the clients. So the questions are: Is there any way to set the date/datetime format to MySQL engine, so the client can see it as dd/mm/? Is there any solution with no change to the client application code? Thanks in advance. David. == David Carlos Brunstein System Analyst / Software Developer Buenos Aires, Argentina Mail to: David _ Brunstein @ Yahoo . Com . ar IM: DavidBrunstein @ Hotmail . Com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
yearweek() result back to date format?
How can I use the output string of the yearweek() function (for example, 200346 below): mysql select dayname(2003-11-12), yearweek(2003-11-12); +---++ | dayname(2003-11-12) | yearweek(2003-11-12) | +---++ | Wednesday | 200346 | +---++ 1 row in set (0.00 sec) to extract the start and end dates for that week (for example, 2003-11-09 and 2003-11-15 above)? I'm using server v3.23. -- Trevor Smith|[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: yearweek() result back to date format?
hi, i was solving same problem before month and i finished it by this way:
select dayname(2003-11-12), yearweek(2003-11-12),ADDDATE('2003-01-06',
INTERVAL 7*(WEEK('2003-11-12')-1) DAY),ADDDATE('2003-01-06', INTERVAL
7*(WEEK('2003-11-12')-1)+6 DAY);
but there is constant '2003-01-06', that's date of first Monday (Monday
because i'm Czech) of first week in a year.
There is a lot of ways to number weeks, see WEEK() function .
I think it's impossible to get this date (the date of first day of first
week) just in query, maybe you can write your own function ;))
But if you are using something like PHP etc., you can simply go throw 6 days
before and after your date, and simply check while the week number is same
like weeknumber you got from mysql.
I hope that it helps you little.
`WEEK(date [,mode])'
The function returns the week number for `date'. The two-argument
form of `WEEK()' allows you to specify whether the week starts on
Sunday or Monday and whether the return value should be in the
range 0-53 or 1-52. When `mode' argument is omitted the value of a
`default_week_format' server variable (or 0 in MySQL 4.0 or
earlier) is assumed. *Note SET OPTION::.
The following table demonstrates how the `mode' argument works:
*Value* *Meaning*
0 Week starts on Sunday; return value range is 0 to 53;
week 1 is the first week that starts in this year
1 Week starts on Monday; return value range is 0 to 53;
week 1 is the first week that has more than 3 days in
this year
2 Week starts on Sunday; return value range is 1 to 53;
week 1 is the first week that starts in this year
3 Week starts on Monday; return value range is 1 to 53;
week 1 is the first week that has more than 3 days in
this year
4 Week starts on Sunday; return value range is 0 to 53;
week 1 is the first week that has more than 3 days in
this year
5 Week starts on Monday; return value range is 0 to 53;
week 1 is the first week that starts in this year
6 Week starts on Sunday; return value range is 1 to 53;
week 1 is the first week that has more than 3 days in
this year
7 Week starts on Monday; return value range is 1 to 53;
week 1 is the first week that starts in this year
The `mode' value of 3 can be used as of MySQL 4.0.5. The `mode'
value of 4 and above can be used as of MySQL 4.0.17.
- Original Message -
From: Trevor Smith [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: Monday, January 26, 2004 12:56 PM
Subject: yearweek() result back to date format?
How can I use the output string of the yearweek() function
(for example, 200346 below):
mysql select dayname(2003-11-12), yearweek(2003-11-12);
+---++
| dayname(2003-11-12) | yearweek(2003-11-12) |
+---++
| Wednesday | 200346 |
+---++
1 row in set (0.00 sec)
to extract the start and end dates for that week (for example,
2003-11-09
and 2003-11-15 above)?
I'm using server v3.23.
--
Trevor Smith|[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Can I change the date format...
Hello Is there anyway to change the default date format (possibly in a .ini or .cfg file) from -MM-DD to another format ? (ie, mm/dd/ or any user-defined format). The useage that I'm looking at is to import a CSV file into a table (using Load data infile command) where dates are stored in mm/dd/ format and can change based on the source of the data. I can import the date fields into a char field and then convert it but would like to save the time to transform and copy contents into another table. Karam __ Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software http://sitebuilder.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Can I change the date format...
Karam Chand [EMAIL PROTECTED] wrote: Is there anyway to change the default date format (possibly in a .ini or .cfg file) from -MM-DD to another format ? (ie, mm/dd/ or any user-defined format). No, You can't. The useage that I'm looking at is to import a CSV file into a table (using Load data infile command) where dates are stored in mm/dd/ format and can change based on the source of the data. I can import the date fields into a char field and then convert it but would like to save the time to transform and copy contents into another table. -- 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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Can I change the date format...
- Original Message - From: Victoria Reznichenko [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Saturday, August 16, 2003 11:48 AM Subject: Re: Can I change the date format... Karam Chand [EMAIL PROTECTED] wrote: Is there anyway to change the default date format (possibly in a .ini or .cfg file) from -MM-DD to another format ? (ie, mm/dd/ or any user-defined format). No, You can't. The useage that I'm looking at is to import a CSV file into a table (using Load data infile command) where dates are stored in mm/dd/ format and can change based on the source of the data. I can import the date fields into a char field and then convert it but would like to save the time to transform and copy contents into another table. Victoria's answer is correct. But you can achieve this by using the DATE_FORMAT() and CONCAT() MySQL functions in a SQL query. This would be a manual process. Or as you mention you could just do the manual conversion at the other end. For example, - write SQL queries that will export the table data in CVS format using the DATE_FORMAT and CONCAT() functions - or, add extra string/char columns to your tables which store the date in the required format temporarily for the export / import process. -- 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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.510 / Virus Database: 307 - Release Date: 14/08/2003 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: ORDER BY with Date Format
probably one of the many replys you'll get on this, but ORDER BY month(start_date) assuming the column that has the date is start_date On Thursday 10 July 2003 14:51, Mike At Spy wrote: I am trying to create an order by in a select statement that takes a standard 'date' column in a table and sorts the returned data by the month of that date. Off hand, I tried ORDER BY start_date(m) And I know that is wrong. Anyone have any suggestions? Thanks, -Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
ORDER BY with Date Format
I am trying to create an order by in a select statement that takes a standard 'date' column in a table and sorts the returned data by the month of that date. Off hand, I tried ORDER BY start_date(m) And I know that is wrong. Anyone have any suggestions? Thanks, -Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: ORDER BY with Date Format
Try: order by left(start_date,7) That will order by year then month for a standard date column.. Cheers, Andrew -Original Message- From: Mike At Spy [mailto:[EMAIL PROTECTED] Sent: Thursday 10 July 2003 20:51 To: [EMAIL PROTECTED] Subject: ORDER BY with Date Format I am trying to create an order by in a select statement that takes a standard 'date' column in a table and sorts the returned data by the month of that date. Off hand, I tried ORDER BY start_date(m) And I know that is wrong. Anyone have any suggestions? Thanks, -Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
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?
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: Help - Convert Date from longtext to MySQL date format
* Roger Baklund [...] UPDATE a SET new_date = ( MID(my_date,7,4),'-', MID(my_date,1,2),'-', MID(my_date,4,2)); huh? How did this happen...? I just checked my outbox, and the message I wrote yesterday[1] contained new_date = CONCAT(. It seems as the substring CON has been replaced with a linefeed somewhere along the way...? Anyway, trying again, the correct statement should be: UPDATE a SET new_date = CONCAT( MID(my_date,7,4),'-', MID(my_date,1,2),'-', MID(my_date,4,2)); [1] That was wednesday. With the current long delays on the list, this message will probably not be read until friday... :/ -- Roger sql - 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: Help - Convert Date from longtext to MySQL date format
* Wong Zach-CHZ013 [...] In table a, the columns are my_date - longtext num - int(11) [...] Q: How do I convert 08/06/2002 to 2002-08-06 format LONGTEXT is not a good column type for dates, you should use the special 'date' type, see URL: http://www.mysql.com/doc/en/Column_types.html . To change the table a, adding a new date column: ALTER TABLE a ADD new_date date; Updating the new column with dates from the old: UPDATE a SET new_date = CAT( MID(my_date,7,4),'-', MID(my_date,1,2),'-', MID(my_date,4,2)); Finally, after checking the result, you may want to drop the old column and rename the new one, and you can use ALTER TABLE for this too: ALTER TABLE a DROP my_date; ALTER TABLE a CHANGE new_date my_date date; URL: http://www.mysql.com/doc/en/ALTER_TABLE.html URL: http://www.mysql.com/doc/en/UPDATE.html HTH, -- Roger - 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: Help - Convert Date from longtext to MySQL date format
this command should get your data moved to a new sql table insert into table2 select (concat(mid(my_date,7,4),'-',mid(my_date,1,2),'-',mid(my_date,4,2))) from table1; - hcir On Tuesday, January 28, 2003, at 01:45 PM, Wong Zach-CHZ013 wrote: Hi I have a few tables in a database Z, namely table a b c In table a, the columns are my_date - longtext num - int(11) eg: mysql select * from a; +--+--+--+ | my_date | x| +--+--+ | 08/06/2002 |1 | | 08/07/2002 |2 | +--+--+--+ 2 rows in set (0.00 sec) Tables b and c have the same table structure and data type format. Q: How do I convert 08/06/2002 to 2002-08-06 format without having to reinput all my data from scratch ? - 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
Help - Convert Date from longtext to MySQL date format
Hi I have a few tables in a database Z, namely table a b c In table a, the columns are my_date - longtext num - int(11) eg: mysql select * from a; +--+--+--+ | my_date | x| +--+--+ | 08/06/2002 |1 | | 08/07/2002 |2 | +--+--+--+ 2 rows in set (0.00 sec) Tables b and c have the same table structure and data type format. Q: How do I convert 08/06/2002 to 2002-08-06 format without having to reinput all my data from scratch ? Any help is FULLY appreciated. Thanks - 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
String Functions and Date Format Living Together in Harmony?
hello all, i have the following selection in my query... DATE_FORMAT(Images.TIMESTAMP, '%a %b %d at %l:%i %p') as TIME now, i want to apply LCASE or LOWER to it, but i'm a little unsure how to handle the syntax with two sets of parentheses. any insight? tia, j - 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: String Functions and Date Format Living Together in Harmony?
At 12:39 -0400 9/20/02, Stanley, Jason wrote: hello all, i have the following selection in my query... DATE_FORMAT(Images.TIMESTAMP, '%a %b %d at %l:%i %p') as TIME now, i want to apply LCASE or LOWER to it, but i'm a little unsure how to handle the syntax with two sets of parentheses. The alias follows the entire expression, so it would be done like this: LCASE(DATE_FORMAT()) as TIME any insight? tia, j - 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
Insert date format
Hello,
I am developing an aplication where I need format the date for inserting
into mySQL.
With DATE_FORMAT I can format the mySQL date to my date, but I haven't
found a good way to format my date to the mySQL date.
My format is 'dd-mm-' and to convert it to '-mm-dd' I'm using
the next sequence:
SELECT DATE_FORMAT(
CONCAT(
SUBSTRING_INDEX('03-05-2002', '-', -1),
'-',
SUBSTRING_INDEX(SUBSTRING_INDEX('03-05-2002', '-', 2),
'-', -1),
'-',
SUBSTRING_INDEX('03-05-2002', '-', 1)),
'%Y-%m-%d' );
I have searched in the manual, the list and in the DuBois' book, but I
haven't found a single function to parse my date to the mySQL date. Is
that rigth? Is there no function to parse from any date to the mySQL
date?
Regards
Javier
-
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: Insert date format
Hello Javier,
I am developing an aplication where I need format the date for inserting
into mySQL.
=what language is your application written in?
=dn
With DATE_FORMAT I can format the mySQL date to my date, but I haven't
found a good way to format my date to the mySQL date.
My format is 'dd-mm-' and to convert it to '-mm-dd' I'm using
the next sequence:
SELECT DATE_FORMAT(
CONCAT(
SUBSTRING_INDEX('03-05-2002', '-', -1),
'-',
SUBSTRING_INDEX(SUBSTRING_INDEX('03-05-2002', '-', 2),
'-', -1),
'-',
SUBSTRING_INDEX('03-05-2002', '-', 1)),
'%Y-%m-%d' );
I have searched in the manual, the list and in the DuBois' book, but I
haven't found a single function to parse my date to the mySQL date. Is
that rigth? Is there no function to parse from any date to the mySQL
date?
Regards
Javier
-
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: Insert date format
Hello,
I am developing an aplication where I need format the date for
inserting into mySQL.
=what language is your application written in?
=dn
The application is written in Java, but the problem is that the module
where the dates for the DB are managed is an open source framework and I
wouldn't like to touch that code.
The framework provides a way to modify the dates from/to the database
defining native database functions in a config file.
Anyway, the problem is alredy solved, but I still have the dude if I
there is a better solution.
Regards
Javier
With DATE_FORMAT I can format the mySQL date to my date, but I
haven't found a good way to format my date to the mySQL date.
My format is 'dd-mm-' and to convert it to '-mm-dd'
I'm using
the next sequence:
SELECT DATE_FORMAT(
CONCAT(
SUBSTRING_INDEX('03-05-2002', '-', -1),
'-',
SUBSTRING_INDEX(SUBSTRING_INDEX('03-05-2002', '-', 2),
'-', -1),
'-',
SUBSTRING_INDEX('03-05-2002', '-', 1)),
'%Y-%m-%d' );
I have searched in the manual, the list and in the DuBois'
book, but I
haven't found a single function to parse my date to the
mySQL date. Is
that rigth? Is there no function to parse from any date to
the mySQL
date?
Regards
Javier
-
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
[ date format ]
Hi, the date format of MySql is /mm/dd (2002/07/16) right? How I do to insert the date typing in my form dd/mm/ (16/07/2002)? It is possible? Thanks, Elby _ 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
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
specifying date format for search
Does anyone know if version 4.0 will support formatted date queries.
Example:
select create_date from what_ever where create_date
date_format('03/01/2002','%m/%d/%Y')
sql, query
--
Walter Anthony
System Administrator
National Electronic Attachment
Atlanta, Georgia
1-800-782-5150 ext. 1608
If it's not broketweak it
-
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: specifying date format for search
At 11:08 -0400 6/25/02, walt wrote:
Does anyone know if version 4.0 will support formatted date queries.
Example:
select create_date from what_ever where create_date
date_format('03/01/2002','%m/%d/%Y')
Why not just write the date correctly? That is, what does
this gain you?
sql, query
--
Walter Anthony
System Administrator
National Electronic Attachment
Atlanta, Georgia
1-800-782-5150 ext. 1608
If it's not broketweak it
-
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
Date format
If you have mutli-date fields, can you set one date format in mysql sql statement for all fields? I have 9 date fields, that like to have the same format. Also I don't want it to work fields that have -00-, can I tell not to show those? - 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
Date Format
Running MYSQL 3.23 can the date mmdd be reversed when using Access or VB frontend so that users can enter it as they are used to eg. ddmm. Also I tried creating a new field for date using DATE(8) BUT the sql statement came back with an error. I tried changing the format of the date field to 8 characters but the change did not work I still get mmddhhmmss. Any ideas? regards Trevor - 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
date format
razvan, Tuesday, March 05, 2002, 7:46:31 AM, you wrote: r Hi, please help me with this one! r I now the date_format function, it converts the standard format sql r (-mm-dd) r to any format, but my problem is that i want to convert from another format r (eg. dd/mm/) to the standard format, in this way i can use insert with r my format. r Can I use another format for date when creating a table? r thanks! No, you can't. You can find info about date and time types, examples of usage in the manual in the section 6.2.2. Date and Time Types: http://www.mysql.com/doc/D/a/Date_and_time_types.html -- For technical support contracts, goto https://order.mysql.com/ 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
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
Date Format Query
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
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
Override date format in MyODBC
Hi All, Is there any way to modify the date format used by MyODBC when returning dates? By example, I have a field in the database with the value 2001-11-01 (this is Nov. 1st, 2001). When I read it in my VB program I get the next date 11/1/2001; this is because the computer date format is set to mm/dd/ (This setting is controlled in the windows control panel), but I want to be able to get the next value 1/11/2001 (dd/mm/) without modifying the computer settings. I'd appreciate any idea or comment. Thanks in advance. andres - 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
Date Format in MySQL
Hello Friends Is possible to change the MysQL Date fromat from -mm-dd to dd-mm- ? Thanks Claudemir F. Martins - 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
Default date format
Is there a default format for Date ? /mm/dd or mm/dd/ or dd/mm/ ? I am not sure which format I should use in SQL. - 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: Default date format
Hi -mm-dd is how it is stored in the db, you have to pass the date like that, and that is how it will be returned. Regards ~ Warren van der Merwe Software Director PRT Trading (Pty) Ltd t/a RedTie Durban, South Africa Cell (+27-83) 262-9163 Office (+27-31) 767-0249 -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] ]On Behalf Of pak Sent: 18 July 2001 08:58 To: [EMAIL PROTECTED] Subject: Default date format Is there a default format for Date ? /mm/dd or mm/dd/ or dd/mm/ ? I am not sure which format I should use in SQL. - 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
TR : Date format prolem in database
Database,sql,query -Message d'origine- De : Nicolas Villatte [mailto:[EMAIL PROTECTED]] Envoyé : mardi 22 mai 2001 23:18 À : '[EMAIL PROTECTED]' Objet : Date format prolem I insert a date like this '2002-10-15' in the DB, everything is ok when doing a select with DBTools, but when I am listing the fields in Jscript using ASP The field format is : 'Tue Oct 15 00:00:00 UTC+0200 2002'... Does anyone has a clue about this problem? Thanks, Nicolas Villatte __ IT Manager Creative Web SPRL Rue Kessels straat, 38 1030 Brussels Office Phone: +32 2 2450110 Office Fax: +32 2 2161628 Mobile Phone : +32 477 588136 Internet Mail: mailto:[EMAIL PROTECTED] Visit us on the web: http://www.creativeweb.be __ BEGIN:VCARD VERSION:2.1 N:Villatte;Nicolas FN:Nicolas Villatte ORG:Creative Web SPRL TITLE:IT Manager TEL;WORK;VOICE:+32 (2) 245.01.10 TEL;HOME;VOICE:+32 (02) 344.94.05 TEL;CELL;VOICE:+32 (477) 588136 TEL;WORK;FAX:+32 (2) 216.16.28 ADR;WORK:;;Rue Kessels, 38;Bruxelles;;1030;Belgique LABEL;WORK;ENCODING=QUOTED-PRINTABLE:Rue Kessels, 38=0D=0ABruxelles 1030=0D=0ABelgique ADR;HOME:;;Rue des châtaignes, 51;Bruxelles;;1190;Belgique LABEL;HOME;ENCODING=QUOTED-PRINTABLE:Rue des ch=E2taignes, 51=0D=0ABruxelles 1190=0D=0ABelgique EMAIL;PREF;INTERNET:[EMAIL PROTECTED] REV:20010226T195907Z END:VCARD BEGIN:VCARD VERSION:2.1 N:Villatte;Nicolas FN:Nicolas Villatte ORG:Creative Web SPRL TITLE:IT Manager TEL;WORK;VOICE:+32 (2) 245.01.10 TEL;HOME;VOICE:+32 (02) 344.94.05 TEL;CELL;VOICE:+32 (477) 588136 TEL;WORK;FAX:+32 (2) 216.16.28 ADR;WORK:;;Rue Kessels, 38;Bruxelles;;1030;Belgique LABEL;WORK;ENCODING=QUOTED-PRINTABLE:Rue Kessels, 38=0D=0ABruxelles 1030=0D=0ABelgique ADR;HOME:;;Rue des châtaignes, 51;Bruxelles;;1190;Belgique LABEL;HOME;ENCODING=QUOTED-PRINTABLE:Rue des ch=E2taignes, 51=0D=0ABruxelles 1190=0D=0ABelgique EMAIL;PREF;INTERNET:[EMAIL PROTECTED] REV:20010226T195907Z END:VCARD smime.p7s
RE: specifying date format for retrieving dates and datetimes
Is there a simple way to force mySQL to always return dates and datetime in ISO long format, without the '-', ':' and spaces? (in a TIMESTAMP(14) like format) I always want 20010517 rather than 2001-05-17, and 20010517130500 rather than 2001-05-17 13:05:00 Use ints and bigints? - 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: Load data infile date format issue
On 09-May-01 Darla Baker wrote: I have a text file which is extracted from a non-sql database each night and then a cron sql script runs to insert the text data into the mysql database tables. My problem is that the date data in the text file is formatted incosistently (12/31/00 or 12-31-00) and so the fields that hold date data are currently char datatypes. Since I need the dates to be dates for queries, I need a solution which will allow me to insert the date data into the database as a date field datatype. snip Create a holding table to load: create table bar ( txtdate char(10) not null default '00-00-00', foo char(30) default 'da_foo' ); Load the data: mysql select * from bar; +--+---+ | txtdate | foo | +--+---+ | 12/31/00 | foo 1 | | 05-09-01 | foo 2 | +--+---+ Add the DATE column: mysql alter table bar add column da_date date not null default '1900-02-31' - after txtdate; Do the conversion: mysql update bar set da_date= - concat(concat(right(txtdate,2),'-'),left(txtdate,5)); Cleanup: mysql alter table bar drop column txtdate; mysql select * from bar; ++---+ | da_date| foo | ++---+ | 2000-12-31 | foo 1 | | 2001-05-09 | foo 2 | ++---+ Insert into your main working table: insert into blah (my_date,my_foo) select * from bar; 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. - 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
Load data infile date format issue
I have a text file which is extracted from a non-sql database each night and then a cron sql script runs to insert the text data into the mysql database tables. My problem is that the date data in the text file is formatted incosistently (12/31/00 or 12-31-00) and so the fields that hold date data are currently char datatypes. Since I need the dates to be dates for queries, I need a solution which will allow me to insert the date data into the database as a date field datatype. I'm sure some type of script could be written to reformat the dates so they could be inserted as dates, but I don't have the knowledge of any scripting language to do this. If anyone has accomplished this task or can point me in the right direction to solve this problem, I would appreciate it. Darla __ Do You Yahoo!? Yahoo! Auctions - buy the things you want at great prices http://auctions.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
date format
Hi, I want to import in my DB in MySql a data. Everyting data is ok but for date format ( yy/mm/dd ) this I have in .csv format when I importI see that -00-00 in all date fields Any help is realy apreciate Regards Dan - 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
import - date format
Hi there, I have many files need import into mysql database. Unfortunately, the date format in my csv file is mm/dd/, so is there a wordaround that I don't need write another script to convert all my files? Thanks. Kevin - 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: import - date format
Hi, If you use DBTools (http://dbtools.vila.bol.com.br), using the import text wizard you have a lot of parameters to set, including date format and separators. Unfortunatelly, runs only on windows platforms. []'s Crercio -Original Message- From: Kevin Xin Lin [mailto:[EMAIL PROTECTED]] Sent: quinta-feira, 5 de abril de 2001 17:12 To: [EMAIL PROTECTED] Subject: import - date format Hi there, I have many files need import into mysql database. Unfortunately, the date format in my csv file is mm/dd/, so is there a wordaround that I don't need write another script to convert all my files? Thanks. Kevin - 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
How to set the date format to dd/mm/yyyy for all tables in MySql for insertion, selection etc.,
Hello, I would like to change the default date format MySql from /mm/dd to dd/mm/. Does anyone know, how to make this change in MySql? Thanks. - 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: How to set the date format to dd/mm/yyyy for all tables in MySql for insertion, selection etc.,
On Fri, Jan 26, 2001 at 01:40:12PM -0700, Web master wrote: Hello, I would like to change the default date format MySql from /mm/dd to dd/mm/. Does anyone know, how to make this change in MySql? You can't. However, you can select DATE, DATETIME, or TIMESTAMP values using DATE_FORMAT() in whatever format you like. - 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: How to set the date format to dd/mm/yyyy for all tables in MySql for insertion, selection etc.,
Thank paul. I know about Date_format, but was curious if there is some setup parameters would allow us to define the MySql date format . Paul DuBois wrote: On Fri, Jan 26, 2001 at 01:40:12PM -0700, Web master wrote: Hello, I would like to change the default date format MySql from /mm/dd to dd/mm/. Does anyone know, how to make this change in MySql? You can't. However, you can select DATE, DATETIME, or TIMESTAMP values using DATE_FORMAT() in whatever format you like. - 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
