You'd do something like this:

updatec

setchkdesc=right('0'+rtrim(convert(char,datepart(mm,trandate))),2) + right('0'+rtrim(convert(char,datepart(dd,trandate))),2) + right('0'+rtrim(convert(char,datepart(yyyy,trandate))),2)

FROM

cmledge AS c

JOIN

<periodTable> AS pt

ONc.period = pt.period

wherechkdesc ='0000000'

Note there's some psuedocode in there so you'll have to edit it a bit...

You can do it with a for xml structure.

Check out this article:

http://www.sqlservercentral.com/articles/FOR+XML+PATH/70203/

Any chance you can post the sample data and DDL like in the first link of my sig? Will make life much easier.

venoym (12/8/2010)

---

Great post Jeff!

I had a thought... this could be extended to account for Times in addition to dates... just move the calculation to the lowest significant field (from day...

Question is a duplicate, please direct all commentary and responses to the following post:

http://www.sqlservercentral.com/Forums/Topic1031483-392-1.aspx

LutzM (12/7/2010)

---

Craig Farrell (12/7/2010)

---

...

One of the nice side effects of constantly being a joker is you can get away with a few comments others would be slammed for.

Nope, I...

michael.horn (12/7/2010)

---

That gives me the last rows that either have no date in them or dates someone entered in 1900. The data and dates are entered by a machine...

No worries. I have my humor bat handy. One of...

That was for Drew, Doug, since he specifically asked. No worries. ...

Alvin Ramard (12/7/2010)

---

Craig Farrell (12/7/2010)

---

Nono, it was an attempt to be accurate. ...

Heh, no. I became an administrator and fuddled my way through it mostly because noone around me at the time wanted anything...

The Dixie Flatline (12/7/2010)

---

Jeff Moden (12/6/2010)

---

The Dixie Flatline (12/6/2010)

---

If I understand you correctly, you always want the start and end dates from the prior month, regardless of the starting and...

Alright, let's see...

Nono, it was an attempt to be accurate. C'mon, get it right....

Wow, seriously Fox? Man, my High School degree is fading fast.

Short form: Pick up a developer's copy of SQL Server, in specific you want to get your hands...