3

I have a bunch of observations that are in M/d/yyyy format. I would like to create a separate attribute column where each observation is listed by year only. But I can't just select columns by year, as I tried to do by creating an expression in my screenshot.

Is there a way to do this?

enter image description here

  • 2
    What QGIS version? What field type is observed_on? – Stu Smith Nov 25 '20 at 17:03
8

Use the field calculator to create a new field, then define the expression using the right() function to extract just the year from your "observed_on" field.

In this case you will need 4 characters.

right("observed_on", 4)
7

First you need to convert your string ('mm-dd-yy') to a date (yyyy-MM-dd). Then you can extract the year with year() function.

All in one: year(make_date(20||substr("obvserved_on",7,2),substr("obvserved_on",1,2),substr("obvserved_on",4,2)))

Note that you need to add 20 (or 19 or whatever) in front of your year (the 20|| part) since it is formatted with only two numbers instead of four.

Edit: as I can see in your Screenshot, your Datestring is not formatted as 'mm-dd-yy', but actually 'M/d/yyyy'. You can use regular expressions or arrays in that case. Like this: year(make_date(array_get( string_to_array( "observed_on",'/'),2),array_get( string_to_array( "observed_on",'/'),0),array_get( string_to_array( "observed_on",'/'),1)))

You can create a new field by opening your attribute table and executing field calculator. Enter your expression there. See:

enter image description here

  • Thanks, and where do I enter this code? Do I just plug it into the expression in the field calculator? – DanG Nov 25 '20 at 17:31
  • One can format the date directly: year(to_date(obvserved_on,'M-d-yyyy')) – JGH Nov 25 '20 at 17:47
  • @JGH true that. Seems like I was stuck in my brain on the year having only two numbers, which does not allow to directly format it (see my note). You should add this as another answer. – MrXsquared Nov 25 '20 at 17:49
  • :-) yy should work. – JGH Nov 25 '20 at 17:52
  • 1
    @JGH this returns 1900 instead of 2000 by default. – MrXsquared Nov 25 '20 at 18:11
3

Simplifying @MrXsquared's answer, one can directly format the recorded date:

year(to_date(obvserved_on,'M/d/yyyy'))

with the doc saying:

M The month as a number without a leading zero (1 to 12)
d The day as a number without a leading zero (1 to 31)
yyyy The year as a four digit number, possibly plus a leading minus sign for negative years.
All other input characters will be treated as text.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.