Converting YYYYMM format to YYYY-MM-DD in SQL Server

I need to perform a query on a large table that has a datetime column that is indexed. We need to query the data for a range from a month (at a minimum) to multiple months.

This query would be executed from Cognos TM1 and the input would be a period like YYYYMM. My question is - how to convert the YYYYMM input to a format that can be used to query that table (with the index being used).

Let's say if the input is

From Date: '201312'
To Date: '201312'

then, we need convert the same to 'between 01-12-2013 and 31-12-2013' in the query

Since we need this to be hooked up in Cognos TM1, so would not be able to write a procedure or declare variables (TM1 somehow does not like it).

Thanks in advance for your reply.

Solution

I would do something like this:

create procedure dbo.getDataForMonth

  @yyyymm char(6) = null

as

  --
  -- use the current year/month if the year or month is invalid was omitted
  -- 
  set @yyyymm = case coalesce(@yyyymm,'')
                when '' then convert(char(6),current_timestamp,112)
                else         @yyyymm
                end

  --
  -- this should throw an exception if the date is invalid
  --
  declare @dtFrom date = convert(date,@yyyymm+'01') -- 1st of specified month
  declare @dtThru date = dateadd(month,1,@dtFrom)   -- 1st of next month

  --
  -- your Big Ugly Query Here
  --
  select *
  from dbo.some_table t
  where t.date_of_record >= @dtFrom
    and t.date_of_record < @dtThru

  --
  -- That's about all there is to it.
  --
  return 0
go