Page 1 of 1

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

Posted: Tue Aug 30, 2016 3:36 am
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?

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

Posted: Tue Aug 30, 2016 3:41 am
by simonmlewis

Code: Select all

UPDATE products SET datefield= '2030-01-01' WHERE LEFT(datefield, 4) <> '0000' 
Does this look correct??

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

Posted: Tue Aug 30, 2016 6:41 am
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?

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

Posted: Tue Aug 30, 2016 6:51 am
by Celauran
Rather than doing a string compare, I'd recommend using the YEAR function

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

Posted: Tue Aug 30, 2016 7:18 am
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.