Updating a date field to 1st of the month 1

[SpiderFlight]

I know this is probably very simple to do but I just seem to be stumped. I have a table with a column data type of datetime. The data in this column contains dates spanning of the last few years. What I want to do is update this column with its month and year but change the day to the 1st of the month.

This is what I have written but doesn't work:
UPDATE [file]
SET [Event Date] = DatePart(Month,[Event Date]) + '/1/' + DatePart(Day,[Event Date])

Any help would be greatly appreciated.

 

[vongrunt]

thread183-921333

------
Math problems? Call 0800-[(10x)(13i)^2]-[sin(xy)/2.362x]

 

[SpiderFlight]

I used the command referenced in Thread183-921333 however I received the following error:

Server: Msg 241, Level 16, State 1, Line 1
Syntax error converting datetime from character string.

This is the command I entered.
UPDATE Table
SET [Event Date] = DateAdd(mm,DateDiff(mm,0,[Event Date]),0)

 

[vongrunt]

I have a table with a column data type of datetime.

It appears not. Are you using char/varchar instead?

------
Math problems? Call 0800-[(10x)(13i)^2]-[sin(xy)/2.362x]

 

[SpiderFlight]

Sorry, I spoke too soon... I tested this in the SQL Query Analyzer and I received the error. When I ran this in my DTS package the statement worked as stated. Not sure why the Analyzer report an error.

Thanks for your help.

 

[donutman]

I've found it best never to ignore strange happenings. They almost always uncover a subtle problem. I'd make sure you get it to work in QA too.
I've found it best never to ignore strange happenings. Sorry, but it's worth repeating.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]