| Author |
 Topic  |
|
|
cirugio
Yak Posting Veteran
88 Posts |
 Posted - 06/05/2012 : 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
Flowing Fount of Yak Knowledge
3828 Posts |
Posted - 06/05/2012 : 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
88 Posts |
Posted - 06/05/2012 : 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. |
Edited by - cirugio on 06/05/2012 15:39:06 |
|
|
|
Lamprey
Flowing Fount of Yak Knowledge
3828 Posts |
Posted - 06/05/2012 : 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
India
311 Posts |
Posted - 06/05/2012 : 17:15:00
|
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'
Vijay is here to learn something from you guys. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47069 Posts |
Posted - 06/05/2012 : 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
Flowing Fount of Yak Knowledge
3828 Posts |
Posted - 06/06/2012 : 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
88 Posts |
Posted - 06/06/2012 : 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
India
311 Posts |
Posted - 06/07/2012 : 07:50:54
|
How did you solve it?
Vijay is here to learn something from you guys. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47069 Posts |
Posted - 06/07/2012 : 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
88 Posts |
Posted - 06/11/2012 : 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
India
47069 Posts |
Posted - 06/11/2012 : 15:10:32
|
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
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
Lamprey
Flowing Fount of Yak Knowledge
3828 Posts |
Posted - 06/11/2012 : 18:14:07
|
UPDATE table
SET w_maturity_date = DATEADD(MONTH, DATEDIFF(MONTH, 0, maturityDate), (DAY(originDate) - 1))
where product in ('cds','deposits') |
|
|
| |
Topic |
|
|
|
Replacing the maturity day with the origin day
