How to get an year ago date from the particular date

  • I have a list of students who have taken a test on different dates.

    I need a function to calculate year ago date from the date of test.

    eg Sam took the test on feb 23 2015

    I need to get a feb 23 2014 to see what subjects he studied from feb 23 2014 to feb 23 2015.

    How can I do that.

    Thanks,

    Blyzzard

  • Probably easiest to use thedateadd function

    select * from table where user='sam' and (testdate between dateadd(y,-1,[testdate]) and testdate)

  • how can I query this for all the students.

    I have testdate and the yearago date from the testdate with studentid and subject usinfg this query :

    select Studentid, Lname,Fname,testdate, subject , ADD_MONTHS( testdate, -12 ) yearago from teststd

    But I want to write a query to select out of these students, students who have taken another etst between testdate and yearago date.

    I was trying to left join this query with itself but not getting any result.

  • amar_kaur16 (5/18/2015)


    how can I query this for all the students.

    I have testdate and the yearago date from the testdate with studentid and subject usinfg this query :

    select Studentid, Lname,Fname,testdate, subject , ADD_MONTHS( testdate, -12 ) yearago from teststd

    But I want to write a query to select out of these students, students who have taken another etst between testdate and yearago date.

    I was trying to left join this query with itself but not getting any result.

    Well, since we can't see what you see, may I suggest that you read the first article I reference below in my signature block. It will walk you step by step regarding what you should post and how to post it.

    The benefit, tested code in return for your extra effort.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply