I need to show results where the column “promiseddate” is equal to todays date.
I have a large database and one of the columns is named “promiseddate” which holds date data in this format
1/1/2015
But in the database it isn’t set as “DATE” but as “VARCHAR”
How can I select the rows that match todays date in this format: 1/1/2015?? is it still doable even though I have them all as a “VARCHAR”?
I appreciate any help greatly and will send good luck your way.
Why do you want to use the WRONG column format for a date column? First step is set it to the correct type.
Its too late for that now. Can you help me please instead of criticizing me?
It never too late. If you insist on doing things wrong you arent going to get much help from anyone.
Well, you should look into the string-to-date or other such functions. They are easy to use. Here are a couple
links to help you learn about them…
To convert a string to time format:
http://www.w3schools.com/php/func_date_strtotime.asp
To format a time-formatted value to display in a certain format:
http://www.w3schools.com/php/php_date.asp
Normally, you use the “DATETIME” format in a database. This holds a date and timestamp in a certain format
and you use the formatting functions to display it as you need to. You must save the timestamp in the way
it is housed in the database. You can’t store “1/1/2016” as-is into a DATETIME field in the database.
Hope that helps…
You can run functions in a database to fix issues like that. Change the length in the column to a larger size, then convert the type to a timestamp or other appropriate field type without any loss. It would just mean that previous entries would be “mm/dd/yyyy 00:00:00” format.
No, the format is YYYY-MM-DD, not mm/dd/yyyy
Thanks for the help everyone
So, does that mean you fixed it up and this is solved or do you need further help doing the conversion?
Just in case you didn’t understand, you can convert the string to a time format and then into a dated format
to store into the database. Just create a new col and go thru all of them using a while loop and convert them
all to the correct format that was mentioned. Then, you can use regular date functions for calculations…
No need for all that fuss and muss. Just run the following query and then change the column type to date.
[php]UPDATE your_table
SET date_field = DATE(STR_TO_DATE(date_field, ‘%m/%d/%Y’))
WHERE DATE(STR_TO_DATE(date_field, ‘%m/%d/%Y’)) <> ‘0000-00-00’;[/php]
Kevin? I do not understand, doesn’t that just alter the format of a string field?
It doesn’t update the field from a varchar to a datetime that way does it? I know you are good, so I am
sure I am missing something! Explain to me… Thanks…
Its not any more complicated then what I already said. The query will change your dates to the proper date format, at which point you can then update the column type from varchar to date
doesn't that just alter the format of a string field?It alters the format of the data in the field.
It doesn't update the field from a varchar to a datetime that way does it?No. You change it after the dates are in the right format from running the query.
Okay, So, it still requires the ALTER column command to change the field format. That was the part I didn’t
understand. Perhaps you should have shown him that query also. Thanks!
[member=43746]ErnieAlex[/member], I didn’t notice it was you I was replying to. I assumed it was op. I did say to change the column type after you run the query though.
run the following query and [b]then change the column type to date[/b]