473,562 Members | 3,059 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Converting VARCHAR "date" info to an actual date field

I have inherited a table where date information was saved from PHP as a VARCHAR.
(Sigh.) This means that the so-called date fields look like this :

August 1, 2005, 9:09 am EDT
October 13, 2004, 12:28 pm EDT

This makes them essentially useless for sorting and logical evaluation.
Understandbly, the MySQL date functions don't seem able to convert the field to
a "real" date field. This yields a blank (or maybe null) "realdate" :

SELECT *, STR_TO_DATE(tex tdate,'%Y/%m/%d') as realdate FROM table ORDER BY
realdate;

Can anyone suggest a way to fix this (aside from data entry) ? I'm willing to
work with complex field manipulations -- or will quite happily ALTER the table
to add a new realdate field to avoid future problems. But I'm pretty much at a
loss about where to start.

Any and all advice will be _greatly_ appreciated.

Aug 15 '05 #1
3 23803
>(Sigh.) This means that the so-called date fields look like this :

August 1, 2005, 9:09 am EDT
October 13, 2004, 12:28 pm EDT

This makes them essentially useless for sorting and logical evaluation.
Understandbl y, the MySQL date functions don't seem able to convert the field to
a "real" date field. This yields a blank (or maybe null) "realdate" :

SELECT *, STR_TO_DATE(tex tdate,'%Y/%m/%d') as realdate FROM table ORDER BY
realdate;
But the format you pass str_to_date doesn't match your examples!
You have to tell it what format to use. To take a really ridiculous
example:
01 02 03 04 05 06
now, which one is the year? the month?

Something like:
str_to_date(tex tdate, '%M %d, %Y %h:%i %p')
might work. I don't think it does timezones. Read the documentation for
date_format to get the meaning of all the %codes.
Can anyone suggest a way to fix this (aside from data entry) ? I'm willing to
work with complex field manipulations -- or will quite happily ALTER the table
to add a new realdate field to avoid future problems. But I'm pretty much at a
loss about where to start.


If you can get the format correct so it works, and you're going to be doing
much comparing and sorting on the field, I suggest making a new realdate
field, loading it from the text field:

update table set realdate = str_to_date(tex tdate, '%M %d, %Y %h:%i %p');

modify your queries to use the new field (and, especially, enter a correct
date in realdate), and eventually drop the old one.
Gordon L. Burditt

Aug 15 '05 #2
<us****@isotope REEMOOVEmedia.c om> wrote in message
news:vs******** *************** *********@4ax.c om...
I have inherited a table where date information was saved from PHP as a VARCHAR. (Sigh.) This means that the so-called date fields look like this :

August 1, 2005, 9:09 am EDT
October 13, 2004, 12:28 pm EDT

This makes them essentially useless for sorting and logical evaluation.
Understandbly, the MySQL date functions don't seem able to convert the field to a "real" date field. This yields a blank (or maybe null) "realdate" :

SELECT *, STR_TO_DATE(tex tdate,'%Y/%m/%d') as realdate FROM table ORDER BY
realdate;

Can anyone suggest a way to fix this (aside from data entry) ? I'm willing to work with complex field manipulations -- or will quite happily ALTER the table to add a new realdate field to avoid future problems. But I'm pretty much at a loss about where to start.


You need to re-arrange the date string to the euro format "yyyy-m-d h:m"
that MySQL likes.
The best option would be to use any (of the many) languages adept at string
manipulation.
However -
You can do it all using MySQL string functions in an update query. It's just
sort of ugly and complicated.

If you can front this database with Microsoft Access, you can make short
shrift of this problem using the VBA functions DATEVALUE() and TIMEVALUE()
which will interpret your given datetime string straight up (almost :-).
This is the cleanest option, but I can give you the complex MySQL only
solution if you ask.

What I might do is make one pass with an UPDATE query that rearranges the
dates/times strings.
Then
I would either convert the field varchar->datetime
or
I would CONVERT them to datetime with a SELECT query and INSERT them into a
new table.

-Tom


Aug 15 '05 #3
>>SELECT *, STR_TO_DATE(tex tdate,'%Y/%m/%d') as realdate FROM table ORDER BY
realdate;


But the format you pass str_to_date doesn't match your examples!
You have to tell it what format to use. To take a really ridiculous
example:
01 02 03 04 05 06
now, which one is the year? the month?


Aargh! I was (stupidly) specifying the output format, rather than the input
format. Thanks for clueing me in, Gordon. It's working now.

And thanks to Tom for the reply, too.
Aug 16 '05 #4

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
5137
by: Michael Albanese | last post by:
I am building an application to report on-the-job injuries and incidents. There are a lot of Date fields, some of which are optional and can be left blank by the user. I have allowed Nulls on these fields in my SQL Server DB, as well as in my stored proc. The problem is that when I submit this data i get an error that states "Input not in...
3
1244
by: pw | last post by:
Hello, How can I typecast a date generated from VARCHAR fields into a date field ie: UPDATE inventory SET date_field = vc_year||'-'||vc_month||'-'||vc_day;
2
4539
by: devprog | last post by:
objConn = New ADODB.Connection objConn.Open("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & dbPath _ & ";Jet OLEDB:Database Password=" & dbPassword) This cause error: (VB.NET. ) sqlString = "insert into table1 " _ & "(Date1,Time,Ext,Co) values " _ & "('04/04/05','06:02PM','101','01');"
11
21058
by: walterbyrd | last post by:
My MySQL table has a field that is set as type "date." I need to get today's date, and insert it into that field. The default for that MySQL field is 2006-00-00. I know about the date() function. I have tried date("Y-m-d") and date("U"), neither worked. The date field was just filed with all zeros. Can anybody point me in the right...
17
5246
by: Petyr David | last post by:
Just looking for the simplest. right now my perl script returns an error messge to the user if the date string is invalid. would like to do this before accessing the server. TX
5
2152
by: veaux | last post by:
I'm thinking this is easy but can't get it. I have a table with following: Table1 Date 1/1/2007 Table2 Type 0107 (This is MMYY of above) So I'm having trouble using a query to turn the date from Table 1 into
2
2667
by: jacc14 | last post by:
Hi Hope there is someone out there that can help. I am sure this is an easy one although not easy to explain. I have a form which produces a report using a query. On the form I have a start and end date which is in the query. So >=!!!. If I enter 2 dates it will produce the data I need. However the date field is a date and time...
2
4715
by: stainless | last post by:
I know this is probably simple but I cannot find a method of converting a date string into a format that matches the DatePicker format in C# eg string "20080131" converted to "31 January 2008" I tried datetime.parseexact but could not find the definition for the appropriate format string. This would be a very useful tool fo me. Any...
1
3650
by: =?Utf-8?B?bGF3ODc4Nw==?= | last post by:
i am looking for some MS Excel formula or MS Visal Basic Marco for converting Date to Lunar Date. tks
0
7867
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. ...
1
7625
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
0
7934
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
0
6219
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...
1
5476
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes...
0
5193
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert...
0
3606
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
2073
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
1
1187
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.