last day of the month

Hi all,
I have Date_Mmmyy column in my table that stores dates in MMMYY format
for example; "OCT01","OCT04","AUG04". I need to update another column in
this table, which is DateTime, with the last day of the Date_Mmmyy value.
Here is an example...
DECLARE @.test table
(
Date_Mmmyy varchar(5),
Date_Datetime datetime
)
INSERT INTO @.test(Date_Mmmyy)
VALUES('OCT01')
INSERT INTO @.test(Date_Mmmyy)
VALUES('OCT04')
INSERT INTO @.test(Date_Mmmyy)
VALUES('AUG04')
SELECT *
FROM @.test
I would like to see...
OCT01 2001-09-30
OCT04 2004-09-30
AUG04 2004-08-31
Thanks.--BEGIN PGP SIGNED MESSAGE--
Hash: SHA1
October is the 10th month not the 9th.
SELECT date_mmmyy,
dateadd(month,1,cast('01' + date_mmmyy as datetime))-1
FROM @.test
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
--BEGIN PGP SIGNATURE--
Version: PGP for Personal Privacy 5.0
Charset: noconv
iQA/ AwUBQjC8+IechKqOuFEgEQJEdQCeI7osM0fWzMiL
l/e3qENjIa25TV0AoKRZ
SYBhDB51p4ZJy+UuV2Sjy0Rk
=8ZVj
--END PGP SIGNATURE--
sql wrote:
> Hi all,
> I have Date_Mmmyy column in my table that stores dates in MMMYY format
> for example; "OCT01","OCT04","AUG04". I need to update another column in
> this table, which is DateTime, with the last day of the Date_Mmmyy value.
> Here is an example...
> DECLARE @.test table
> (
> Date_Mmmyy varchar(5),
> Date_Datetime datetime
> )
> INSERT INTO @.test(Date_Mmmyy)
> VALUES('OCT01')
> INSERT INTO @.test(Date_Mmmyy)
> VALUES('OCT04')
> INSERT INTO @.test(Date_Mmmyy)
> VALUES('AUG04')
> SELECT *
> FROM @.test
> I would like to see...
> OCT01 2001-09-30
> OCT04 2004-09-30
> AUG04 2004-08-31|||> DECLARE @.test table
> (
> Date_Mmmyy varchar(5),
> Date_Datetime datetime
> )
> INSERT INTO @.test(Date_Mmmyy)
> VALUES('OCT01')
> INSERT INTO @.test(Date_Mmmyy)
> VALUES('OCT04')
> INSERT INTO @.test(Date_Mmmyy)
> VALUES('AUG04')
UPDATE @.test SET Date_Datetime=dateadd(day, -1, dateadd(month, 1,
'1'+Date_Mmmyy))

> SELECT *
> FROM @.test
>
Regards
Mr Tea
"sql" <donotspam@.nospaml.com> wrote in message
news:eygksbbJFHA.904@.tk2msftngp13.phx.gbl...
> Hi all,
> I have Date_Mmmyy column in my table that stores dates in MMMYY format
> for example; "OCT01","OCT04","AUG04". I need to update another column in
> this table, which is DateTime, with the last day of the Date_Mmmyy value.
> Here is an example...
> DECLARE @.test table
> (
> Date_Mmmyy varchar(5),
> Date_Datetime datetime
> )
> INSERT INTO @.test(Date_Mmmyy)
> VALUES('OCT01')
> INSERT INTO @.test(Date_Mmmyy)
> VALUES('OCT04')
> INSERT INTO @.test(Date_Mmmyy)
> VALUES('AUG04')
> SELECT *
> FROM @.test
> I would like to see...
> OCT01 2001-09-30
> OCT04 2004-09-30
> AUG04 2004-08-31
> Thanks.
>|||Thank you all for your replies. I got this working with your help.
"sql" <donotspam@.nospaml.com> wrote in message
news:eygksbbJFHA.904@.tk2msftngp13.phx.gbl...
> Hi all,
> I have Date_Mmmyy column in my table that stores dates in MMMYY format
> for example; "OCT01","OCT04","AUG04". I need to update another column in
> this table, which is DateTime, with the last day of the Date_Mmmyy value.
> Here is an example...
> DECLARE @.test table
> (
> Date_Mmmyy varchar(5),
> Date_Datetime datetime
> )
> INSERT INTO @.test(Date_Mmmyy)
> VALUES('OCT01')
> INSERT INTO @.test(Date_Mmmyy)
> VALUES('OCT04')
> INSERT INTO @.test(Date_Mmmyy)
> VALUES('AUG04')
> SELECT *
> FROM @.test
> I would like to see...
> OCT01 2001-09-30
> OCT04 2004-09-30
> AUG04 2004-08-31
> Thanks.
>