declare variable with last month's date [yy-mm-01] and [yymm]

Question

Post reply

declare variable with last month's date [yy-mm-01] and [yymm]

mcinvalek

SSC-Addicted

Points: 429
More actions
December 16, 2014 at 12:32 pm

#313090

I am struggling with something rather easy, but I cant seem to get it correct. i have a monthly production script that requires two forms of the prior month's date.
'yyyymm' or '201411' and 'yyyy-mm-dd' or '2014-11-01'. The second date must have a day value of '1'. I have to use these date values in my script a half dozen times. Ive tried to declare two variables to accomodate but i can not get them correct.
I can execute the following:
SELECT LEFT(CONVERT(varchar, DATEADD(MONTH,-1,GetDate()),112),6)
---------------------------
201411
When I try this as a declared variable:
DECLARE @RPT_DTA VARCHAR
SET @RPT_DTA = CONVERT(varchar, DATEADD(MONTH,-1,GetDate()),112)
SELECT @RPT_DTA
--------------------------
2
DECLARE @RPT_DTB VARCHAR
SET @RPT_DTB = DATEPART(YEAR,DATEADD(MONTH,-1,GetDate())) + DATEPART(month,DATEADD(MONTH,-1,GetDate()))
SELECT @RPT_DTB
-----------------------------
*
any and all help is greatly appreciated.
correction** the title should read [yyyy-mm-01] and [yyyymm]

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

imex Ten Centuries Points: 1234 More actions · Answer 1

Try:

DECLARE @RPT_DTA VARCHAR(6)

SET @RPT_DTA = CONVERT(varchar(6), DATEADD(MONTH, -1, GetDate()), 112)

SELECT @RPT_DTA

DECLARE @RPT_DTB VARCHAR(10)

SET @RPT_DTB = CONVERT(VARCHAR(10), DATEADD(MONTH, DATEDIFF(MONTH, 0, GetDate()) - 1, 0), 120);

SELECT @RPT_DTB

or

SET @RPT_DTB = CONVERT(VARCHAR(8), DATEADD(MONTH, -1, GetDate()), 120) + '01';

Hope this helps.

https://www.imoveisemexposicao.com.br/imobiliarias-em-guarulhos

Luis Cazares SSC Guru Points: 183698 More actions · Answer 2

The problem is that you aren't assigning length to your strings. There's no need to use varchar since you're always going to have the same length. Here's an example:

DECLARE @RPT_DTA char(6), @RPT_DTA2 char(10)

SELECT @RPT_DTA = CONVERT(char(6), DATEADD(MONTH,-1,GetDate()),112),

@RPT_DTA2 = CONVERT(char(10), DATEADD(MONTH,DATEDIFF( MONTH, 0, GetDate()) - 1, 0),120)

SELECT @RPT_DTA ,@RPT_DTA2

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Dwain Camps SSC Guru Points: 86908 More actions · Answer 3

Another option:

SELECT CONVERT(VARCHAR(6), d, 112)

,CONVERT(VARCHAR(7), d, 20) + '-01'

FROM (SELECT DATEADD(month, -1, GETDATE())) a (d);

My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]
My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St