SQL SELECT Previous MMYY as varchar(4)

问题:

Dearest genius StackOverflow friends,

I'm in the need of creating a view that will always give me data in the WHERE clause for "Period" looking for the previous Month and Year (MMYY) in varchar(4) format.

Example: Today is March 3rd, 2015, so what I need is for Period to be 0215.

SELECT stuff
FROM   table
WHERE  period = '0215'

How do I automatically generate the '0215' in the view so I'm always seeing last months data?

Any assistance would be greatly appreciated.

回答1:

Try this expression:

SELECT STUFF(CONVERT(VARCHAR(10), DATEADD(MONTH, -1, GETDATE()), 101), 3, 6, '')

回答2:

Use this:

;WITH PrevMonth AS (
   SELECT LEFT(REPLACE(CONVERT(date, DATEADD(m, -1, getdate())), '-', ''), 6) AS YYYYMM
)
SELECT SUBSTRING(YYYYMM, 5, 2) + SUBSTRING(YYYYMM, 3, 2) AS MMYY 
FROM PrevMonth  

The CTE yields the date of previous month in YYYYMM format. Using SUBSTRING the format is rearranged to produce the required output.

If you are using SQL Server 2012+ it's a lot easier to get the desired result using FORMAT:

SELECT FORMAT(DATEADD(m, -1, getdate()), 'MMyy') AS MMYY

回答3:

you can get what you want using

TO_CHAR(add_months(sysdate,-1), 'MMYY')

of course you can replace sysdate with timestamp, if necessary.