Trouble With Dates, Variables, and Update

[CptTom]

I need to update a field (True_Poss) to reflect the number of days from the 15th of last month to the date in the field called (Dte_Reported). Here is what I am trying, but I get an Incorrect syntax near '=' error. I have tried variations of Set tablename.true_poss, etc. same results.

Any ideas?


Declare @Mmonth char(2)
Declare @MYear Int
Declare @MDate char(4)
Declare @MCurDate SmallDatetime
Declare @MLastDate SmallDatetime
Declare @SQLString varchar(200)
SET @MDate = '/15/'
BEGIN
UPDATE dbo.XMF_NMC
SET @MCurDate = dbo.XMF_NMC.Dte_Reported
SET @MYear = Year(Dte_Reported - 30)
SET @Mmonth = Month(Dte_Reported - 30)
SET @MLastDate = (Mmonth + MDate + MYear)
SET True_Poss = (DATEDIFF(day, @MLastDate, @MCurDate) )
END

Larry

 

[grahams]

I suspect these are meant to be your variable names:

SET @MLastDate = (Mmonth + MDate + MYear)

but you've missed your @



Graham

 

[CptTom]

That must have dropped off inadvertently, but I still get the error. I have been fooling with this think half the morning.

Larry

 

[sunila7]

Hi,

change ur update statement like this....

UPDATE dbo.XMF_NMC
SET @MCurDate = dbo.XMF_NMC.Dte_Reported
, @MYear = Year(Dte_Reported - 30)
, @Mmonth = Month(Dte_Reported - 30)
, @MLastDate = (Mmonth + MDate + MYear)
, True_Poss = (DATEDIFF(day, @MLastDate, @MCurDate) )


Sunil

 

[grahams]

declare @PrevDate varchar(10)
declare @PrevMonth int, @PrevYear int

set @PrevMonth = dateadd(m, -1, dbo.XMF_NMC.Dte_Reported)
set @PrevYear = datepart(yyyy, dbo.XMF_NMC.Dte_Reported)

set @PrevDate = '15/' + convert(varchar(2), @PrevMonth) + '/' + convert(varchar(4), @PrevYear)

UPDATE dbo.XMF_NMC
SET True_Poss = DATEDIFF(d, Convert(datetime, @PrevDate, 120), dbo.XMF_NMC.Dte_Reported)

(I've kept it all separated out for clarity, but you could condense it somewhat.

HTH,

Graham

 

[JamesLean]

Graham, there are a couple of errors in your code. Try this:

DECLARE @prev datetime
DECLARE @prev15 varchar(8)

SET @prev = DATEADD(mm, -1, getdate())

SET @prev15 = CONVERT(varchar(4), YEAR(@prev)) + RIGHT('0' + CONVERT(varchar(2), MONTH(@prev)), 2) + '15'

UPDATE xmf_nmc
SET true_poss = DATEDIFF(dd, CONVERT(datetime, @prev15), dte_reported)

--James

 

[grahams]

Oh - I couldn't test it, but where did I go wrong? My understanding was that CptTom wanted the 15th day of the month prior to the date in dte_reported (rather than from today).
Have I done anything else obviously wrong? Am I going mad/blind? ;-)

 

[JamesLean]

If that's the case then mine won't work either and I misunderstood the question ;-)

Anyhow, your syntax wouldn't work for that either:

1)You've declared @prevmonth as int but then tried to set it equal to a datetime value (returned by the DATEADD function).

2)You can't use the column name in those first DATEADD and DATEPART statements the way you have tried to. --James

 

[grahams]

Ye gods, you're right. Time to go home for a lie down I think. Did I say something somewhere about my brain still working? Sheesh.....

 

[sunila7]

Oops..... I thought cptom wanted to fix the synstax error... good one grahams....

Sunil

 

[JamesLean]

I thought I'd have a go at this, should you want to get the days between the 15th of the month previous to dte_reported and dte_reported (rather than the month previous to the current month). I hope I've managed to match up all the brackets!!

UPDATE xmf_nmc
SET true_poss = DATEDIFF(dd, CONVERT(datetime, CONVERT(varchar(4), YEAR(DATEADD(mm, -1, dte_reported))) + RIGHT('0' + CONVERT(varchar(2), MONTH(DATEADD(mm, -1, dte_reported)))) + '15'), dte_reported)

--James

 

[CptTom]

This is what I changed and it works. Thanks! Now, how do I call it in the DTS Process? I have one that runs the import and I want this to run upon completion.

UPDATE dbo.XMF_NMC
SET @MCurDate = dbo.XMF_NMC.Dte_Reported
, @MYear = Year(Dte_Reported - 30)
, @Mmonth = Month(Dte_Reported - 30)
, @SQLString = (@Mmonth + @MDate + @MYear)
, @MLastDate = (@SQLString)
, True_Poss = (DATEDIFF(day, @MLastDate, @MCurDate) )

 

[JamesLean]

Use an Execute SQL task to run your statement. --James

 

[CptTom]

Thanks! Everything works the way it shoudl!