SQL extract string Year YYYY from a Date Time for SSRS

Posted on 2012-09-03
Last Modified: 2012-09-10
Experts I'm developing some SSRS reports for the office and I'm trying to feed a string parameter from my SSRS report @Year (i.e. '2010') into a MS SQL statement to get a resultset of records for i.e. “2010”. “Indate” is the datetime field I need to compare the string parameter data to to get a resultset of just invoices for example '2010'. What functions and syntax should I use? Can you help our here? Thanks in Advance.

SELECT     CustSelCode, ExtPrice, ORep, InDate
FROM         [07Fox]
WHERE     ????? What function and syntax here?
Question by:GetRdone
    LVL 25

    Expert Comment

    convert(varchar, year(InDate)) = @Year

    or better still

    InDate BETWEEN convert(date, '01-01-'+@Year) AND convert(date, '12-31-'+@Year)
    LVL 25

    Accepted Solution

    If InDate has time components ...
    InDate BETWEEN convert(datetime, '01-01-'+@Year) AND convert(datetime, '12-31-'+@Year+' 23:59:59')
    LVL 37

    Expert Comment

    Another method to get the year out of your datetime is by using the DATEPART function:

    WHERE DATEPART(InDate) = @Year

    Open in new window


    Author Closing Comment

    Thanks for the help!
    LVL 37

    Expert Comment

    In case someone stumbles upon this PAQ and notices my previous reply, looks like something went wrong while pasting that code.  This is what it should have been:

    WHERE DATEPART(year, InDate) = @Year 

    Open in new window


    Featured Post

    Courses: Start Training Online With Pros, Today

    Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

    Join & Write a Comment

    In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
    JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
    Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
    Viewers will learn how the fundamental information of how to create a table.

    732 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

    24 Experts available now in Live!

    Get 1:1 Help Now