date range selecting incorrect dates

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

jonnyfortis
Forum Contributor
Posts: 462
Joined: Tue Jan 10, 2012 6:05 am

Re: date range selecting incorrect dates

Post 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
jonnyfortis
Forum Contributor
Posts: 462
Joined: Tue Jan 10, 2012 6:05 am

Re: date range selecting incorrect dates

Post 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?
User avatar
Celauran
Moderator
Posts: 6427
Joined: Tue Nov 09, 2010 2:39 pm
Location: Montreal, Canada

Re: date range selecting incorrect dates

Post 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
jonnyfortis
Forum Contributor
Posts: 462
Joined: Tue Jan 10, 2012 6:05 am

Re: date range selecting incorrect dates

Post 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 ?
jdhmtl
Forum Newbie
Posts: 18
Joined: Tue Aug 12, 2014 7:33 am

Re: date range selecting incorrect dates

Post by jdhmtl »

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

Post 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
User avatar
Celauran
Moderator
Posts: 6427
Joined: Tue Nov 09, 2010 2:39 pm
Location: Montreal, Canada

Re: date range selecting incorrect dates

Post 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.
Post Reply