I have a database of asset information with asset age as one of the columns..now i wish to display all the assets which crossed 4 years in the current year ..any suggestions will be appreciated!
Depending on the format of the age column, you can limit the results by checking to see if the age is equal to or greater than the current year - 4.
Ex if column is DateTime/SmallDateTime datatype:
SELECT * FROM <table> WHERE YEAR(<asset age column>) >= 2009
To make it more general you could replace
this query will show even the assets which are 5 0r 6 year old i.e. the assets which crossed 4 years two years or 3 years back
I just want those assets which just crossed 4 years(in the current year)
The how about
SELECT * FROM <table> WHERE DATEPART("YEAR",GETDATE()) - YEAR(<asset age column>) = 3
I have a <PO date> column i.e. purchase order date
I tried the query
select * from table where '"&datediff("yyyy",now(),PO date)&"'=3 but it wont work
actually,I am not able to use PO date column in the function
it is a datetimepicker one in vb and date/time type in access database
SELECT * FROM table WHERE DateDiff(year, Now(), '" & <po date datetimepicker>.Value & " 00:00:00.0000000') = 3".
What kind of database is it?? Access, MSSQL or MYSQL?
I derived a query in Access
hope ur colname is a datetime field
SELECT * FROM tablename WHERE (format(colname,"yyyy")<Year(Now())-4);
I chose to use the query for listing assets whose asset age is 4 as I am already using a query for calculating age from the purchase till now.