Convert( ) to update date field

[ChopinFan]

I have a bunch of dates currently the first of each month and I need to change them all to the 22nd of the month all at once with an update query. SQL isn't liking the syntax I've tried to specify the new date. What is the syntax to do this? Thanks!

CONVERT (datetime, MONTH(CompletionDate) + '/22/' + YEAR(CompletionDate))

It keeps giving me the "Syntax error converting the varchar value '/22/' to a column of data type int." message.

 

[gmmastros]

Month and year both return integers which you are trying to add to a string, which is causing your problem. You need to convert the output of the month and the year function to varchar before adding it to the string.

CONVERT (datetime, [!]Convert(varChar(2), [/!]MONTH(CompletionDate)[!])[/!] + '/22/' + [!]Convert(VarChar(4),[/!] YEAR(CompletionDate))[!])[/!]

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[bborissov]

You try to concatenate integers with varchar(). MONTH() and YEAR() functions both retirn integer.
Try:

CONVERT (datetime, convert(varchar(6), CompletionDate,112)+'22')

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[ChopinFan]

Thanks much! Both solutions work great.