How do I update forst 4 charcs, where first four <> 0000?

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

How do I update forst 4 charcs, where first four <> 0000?

Post by simonmlewis »

I need to set a date field to be way into the future.

So if the field is not 0000-00-00, I was to set the first four "0000" to be "2030".....

How do I do that?
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

Re: How do I update forst 4 charcs, where first four <> 0000

Post by simonmlewis »

Code: Select all

UPDATE products SET datefield= '2030-01-01' WHERE LEFT(datefield, 4) <> '0000' 
Does this look correct??
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
User avatar
Celauran
Moderator
Posts: 6427
Joined: Tue Nov 09, 2010 2:39 pm
Location: Montreal, Canada

Re: How do I update forst 4 charcs, where first four <> 0000

Post by Celauran »

simonmlewis wrote:

Code: Select all

UPDATE products SET datefield= '2030-01-01' WHERE LEFT(datefield, 4) <> '0000' 
Does this look correct??
It's trivial to test. Have you tried?
User avatar
Celauran
Moderator
Posts: 6427
Joined: Tue Nov 09, 2010 2:39 pm
Location: Montreal, Canada

Re: How do I update forst 4 charcs, where first four <> 0000

Post by Celauran »

Rather than doing a string compare, I'd recommend using the YEAR function
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

Re: How do I update forst 4 charcs, where first four <> 0000

Post by simonmlewis »

I have. It seemed to work. Just wondered if it was the appropriate method.

Not seem that function before, but I get technically why that would be better To adjust the year on a date field, rather than the entire string.
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
Post Reply