Page 2 of 2
Re: date range selecting incorrect dates
Posted: Tue Aug 26, 2014 7:24 am
by jonnyfortis
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.
ok thanks i will try that
Re: date range selecting incorrect dates
Posted: Fri Aug 29, 2014 4:32 am
by jonnyfortis
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.
what i have had to do it as you suggested. when i convert to DATE it doesnt keep the correct format.
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
Posted: Fri Aug 29, 2014 6:07 am
by Celauran
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
Posted: Fri Aug 29, 2014 7:56 am
by jonnyfortis
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
yes i get that but what will go in the other two digits for the year 0000-00-00 ?
Re: date range selecting incorrect dates
Posted: Fri Aug 29, 2014 8:18 am
by jdhmtl
I don't understand the question. We received two digits for the year and we're outputting four.
Re: date range selecting incorrect dates
Posted: Sat Aug 30, 2014 4:18 am
by jonnyfortis
jdhmtl wrote:I don't understand the question. We received two digits for the year and we're outputting four.
i have tried a few variation.
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
Posted: Sat Aug 30, 2014 7:27 am
by Celauran
Are you modifying the dates at all before inserting them?
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
That's the same date.