| Author |
Topic |
|
meef
Posting Yak Master
113 Posts |
 Posted - 2012-01-19 : 15:45:45
|
| Right now I have a column called PerSh (period shipped) that displays yy/mm format (like this month would be "1201". How can I convert that back to a date so I can add 570 days to it? |
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2012-01-19 : 15:59:48
|
If the column is integer type, do this:CAST(CAST(PerSh*100+20000001 AS VARCHAR(8)) AS DATETIME); If it is character:CAST('20'+PerSh+'01' AS DATETIME); |
 |
|
|
meef
Posting Yak Master
113 Posts |
Posted - 2012-01-23 : 08:33:24
|
| After I do that, would I just do DATEADD(day, 550, PerSh) as PerSh? I actually just tried that and it says it resulted in an out of range datetime value.So I basically have to convert it to DATETIME, add 570 days to it, then convert it back? |
|
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2012-01-23 : 09:20:42
|
quote:
Originally posted by meef
After I do that, would I just do DATEADD(day, 550, PerSh) as PerSh? I actually just tried that and it says it resulted in an out of range datetime value.So I basically have to convert it to DATETIME, add 570 days to it, then convert it back?
You would need to replace every occurrence of Persh with the expression to convert it to datetime. So, for example, to add 570 days,DATEADD(day, 550, CAST(CAST(PerSh*100+20000001 AS VARCHAR(8)) AS DATETIME)) If that does not seem to behave the way you are expecting it to, can you post your query? |
|
|
|
meef
Posting Yak Master
113 Posts |
Posted - 2012-01-23 : 12:00:34
|
| That did add 570 days, but after that's done I need it to be displayed the way it was before, i.e. 0712 (year then month)... |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2012-01-23 : 14:14:28
|
Use convert function http://msdn.microsoft.com/en-us/library/ms187928.aspx But, I am really with Brett on this one. You are taking a number, converting it to date, adding 570 days, converting it back to a number - seems like there HAS to be a better way. May be doing some of the stuff at the front end (if you have one) may be the answer?LEFT(CONVERT(VARCHAR(6),DATEADD(day, 550, CAST(CAST(PerSh*100+20000001 AS VARCHAR(8)) AS DATETIME)),12),4) |
|
|
|
meef
Posting Yak Master
113 Posts |
Posted - 2012-01-24 : 08:34:36
|
| I would agree there should be a better way, but I can't think of it. I have a bunch of reports that are using this PerSh with the demo data, and I have to make the data look somewhat "fresh", hence the adding 570 days. To do it via the reporting system seems even more counter productive, because I've going to have to do that in each of the reports. |
|
|
|
|
Converting varchar to datetime so I can DATEADD()