ok thanks i will try thatCelauran wrote:It might. I'd definitely make a backup first. Maybe add a second column and run an update across the table using STR_TO_DATE or something to convert them to valid DATETIME values (or just DATE if that's all you need). You could then compare a sample of rows to make sure the values aligned. If they do, delete the old column and rename the new.
date range selecting incorrect dates
Moderator: General Moderators
-
jonnyfortis
- Forum Contributor
- Posts: 462
- Joined: Tue Jan 10, 2012 6:05 am
Re: date range selecting incorrect dates
-
jonnyfortis
- Forum Contributor
- Posts: 462
- Joined: Tue Jan 10, 2012 6:05 am
Re: date range selecting incorrect dates
what i have had to do it as you suggested. when i convert to DATE it doesnt keep the correct format.Celauran wrote:It might. I'd definitely make a backup first. Maybe add a second column and run an update across the table using STR_TO_DATE or something to convert them to valid DATETIME values (or just DATE if that's all you need). You could then compare a sample of rows to make sure the values aligned. If they do, delete the old column and rename the new.
i have another question
the dates i am dealing are returned from a third party bank and these are fixed and cant be changed
MM/DD/YY
if i set the database column to DATE this format is 0000-00-00
Will this be ok?
Re: date range selecting incorrect dates
Sure, that shouldn't be an issue. Read them in in the format the bank sends, store them in the format you require.
Code: Select all
php > $bank_date = new DateTime('09/01/14');
php > echo $bank_date->format('Y-m-d') . "\n";
2014-09-01-
jonnyfortis
- Forum Contributor
- Posts: 462
- Joined: Tue Jan 10, 2012 6:05 am
Re: date range selecting incorrect dates
yes i get that but what will go in the other two digits for the year 0000-00-00 ?Celauran wrote:Sure, that shouldn't be an issue. Read them in in the format the bank sends, store them in the format you require.
Code: Select all
php > $bank_date = new DateTime('09/01/14'); php > echo $bank_date->format('Y-m-d') . "\n"; 2014-09-01
Re: date range selecting incorrect dates
I don't understand the question. We received two digits for the year and we're outputting four.
-
jonnyfortis
- Forum Contributor
- Posts: 462
- Joined: Tue Jan 10, 2012 6:05 am
Re: date range selecting incorrect dates
i have tried a few variation.jdhmtl wrote:I don't understand the question. We received two digits for the year and we're outputting four.
i am receiving from the bank 31/07/14 but being inserted into the column of the database is 2031-07-14
as there are two additional digits in the database these look like they are being populated with 20
so when i output short dates i can get the correct dates
for example d/m/y comes out as 14/07/31
but if i try longer dates it comes out incorrect
Y M j comes out as 2031 Jul 14
i think i will just have to use the short d/m/y. i am just hoping the way it is stored wont be an issue
thanks
Re: date range selecting incorrect dates
Are you modifying the dates at all before inserting them?
That's the same date.so when i output short dates i can get the correct dates
for example d/m/y comes out as 14/07/31
but if i try longer dates it comes out incorrect
Y M j comes out as 2031 Jul 14