Replacing the maturity day with the origin day

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Replacing the maturity day with the origin day

Author

Topic

cirugio
Yak Posting Veteran

90 Posts

Posted - 2012-06-05 : 15:01:34

I need to code some logic which will create a new date field. Basically, I need to take the month and year from the maturity date and the day from the origindate fields to create this new date field. I was able to use the datepart function along with cast to convert to a date field. This logic worked fine (see exhibit 1), but ran into a syntax error when I had to expand the logic with a case statement (see exhibit 2) to take into account records where the maturity month is feb, apr, june, sept, and nov. For February one can not assign an origin day of 29, 30, or 31 so I tried to default it to make the maturity day to be 28. For Apr, June, Sept, and Nov since the origin day of 31 can not be assigned, I tried to default it to be 30.

Hoping someone can help me figure out how to correct my syntax error (msg 241 - conversion failed when converting date and/or time from character string). Tried everything, but still cant get it to go after a day of working on this. Thank you for your time in advance.

EXHIBIT 1 --- this logic works ----
select origindate,matdate,
CAST( cast(DATEPART(year,matdate) as varchar) + '-' + CAST(DATEPART(month,matdate) as varchar) + '-' +
cast(DATEPART(day,origindate) as varchar) as Datetime ) as 'newdate'
from file1

EXHIBIT 2 --- This logic DOESN'T work. Get syntax error. -----

select origindate,matdate,

CAST (
cast(DATEPART(year,matdate) as varchar) + '-' + CAST(DATEPART(month,matdate) as varchar) +
case
when DATEPART(month,matdate) = 2 and DATEPART(day,origindate) IN (29,30,31) then '28'
when DATEPART(month,matdate) IN (4,6,9,11) and DATEPART(day,origindate) IN (31) then '30'
else cast(DATEPART(day,origindate) as varchar)
end

as datetime) as 'newdate'

from file1

Results Sample
--------------
originDate maturityDate new maturdate
2012-01-15 2012-03-23 2012-03-15
2007-11-26 2017-11-09 2012-11-26
2001-05-31 2012-04-25 2012-04-30 **** need case logic
2012-03-30 2012-02-28 2012-02-28 **** need case logic

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-06-05 : 15:27:44

Will this work for you?

DECLARE @Foo TABLE (originDate DATE, maturityDate DATE)

INSERT @Foo
VALUES
('2012-01-15', '2012-03-23'),
('2007-11-26', '2017-11-09'), 
('2001-05-31', '2012-04-25'),
('2012-03-30', '2012-02-28')

SELECT DATEADD(MONTH, DATEDIFF(MONTH, 0, maturityDate), (DAY(originDate) - 1))
FROM @Foo

Also is the last row in your sample results correct? Shouldn't it be 2/29?

cirugio
Yak Posting Veteran

90 Posts

Posted - 2012-06-05 : 15:36:57

Thank you for your response, but actually the logic you provided will not help. The sample data I provided is just a sample of the data I am reading in. I don't need to insert it into a table. Just need to create a report which will display the new maturity field. I just need to figure out how to get the syntax error corrected in the case logic I provided in my Exhibit 2.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-06-05 : 16:06:42

quote:
Originally posted by cirugio

Thank you for your response, but actually the logic you provided will not help. <snip>

I'm not sure I understand. I converted a convoluted date to string to date manipulation case expression and turned it into a simple date manipulation algorithm.

Here is a fix for your string manipulation:

select origindate,matdate,

CAST ( 
cast(DATEPART(year,matdate) as varchar) + '-' + CAST(DATEPART(month,matdate) as varchar) + '-' +
case
when DATEPART(month,matdate) = 2 and DATEPART(day,origindate) IN (29,30,31) then '28' 
when DATEPART(month,matdate) IN (4,6,9,11) and DATEPART(day,origindate) IN (31) then '30' 
else cast(DATEPART(day,origindate) as varchar)
end 

as datetime) as 'newdate'

from file1

vijays3
Constraint Violating Yak Guru

354 Posts

Posted - 2012-06-05 : 17:15:00

[code]

why do you want to convert varchar data into datetime when you
can get your result by firing below query. I have removed extra cast function.Hope this helps.

select origindate,matdate,
cast(DATEPART(year,matdate) as varchar) + '-' + CAST(DATEPART(month,matdate) as varchar) +
case
when DATEPART(month,matdate) = 2 and DATEPART(day,origindate) IN (29,30,31) then '28'
when DATEPART(month,matdate) IN (4,6,9,11) and DATEPART(day,origindate) IN (31) then '30'
else cast(DATEPART(day,origindate) as varchar)
end

as 'newdate'

[/code]

Vijay is here to learn something from you guys.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-06-05 : 19:07:27

wont this be enough?


SELECT CASE WHEN MONTH(ConvertedDate) <> MONTH(maturityDate) THEN DATEADD(mm,DATEDIFF(mm,0,ConvertedDate),0)-1 ELSE ConvertedDate END AS MaturityDate
FROM
(
SELECT maturityDate,DATEADD(dd,(-1 * DAY(maturityDate)) + DAY(originDate),maturityDate) AS ConvertedDate
FROM table
)t

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-06-06 : 10:11:09

quote:

Originally posted by visakh16

wont this be enough?


SELECT CASE WHEN MONTH(ConvertedDate) <> MONTH(maturityDate) THEN DATEADD(mm,DATEDIFF(mm,0,ConvertedDate),0)-1 ELSE ConvertedDate END AS MaturityDate
FROM
(
SELECT maturityDate,DATEADD(dd,(-1 * DAY(maturityDate)) + DAY(originDate),maturityDate) AS ConvertedDate
FROM table
)t

That produces the same output as my solution. Apparently, the OP has specific business rules about when the end of the month should be that doesn't align to the actual end of the month. Hence, the max day for Feb is 28.

cirugio
Yak Posting Veteran

90 Posts

Posted - 2012-06-06 : 23:31:20

Thanks to all the responses, I was able to get it to go. I really must say I love this sight and grateful for all the members and their knowledge. Thanks again!!!

vijays3
Constraint Violating Yak Guru

354 Posts

Posted - 2012-06-07 : 07:50:54

How did you solve it?

Vijay is here to learn something from you guys.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-06-07 : 22:26:14

quote:
Originally posted by cirugio

Thanks to all the responses, I was able to get it to go. I really must say I love this sight and grateful for all the members and their knowledge. Thanks again!!!

welcome
but it would be really helpful for all if you could post your final solution

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

cirugio
Yak Posting Veteran

90 Posts

Posted - 2012-06-11 : 13:08:10

As much as I would have liked to have used Visakh16 recommendation, since it was a cleaner way of doing it, I ended up using my original code with Vijalys3's correction.

I really wanted to utilize Visakh16 code, but I couldn't get the code to work when I incorporated it into a update/where statement (see attached). Kept getting an error on a ',' somewhere within the select statement.

Because of the time constraints I had to use my original. I would like to go back and use Visakh16, if someone can assist me in how to get the syntax corrected. thanks again.

update table
set w_maturity_date =

CASE WHEN MONTH(ConvertedDate) <> MONTH(maturityDate)
THEN DATEADD(mm,DATEDIFF(mm,0,ConvertedDate),0)-1
ELSE ConvertedDate END
FROM
(SELECT maturityDate,DATEADD(dd,(-1 * DAY(maturityDate)) + DAY(originDate),maturityDate) AS ConvertedDate
FROM table
)

where product in ('cds','deposits')

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-06-11 : 15:10:32

[code]
update t
set w_maturity_date =
CASE WHEN MONTH(ConvertedDate) <> MONTH(maturityDate)
THEN DATEADD(mm,DATEDIFF(mm,0,ConvertedDate),0)-1
ELSE ConvertedDate END
FROM
(SELECT maturityDate,DATEADD(dd,(-1 * DAY(maturityDate)) + DAY(originDate),maturityDate) AS ConvertedDate
FROM table
where product in ('cds','deposits')
) t
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-06-11 : 18:14:07

[code]UPDATE table
SET w_maturity_date = DATEADD(MONTH, DATEDIFF(MONTH, 0, maturityDate), (DAY(originDate) - 1))
where product in ('cds','deposits')[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources