CODEWITHSUNDEEP
sqlt-sql
user380432
user380432

Reputation: 4779

SQL Int to Date then 1 year ahead?

I have an int column that stored dates as follows:

201101
201102
201103

I need to take these int's and convert them something like this:

01/2011
02/2011
03/2011

But then in that same column I need to add a year to that date so I want the following column to correspond to the existing

201101 = 01/2011 - 12/2011
201102 = 02/2011 - 01/2012
201103 = 03/2011 - 02/2012

I did that following so far to make it into a date string:

RIGHT(RTRIM([x].[Work_Month]),2) + '/' + LEFT([x].[Work_Month],4) AS PeriodDisplay

I just dont know how I would add a year to that then?

Thanks

Upvotes: 2

Views: 601

Answers (5)

Mikael Eriksson
Mikael Eriksson

Reputation: 138980

declare @T table (wm int)

insert into @T values
(201101),
(201102),
(201103)

select
 right(convert(char(10),dateadd(m,wm%100-1 ,dateadd(yy,wm/100-year(0),0)),103),7) as StartDate,
 right(convert(char(10),dateadd(m,wm%100+10,dateadd(yy,wm/100-year(0),0)),103),7) as EndDate
from @T

Result

StartDate EndDate
--------- -------
01/2011   12/2011
02/2011   01/2012
03/2011   02/2012

Upvotes: 1

Andriy M
Andriy M

Reputation: 77697

Get the end month ready in the same format as the start month, then apply the conversion you've already got to both:

SELECT
  RIGHT(RTRIM(x.WorkMonth1),2) + '/' + LEFT(x.WorkMonth1,4) + ' - ' +
  RIGHT(RTRIM(x.WorkMonth2),2) + '/' + LEFT(x.WorkMonth2,4) AS PeriodDisplay
FROM (
  SELECT
    WorkMonth1 = Work_Month,
    WorkMonth2 = Work_Month + CASE Work_Month % 100 WHEN 1 THEN 11 ELSE 99 END
  FROM atable
) x

Upvotes: 1

Tom Morgan
Tom Morgan

Reputation: 2365

DATEADD(yyyy,1,convert(date,RIGHT(RTRIM([x].[Work_Month]),2) + '/01/' + LEFT([x].[Work_Month],4) ))

Changes I made:

  • added in the /01/ where it was / - in order to set it to be the first of the month, otherwise you can't parse it as a date
  • surrounded it in a convert(date) function to convert it to be a date type. msdn
  • surrounded that in a dateadd function to add one year. msdn

If you actually want the column to contain BOTH dates (so the string would be literally 03/2011 - 02/2012) then just take off the dateadd from the first one. And if you don't want the days, then you could use substring to strip them out. This really is a front-end thing though: SQL Server isn't the ideal tool for doing this.

Upvotes: 1

Thomas
Thomas

Reputation: 64655

Declare @SampleData Table   ( Value int not null )

Insert @SampleData(Value) Values(201101)
Insert @SampleData(Value) Values(201102)
Insert @SampleData(Value) Values(201103)

Select Cast( Cast(Value As char(6)) + '01' As datetime)
    , DateAdd(mm,11,Cast( Cast(Value As char(6)) + '01' As datetime))
From @SampleData

Once you have the values converted to dates, you can use your presentation layer to format the date to appear as MM/yyyy.

Upvotes: 0

MatBailie
MatBailie

Reputation: 86745

My advise would be to make them into dates, sort the logic, the convert them to strings.

As opposed to trying to do it all as strings.

SELECT
  DATEADD(
    MONTH,
    (work_month % 100) - 1),
    DATEADD(
      YEAR,
      (work_month / 100) - 2001,
      '2000-Jan-01'
    )
  )                           AS "Start",
  DATEADD(
    MONTH,
    (work_month % 100) - 1),
    DATEADD(
      YEAR,
      (work_month / 100) - 2000,
      '2000-Jan-01'
    )
  ) - 1                       AS "End",
FROM
  myTable

(Messing with strings is much slower than messing with dates and integers. So this code may look a little longer, but over any significant number of records you may see a noticable performance difference.)

Upvotes: 1

Related Questions