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

Question

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.

What language are you working with? Because you defined the format as YYYYMM that makes me believe you are getting that from somewhere and couldn't just change the format manually, is this right? — Matthew Brown, Oct 15 '14 at 23:23
TM1 as in Cognos TM1? I've added that as a tag. If the database field is indexed, you must ensure you don't wrap any functions around the database column as it stops an index being used. I guess the querying is occurring in Turbo Integrator so you are constrained by that tool? — Nick.Mc, Oct 15 '14 at 23:57

score 1 · Answer 1 · edited Nov 13 '18 at 23:23

1

Suppose you are getting this value of YYYYMM in a varchar variable @datefrom .

You can do something like

DECLARE @DateFrom VARCHAR(6) = '201201';

-- Append '01' to any passed string and it will get all 
-- records starting from that month in that year

DECLARE @Date VARCHAR(8) = @DateFrom + '01'

-- in your query do something like 

SELECT * FROM TableName WHERE DateTimeColumn >= @Date

Passing Datetime in a ansi-standard format i.e YYYYMMDD is a sargable expression and allows sql server to take advantage of indexes defined on that datetime column.

here is an article written by Rob Farley about SARGable functions in SQL Server.

edited Nov 13 '18 at 23:23

Community

1
1

answered Oct 15 '14 at 23:31

M.Ali

67,945
13
101
127

Thanks for your responses. My bad - adding more information now. I need to get it done in a sql statement that would be hooked up in TM1, so would not be able to write a procedure or declare variables (TM1 somehow does not like it). Also, as I mentioned , I need to run the same for a complete month. So, let's say if the input is '201312', I would need convert the same as 'between 01-12-2013 and 31-12-2013' – IT Guy Oct 16 '14 at 00:15

score 1 · Accepted Answer · answered Oct 15 '14 at 23:48

1

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

answered Oct 15 '14 at 23:48

Nicholas Carey

71,308
16
93
135

Thanks for your response. My bad - adding more information now. I need to get it done in a sql statement that would be hooked up in TM1, so would not be able to write a procedure or declare variables. Also, as I mentioned , I need to run the same for a complete month. So, let's say if the input is '201312', I would need convert the same as 'between 01-12-2013 and 31-12-2013' – IT Guy Oct 16 '14 at 00:14
@ITGuy: Does it actually have to be a single statement? You could try using the body of this procedure as a client script. In any event, I believe this answer shows how to use an input value formatted as YYYYMM as a date interval – i.e. what you are actually asking. – Andriy M Oct 16 '14 at 00:39
@AndriyM: Thanks, it does portray what needs to be achieved. However, I am not sure in how to incorporate the same with TM1. There are a number of restrictions in the environment that we are working. so invoking a proc or a client script is not a viable option. Hence, I am looking for something that can be done in single statement. In the end, we might end up doing some tweak within TM1 and leave the query as it is. – IT Guy Oct 16 '14 at 00:56
@ITGuy: Well, you could take just the SELECT statement in this SP and replace the variables in the WHERE clause with corresponding expressions, something like this: `t.date_of_record >= @yyyymm+'01' and t.date_of_record < dateadd(month,1,@yyyymm+'01')` (you could also add explicit conversion to `date`, as this answer suggests, but that is not really necessary in this case IMHO). Not sure how you specify an argument (`@yyyymm`) in TM1, though. – Andriy M Oct 16 '14 at 01:08
@AndriyM: I just did the same and it worked :-) Thanks for the help. I am not sure if the DBA likes it though. select convert(datetime,'201304'+'01') as 'Start Date' select dateadd(SECOND,-1,dateadd(month,1,convert(datetime,'201305'+'01'))) as 'End Date' – IT Guy Oct 16 '14 at 01:24

score 0 · Answer 3 · answered Oct 16 '14 at 01:43

Try this...

declare @startdate date,@endate date

select @startdate =convert(date,left('201312',4)+'-'+right('201312',2)+'-01')

select @endate= DATEADD(d, -1, DATEADD(m, DATEDIFF(m, 0, @startdate) + 1, 0))

select convert(date,@startdate,102) startdate,convert(date,@endate,102) endate

score 0 · Answer 4 · answered Nov 23 '14 at 06:14

In the datasource of your TM1 Turbo Integrator process, you can use parameters in the SQL query. E.g. you could take this SQL query:

SELECT Col1, Col2
FROM Table
WHERE Col1 = 'Green'
AND Col2 < 30

In TM1, to parameterise this, you would create two parameters e.g. P1 and P2 and put them in the query:

SELECT Col1, Col2
FROM Table
WHERE Col1 = '?P1?'
AND Col2 < ?P2?

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

4 Answers4