To Update smalldatetime column

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

To Update smalldatetime column

Author

Topic

raky
Aged Yak Warrior

767 Posts

Posted - 2009-06-16 : 10:43:19

Hi,

I have a table having a column with smalldatetime datatype and it is having values. I want to update all the values in that column with datepart as 1

for eg:
1949-04-10 00:00:00
1998-03-26 00:00:00
2005-07-30 00:00:00
2010-01-30 00:00:00
2010-01-23 00:00:00

as

1949-04-01 00:00:00
1998-03-01 00:00:00
2005-07-01 00:00:00
2010-01-01 00:00:00
2010-01-01 00:00:00

Hope i am clear

nr
SQLTeam MVY

12543 Posts

Posted - 2009-06-16 : 10:52:20

update tbl
set col = col - datepart(dd,col) + 1

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

raky
Aged Yak Warrior

767 Posts

Posted - 2009-06-16 : 11:05:07

Simply Superb...

Thnx nr...

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-06-18 : 02:19:00

or

update tbl
set col = dateadd(month,datediff(month,0,col),0)

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources