[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 378
  • Last Modified:

YrMo Sql

I have created the below query to capture the 1st day of the month for the data that i have archived.  

sELECT Distinct CAST(CAST(YEAR(Archived) AS VARCHAR(4)) + RIGHT('0' + CAST(DATEPART(MM, Archived) AS VARCHAR(12)),2) AS INT) as 'ArchivedYrMo' FROM [RCSC].[dbo].[IAQ_Archive] I
ORDER BY I.Archived DESC

ArchivedYrMo

201311
201310
201309
201308
201307
201306


However I also need to create a query pull the previous month.  I would need the results to pull 201310 if i were running the query today and if i was to run the query in Jan  the results would need to be 201312.

Any takers
0
Thomask23
Asked:
Thomask23
  • 2
1 Solution
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>I have created the below query to capture the 1st day of the month
>201311 .. 201310
The above returns year-months, and not days.

If it helps, I have an article out there titled Date Fun, Part One:  Build your own SQL calendar table to perform complex date expressions that is a T-SQL demo on how to build your own calendar table, that can answer questions like 'what is the previous month' very easy.
0
 
Scott PletcherSenior DBACommented:
SELECT Distinct
    CAST(CONVERT(char(6), Archived, 112) AS int) AS 'ArchivedYrMo'
FROM [RCSC].[dbo].[IAQ_Archive] I
WHERE
    Archived >= DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) - 1, 0) AND
    Archived < DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0)
0
 
Scott PletcherSenior DBACommented:
If you want to return the first of a month, the calc is:

DATEADD(MONTH, DATEDIFF(MONTH, 0, <date>, 0)

There is no I/O and no string conversion, so it's almost no overhead and lightening quick.

As an example, the last three months, grouped by month:

SELECT
    DATEADD(MONTH, DATEDIFF(MONTH, 0, Archived, 0) AS ArchivedYrMo,
    SUM(...),
    COUNT(...)
FROM [RCSC].[dbo].[IAQ_Archive] I
WHERE
    Archived >= DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) - 3, 0) AND
    Archived < DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0)
GROUP BY
    DATEADD(MONTH, DATEDIFF(MONTH, 0, Archived, 0)
0

Featured Post

[Webinar] Improve your customer journey

A positive customer journey is important in attracting and retaining business. To improve this experience, you can use Google Maps APIs to increase checkout conversions, boost user engagement, and optimize order fulfillment. Learn how in this webinar presented by Dito.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now