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.