date question 1

[room24]

i trying to update the year of one date with the year of another date

take the example below

declare @time datetime

select @time = '11/08/2003 11:59'

select (datepart(yy,@time)) = datepart(yy,getdate())

this is not working out its not even compiling

 

[SQLSister]

You are using a select statment not an update. Before we can help you we need to know are you updating the dataset or just displaying a different year in a query?

Questions about posting. See faq183-874

 

[room24]

this what i am trying to achieve
i want to update the year of maturity_date with the year of @date_value


update #independent_investment_certificate

set datepart(yy,#independent_investment_certificate.maturity_date) = datepart(yy,@date_value) + 1



from #ticker_interest_date, #independent_investment_certificate

where #ticker_interest_date.ticker_key = @ticker_value
and #independent_investment_certificate.issue_date = @date_value
and #independent_investment_certificate.ticker_key = @ticker_value
and #independent_investment_certificate.maturity_date is null



but it will not compile

 

[dbomrrsm]

dont think you can do that - you are trying to update only part of a field i.e. the year in '11/08/2003 11:59'.

you could do something like

SET maturity_date = datepart(dd,#independent_investment_certificate.maturity_date)+
datepart(mm,#independent_investment_certificate.maturity_date)+
datepart(yy,@date_value)

Not tested but a start I hope !


[blue]DBomrrsm[/blue]

 

[SQLSister]

Ok what you need to do here is only put the column name in the left part of the set, not the datepart statment. Then rebuild the whole string onthe right side using threedate part statments and of course strings to get the / and concatenate them all together.

Questions about posting. See faq183-874

 

[room24]

i did this and its giving me "1905-07-17 00:00:00.000" which is the wrong output

declare @date_value datetime

drop table #independent_investment_certificate
create table #independent_investment_certificate
(
maturity_date datetime
)

insert #independent_investment_certificate

select '11/08/2003 11:59'

select @date_value = getdate()

update #independent_investment_certificate

set maturity_date = datepart(dd,maturity_date) +
datepart(mm,maturity_date) +
datepart(yy, @date_value)

select * from #independent_investment_certificate

 

[donutman]

Here's a way to do it, but you have to accept the problem of leap years.

Declare @D1 as datetime
Declare @D2 as datetime
SET @D1='2004-02-29'
SET @D2='2003-05-01'
SELECT DateAdd(dd,DAY(@D1)-1,DateAdd(m,MONTH(@D1)-1,DateAdd(yy,DateDiff(yy,0,@D2),0)))

@D1 in this case is your @date_value
@D2 is your maturity date
The SELECT computes the new maturity date.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[room24]

i think the problem is that the + is actually adding the dates instead of concatenating then

 

[donutman]

No, there's no concatenating going on. The problem with leap years is illustrated in the example I provided...how do you tag an arbitrary year onto 2/29? My solution changes the 2/29 to 3/1.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[SQLSister]

I figured it out, you have to explicitly convert to varchar for it to concatenate. See example below:

SET Datefield = convert(varchar(2),datepart(mm,Duedate))+ '/' + Convert(varchar(2),datepart(dd, DueDate)) 
+ '/' + Convert (varchar (4), datepart(yy, getdate()))

Questions about posting. See faq183-874

 

[room24]

thanks SQLSister that worked!!!

 

[donutman]

Except when DueDate is 2/29/x...it crashes if DateField is datetime datatype.

 

[SQLSister]

Yep you are right, you will need a case statement to handle that. Yuck. although I might be tempted to just write two updates, one for most dates and one for the 2/29 days.

Questions about posting. See faq183-874