SQL Select from dates with dynamic year. Get current year

Posted on 2009-05-01
Last Modified: 2012-05-06
I have a table in SQL with a column called CompletedDate.

I need to select data from the table where CompletedDate is in this current financial year i.e. 1/4/2009 - 31/03/2010. But i cannot fix the year as this will be put into a stored procedure. So i need to be able to get the current year.

So its like

SELECT COUNT(*) FROM Completed WHERE CompletedDate is between '01/04' & [THE CURRENT YEAR] AND '31/03/' [NEXT YEAR].

How can i do this?

Question by:AVONFRS
    1 Comment
    LVL 142

    Accepted Solution

    this will do:
    SELECT COUNT(*) FROM Completed 
    WHERE CompletedDate >= CONVRET(datetime, '01/04/' + CAST(YEAR(GETDATE()) as varchar(4)), 103) 
      AND CompletedDate < DATEADD(year,1, CONVRET(datetime, '01/04/' + CAST(YEAR(GETDATE()) as varchar(4)), 103))

    Open in new window


    Featured Post

    IT, Stop Being Called Into Every Meeting

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Join & Write a Comment

    Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
    The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
    Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
    Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

    734 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    23 Experts available now in Live!

    Get 1:1 Help Now