VB.net SQL statement to query 1 yr old Timestamp

Question

I want to display in my gridview all data whose timestamp is not older than 1 years from current date.

My timestamp is formatted like so: 20110125-071830 or yyyymmdd-hhmmss

I have tried to reference :

Retrieve rows less than a day old:

Select *  from table_name WHERE DATE(timestampVal) > DATE(NOW() - INTERVAL 1 YEAR);

but got missing expression flag

I also tried various others; however, my timestampVal is different simply by how it is formatted.

Like for example: https://community.oracle.com/thread/2207956

With coding: Select * from table_name WHERE timestampVal < sysdate - interval '1' year ;

but get error: literal does not match format string which means sysdate can't read how mine is formatted.

How do I query my timestamp to pull all that are a year or less old?

FYI: timestampVal is string type [varchar]

Answer 1

It looks like the following should work:

Select *
  from table_name
  WHERE TO_DATE(timestampVal, 'YYYYMMDD-HH24MISS') > sysdate - interval '1' year;

Also note that I reversed the comparison: you indicated that you want rows where timestampVal is not older than 1 year ago - so timestampVal should be greater (newer) than current time minus one year.

Give that a try.

Best of luck.